Understanding Oracle Database Grants: A Complete Overview

In managing Oracle databases, understanding user permissions and privileges is crucial for maintaining security and ensuring appropriate access levels. This tutorial delves into how to comprehensively list all types of grants a user has received in an Oracle database.

Introduction to Database Privileges

Oracle databases manage access through various types of privileges:

1. System Privileges: These are high-level permissions, such as CREATE TABLE or SELECT ANY DICTIONARY, allowing operations across the entire database.
2. Object Privileges: More specific permissions granted on individual objects like tables, views, and procedures (e.g., SELECT, INSERT, UPDATE, DELETE).
3. Role-Based Privileges: These allow bundling privileges into roles, simplifying privilege management by assigning roles to users instead of individual privileges.

Listing System Privileges

To view system-level privileges granted directly or indirectly through roles:

SELECT PRIVILEGE
  FROM sys.dba_sys_privs
 WHERE grantee = 'username'
UNION
SELECT PRIVILEGE 
  FROM dba_role_privs rp 
 JOIN role_sys_privs rsp ON (rp.granted_role = rsp.role)
 WHERE rp.grantee = 'username'
 ORDER BY 1;

This query combines privileges directly granted to a user and those obtained through roles.

Direct Object Privileges

To list object-level grants directly assigned to a user:

SELECT owner, table_name, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv 
  FROM dba_tab_privs
 WHERE grantee = 'username'
 ORDER BY owner, table_name;

This query provides details on privileges like SELECT, INSERT, etc., directly granted to user objects.

Indirect Object Privileges

For object-level grants acquired through roles:

SELECT DISTINCT owner, table_name, PRIVILEGE 
  FROM dba_role_privs rp 
 JOIN role_tab_privs rtp ON (rp.granted_role = rtp.role)
 WHERE rp.grantee = 'username'
 ORDER BY owner, table_name;

This query helps in identifying privileges a user has through roles.

Comprehensive Privilege Listing Using DBMS_METADATA

To generate the DDL for all grants (system, object, and role) received by a user:

SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'username') FROM dual;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT', 'username') FROM dual;
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT', 'username') FROM dual;

The DBMS_METADATA package retrieves the exact SQL statements used to grant privileges, providing a complete view of user permissions.

Special Considerations

• Public Grants: These are global and apply to all users. They should be reviewed separately.
• Current User Context: Some views like ALL_TAB_PRIVS_RECD only show grants where the current user is the grantee or through roles, not those on objects they own directly.

Best Practices

1. Regularly audit user privileges to ensure compliance with security policies.
2. Use roles for ease of privilege management and reduce direct assignment errors.
3. Document all granted privileges for transparency and future reference.

By understanding these methods and tools, database administrators can effectively manage and audit user permissions in Oracle databases, ensuring both security and operational efficiency.