RelationalDBDesignRelationalDBDesign


Security Roles   «Prev  Next»
Lesson 10 Listing user roles
Objective List roles granted to user

List roles granted to user

The previous lesson explained the data dictionary views that contain information about roles and their privileges. There are also data dictionary views that advise you on the particular roles assigned to a particular user.

Listing roles for user

The primary data dictionary view used to describe the roles for a user is USER_ROLE_PRIVS. This view contains the following columns:
Role What it determines
USERNAME The name of the user, or the group PUBLIC
GRANTED_ROLE The name of the role
ADMIN_OPTION Whether the role was granted with the ADMIN OPTION
DEFAULT_ROLE Whether the role is a default role for the user
OS_GRANTED Whether the role was granted by the operating system, as when the role was created with the IDENTIFIED EXTERNALLY option

Some of this information is also available through the DBA_ROLE_PRIVS view, which contains columns for GRANTEE, the recipient of the role; GRANTED_ROLE, for the role name; ADMIN_OPTION; and DEFAULT_ROLE. The DBA view contains this information for all roles for all users.

Listing available roles

There is also a view that you can use to find out the current roles for a user.
The SESSION_ROLES view lists the current roles for the current user.
These two views, along with the data dictionary views mentioned previously, can be used to illustrate how roles and privileges work in the following simulation.
Text 1
1) Text 1
Text 2
2) Text 2
Text 3
3) Text 3
Text 4
4) Text 4
Text 5
5) Text 5
Text 6
6) Text 6
Text 7
7) Text 7

DBA using Roles
The next lesson shows how to get rid of a role.