To check an Oracle user permission in an Oracle database, you can query the data dictionary views like DBA_SYS_PRIVS and DBA_TAB_PRIVS, which store information about system privileges, object privileges, and role grants assigned to the user.
Step-by-step guide: How to check various types of Oracle user permission.
There are two types of privileges.
System Privilege
Object Privilege
- To check System Privileges
System privileges are privileges that allow users to perform multiple actions at the database level as a dba like CREATE TABLE, ALTER USER.
SQL> SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE = ‘YOUR_USERNAME’;
Just replace YOUR_USERNAME with your own user for which you want to check the privileges.
- To check Object Privileges
Object privileges are privileges on specific objects like tables, views, and sequences owned by another particular user, such as SELECT, INSERT, UPDATE, and DELETE.
SQL> SELECT * FROM DBA_TAB_PRIVS
WHERE GRANTEE = ‘YOUR_USERNAME’;
- To check Role Grants
The Roles are collections of privileges that can be granted to users.
SQL> SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE = ‘YOUR_USERNAME’;
- To check Roles and Privileges Assigned to a Role
SQL> SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE IN (SELECT granted_role FROM DBA_ROLE_PRIVS WHERE GRANTEE = ‘YOUR_USERNAME’);
- To check Privileges on Specific Objects Owned by the User
SQL> SELECT * FROM ALL_TAB_PRIVS
WHERE GRANTEE = ‘YOUR_USERNAME’ OR OWNER = ‘YOUR_USERNAME’;
- Using Session Privileges for Currently logged-in user
SQL> SELECT * FROM SESSION_PRIVS;
Current session:
SQL> SELECT * FROM SESSION_ROLES;
- Privilege Check for a User
The list of system privileges, object privileges, and role privileges for a user:
System Privileges:
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = ‘YOUR_USERNAME’;
Object Privileges:
SQL> SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = ‘YOUR_USERNAME’;
Role Privileges:
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = ‘YOUR_USERNAME’;
Oracle user permission in an Oracle database.