Oracle - Finding Grants for Tables
To be able to determine grants for a user or table in Oracle, the user must first be logged in with DBA privileges to gain access to the tables that store the grant information. To connect to Oracle with DBA privileges using RazorSQL, make sure to select the "SYSDBA" connect as option when creating the Oracle connection profile on the Connections -> Add Connection Profile menu option.
The SYS.DBA_TAB_PRIVS view contains table grant information for the database. Columns in this view include GRANTEE, OWNER, TABLE_NAME, GRANTOR and PRIVILEGE. The following query will retrieve all grants for the table named EMPLOYEE:
SELECT
*
FROM
SYS.DBA_TAB_PRIVS
WHERE
TABLE_NAME = 'EMPLOYEE'
;
The DBA_TAB_PRIVS view can also be queried to determine table grants for a user. The following query will retrieve all table grants for the user TESTUSER:
SELECT
*
FROM
SYS.DBA_TAB_PRIVS
WHERE
GRANTEE = 'TESTUSER'
;
