Identifying unused privileges in a database system

• US 10,268,705 B2
• Filed: 06/24/2014
• Issued: 04/23/2019
• Est. Priority Date: 06/24/2014
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Chui, Chi Ching, Pesati, Vikram R.
• Primary Examiner(s): Ruiz, Angelica
• Application Number:
  US14/313,254
• Publication Number:
  US 20150370824A1
• Time in Patent Office: 1,764 Days
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases

Abstract

Techniques for identifying unused privileges are provided. Database accesses are monitored to generate privilege usage data. Privilege usage data for each database access may indicate a user, a utilized privilege, an object that is the target of the privilege, and a role to which the privilege is granted. The privilege usage data is compared to database authorization data that indicates all (or a subset) of granted privileges. A result of the comparison is unused privilege data that indicates what granted privileges were not utilized. A role graph may be generated that indicates one or more privileges that were utilized and one or more privileges that were not utilized along with role paths providing the privileges.

Claims

1. A method comprising:

identifying a set of privileges that are available to a plurality of requesting entities, wherein the set of privileges includes two or more of select, insert, delete, or update;

monitoring a set of database accesses initiated by the plurality of requesting entities, wherein the set of database accesses include a subset that involve a first subset of the set of privileges, wherein the first subset comprises privileges that have been used by the plurality of requesting entities;

wherein monitoring comprises;

determining, by a database authorization engine, based on a query, whether a requesting entity is authorized to utilize a privilege with respect to an object;

in response to determining, by the database authorization engine, that the requesting entity is authorized to utilize the privilege with respect to the object;

executing an execution plan for the query, wherein executing involves a database access in the subset of the set of database accesses, and

creating a particular record about the database access, wherein the particular record identifies the requesting entity, the privilege, the object, and zero or more roles;

performing a comparison of the set of privileges and the first subset;

based on the comparison of the set of privileges and the first subset, identifying a second subset, of the set of privileges, that has not been used by any of the plurality of requesting entities;

wherein the method is performed by one or more computing devices.

2. The method of claim 1, further comprising:

storing a record for each of multiple database accesses in the subset of the set of database accesses;

wherein the record identifies a particular requesting entity, a particular privilege, a particular object that was a subject of the corresponding database access, and a role to which the particular privilege is granted.

3. The method of claim 1, further comprising:

for each privilege in the second subset;

identifying a particular requesting entity that is associated with said each privilege, and

identifying one or more roles to which the particular requesting entity is assigned and through which the particular requesting entity is granted said each privilege.

4. The method of claim 1, wherein:

monitoring comprises storing privilege usage data that indicates the first subset of the set of privileges;

wherein storing the privilege usage data comprises storing at least a portion of the privilege usage data in a cursor that includes a particular execution plan.

5. The method of claim 4, wherein storing the privilege usage data comprises:

storing a first portion of the privilege usage data in a first cursor, and

storing a second portion of the privilege usage data in a second cursor that is different than the first cursor.

6. The method of claim 1, further comprising:

receiving input that indicates what information to monitor;

wherein the input identifies one or more of a set of requesting entities, the set of privileges, a set of objects, or a set of roles;

wherein monitoring comprises monitoring based on the input.

7. The method of claim 1, wherein monitoring comprises:

determining that a first database access in the set of database accesses is initiated by a particular requesting entity and involves a first privilege;

creating a first record that includes information about the first database access;

determining that a second database access in the set of database accesses is initiated by the particular requesting entity and involves the first privilege;

based on the first record, determining not to create a second record for the second database access.

8. A method comprising:

determining that a first privilege with respect to a first database object was not utilized by a first requesting entity, wherein the first privilege includes one of select, insert, delete, or update;

determining a first grant path from the first requesting entity to the first privilege through one or more roles;

causing data about the first grant path to be displayed;

causing a role graph to be displayed, wherein the role graph includes;

a first node that represents a particular requesting entity,

one or more second nodes, each representing a role assigned directly or indirectly to the particular requesting entity;

one or more third nodes, each representing a privilege that is granted to the particular requesting entity either directly or indirectly through one or second more roles;

wherein the method is performed by one or more computing devices.

