Directory permissions granted to a user in Oracle database

Querying directory permissions granted to a user

To query directory permissions granted to users in Oracle Database, you can use the DBA_TAB_PRIVS view. This view contains information about table and directory object privileges granted to users. Specifically, you want to retrieve information about directory privileges granted to users for a particular directory object.

Here’s a refined SQL query to achieve this, including sample output:

SQL Query:

sql:


SELECT 
    grantee AS "GRANTEE",
    table_name AS "DIRECTORY_NAME",
    LISTAGG(privilege, ',') WITHIN GROUP (ORDER BY privilege) AS "GRANTS"
FROM 
    dba_tab_privs
WHERE 
    table_name = 'DPUMP' 
GROUP BY 
    grantee, table_name;

Columns Explanation:

• grantee: The user or role to whom the privilege is granted.
• table_name: The name of the directory object. In Oracle, directories are also managed as table-like objects in the DBA_TAB_PRIVS view.
• LISTAGG(privilege, ',') WITHIN GROUP (ORDER BY privilege): Aggregates the list of privileges granted to each user, separating them by commas.

Sample Output:

GRANTEE              DIRECTORY_NAME                 GRANTS            
-------------------- ------------------------------ --------------------
SCOTT                DPUMP                           READ,WRITE         
TIGER                DPUMP                           READ,WRITE         
TOM                  DPUMP                           READ,WRITE         
CAM                  DPUMP                           READ,WRITE         
SAM                  DPUMP                           READ,WRITE         

Usage Notes:

1. Adjust Table Name: Make sure to replace 'DPUMP' in the WHERE clause with the actual directory name you want to query.
2. Privileges: The privilege column typically includes access types such as READ and WRITE.
3. Oracle Versions: The DBA_TAB_PRIVS view is standard across Oracle versions, but always refer to your specific Oracle documentation for any version-specific details.

This query helps database administrators quickly review and manage directory access permissions granted to different users, ensuring appropriate access control and security.