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 theDBA_TAB_PRIVSview.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:
- Adjust Table Name: Make sure to replace
'DPUMP'in theWHEREclause with the actual directory name you want to query. - Privileges: The
privilegecolumn typically includes access types such asREADandWRITE. - Oracle Versions: The
DBA_TAB_PRIVSview 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.