9. The method of claim 8, wherein the one or more roles is a plurality of roles that includes a first role to which the first requesting entity is assigned and a second role that is different than the first role and to which is granted the first privilege with respect to the first database object.

10. The method of claim 8, wherein:

the one or more third nodes are a plurality of third nodes.

11. The method of claim 8, wherein the role graph also includes one or more fourth nodes, each representing a requesting entity that is different than the particular requesting entity.

12. One or more non-transitory storage media storing instructions which, when executed by one or more processors, cause:

identifying a set of privileges that are available to a plurality of requesting entities, wherein the set of privileges includes two or more of select, insert, delete, or update;

monitoring a set of database accesses initiated by the plurality of requesting entities, wherein the set of database accesses include a subset that involve a first subset of the set of privileges, wherein the first subset comprises privileges that have been used by the plurality of requesting entities;

wherein monitoring comprises;

determining, by a database authorization engine, based on a query, whether a requesting entity is authorized to utilize a privilege with respect to an object;

in response to determining, by the database authorization engine, that the requesting entity is authorized to utilize the privilege with respect to the object;

executing an execution plan for the query, wherein executing involves a database access in the subset of the set of database accesses, and

creating a particular record about the database access, wherein the particular record identifies the requesting entity, the privilege, the object, and zero or more roles;

performing a comparison of the set of privileges and the first subset;

based on the comparison of the set of privileges and the first subset, identifying a second subset, of the set of privileges, that has not been used by any of the one or more requesting entities.

13. The one or more storage media of claim 12, wherein the instructions, when executed by the one or more processors, further cause:

storing a record for each of multiple database accesses in the subset of the set of database accesses;

wherein the record identifies a particular requesting entity, a particular privilege, a particular object that was a subject of the corresponding database access, and a role to which the particular privilege is granted.

14. The one or more storage media of claim 12, wherein the instructions, when executed by the one or more processors, further cause:

for each privilege in the second subset;

identifying a particular requesting entity that is associated with said each privilege, and

identifying one or more roles to which the particular requesting entity is assigned and through which the particular requesting entity is granted said each privilege.

15. The one or more storage media of claim 12, wherein:

monitoring comprises storing privilege usage data that indicates the first subset of the set of privileges;

wherein storing the privilege usage data comprises storing at least a portion of the privilege usage data in a cursor that includes a particular execution plan.

16. The one or more storage media of claim 15, wherein storing the privilege usage data comprises:

storing a first portion of the privilege usage data in a first cursor, and

storing a second portion of the privilege usage data in a second cursor that is different than the first cursor.

17. The one or more storage media of claim 12, wherein the instructions, when executed by the one or more processors, further cause:

receiving input that indicates what information to monitor;

wherein the input identifies one or more of a set of requesting entities, the set of privileges, a set of objects, or a set of roles;

wherein monitoring comprises monitoring based on the input.

18. The one or more storage media of claim 12, wherein monitoring comprises:

determining that a first database access in the set of database accesses is initiated by a particular requesting entity and involves a first privilege;

creating a first record that includes information about the first database access;

determining that a second database access in the set of database accesses is initiated by the particular requesting entity and involves the first privilege;

based on the first record, determining not to create a second record for the second database access.

19. One or more storage media storing instructions which, when executed by one or more processors, cause:

determining that a first privilege with respect to a first database object was not utilized by a first requesting entity, wherein the first privilege includes one of select, insert, delete, or update;

determining a first grant path from the first requesting entity to the first privilege through one or more roles;

causing data about the first grant path to be displayed;

causing a role graph to be displayed, wherein the role graph includes;

a first node that represents a particular requesting entity,

one or more second nodes, each representing a role assigned directly or indirectly to the particular requesting entity;

one or more third nodes, each representing a privilege that is granted to the particular requesting entity either directly or indirectly through one or second more roles.

20. The one or more storage media of claim 19, wherein the one or more roles is a plurality of roles that includes a first role to which the first requesting entity is assigned and a second role that is different than the first role and to which is granted the first privilege with respect to the first database object.

21. The one or more storage media of claim 19, wherein the

one or more third nodes are a plurality of third nodes.

22. The one or more storage media of claim 19, wherein the role graph also includes one or more fourth nodes, each representing a requesting entity that is different than the particular requesting entity.