How to Determine Permissions for Roles in Oracle Database

The primary difference between roles and privileges is users such as database administrators define roles while Oracle sets privileges.

In simple terms, a role is a collection of permissions or privileges and other roles. This is in contrast with privileges that give database users distinct rights to access another user’s object or to use specific SQL statements. The two types of privileges available in an Oracle database are object privileges and system privileges. Object privileges give users permissions to database objects or another user’s objects; system privileges are permissions that allow users to perform powerful database operations. To determine the system privileges associated with roles and users in the database, use the DBA_SYS_PRIVS data dictionary view to determine the object privileges associated with a role or user in the database.

Instructions

1. Select “All Programs” from the Windows Start menu.

2. Click on the Oracle database from the list of programs, select “Application Development” and select “SQL Plus."

3. Enter your user name, password and host string in the Log On window and click “OK.”

4. Type “Select * DBA_Roles;” at the SQL command prompt and press the “Enter” key to view a listing of all roles in the database.

5. Type “SELECT * FROM DBA_SYS_PRIVS;” at the SQL command prompt to view a listing of all system permissions associated with users and roles in a database.

6. Type “SELECT * FROM DBA_TAB_PRIVS;” at the SQL command prompt to view a listing of all object permissions associated with users and roles in a database.

References

• Oracle: PeopleTools Security - Roles and Permission Lists
• Oracle: Managing User Privileges and Roles
• PSOUG.org: Oracle Roles
• Oracle: Administering User Privileges, Roles, and Profiles