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.
- You have started SQL*Plus. At the login box, enter BID1 as both the User Name and Password (using the Tab key to move between text boxes) and then press Enter.
- Enter BID1 in the User Name box.
- Enter BID1 in the Password box and click OK.
- Once the SQL*Plus environment starts, enter SELECT * FROM SESSION_ROLES;at the prompt and then press Enter to list the current session roles for the user.
- SELECT * FROM SESSION_ROLES;
- SELECT * FROM SESSION_ROLES;is the correct syntax. Don't forget to press the Enter key.
- The current roles assigned to the user BID1 are listed. These are all default roles. The BROWSER role gives the SELECT privilege on the BID table in the
COIN
schema to the user. To test these privileges, select all the columns from the COIN
.
BID table by entering SELECT * FROM COIN
. BID;at the prompt and pressing enter.
- SELECT * FROM COIN.BID;
- SELECT * FROM
COIN
.BID;is the correct syntax. Don't forget to press the Enter key.
- To take away this privilege, you can simply reassign the BIDDER role to the user by entering SET ROLE BIDDER;at the prompt and pressing Enter.
- SET ROLE BIDDER;
- SET ROLE BIDDER;is the correct syntax. Don't forget to press the Enter key.
- To check the result of this command, query the SESSION_ROLES view again by entering SELECT * FROM SESSION_ROLES;at the prompt and pressing Enter.
- SELECT * FROM SESSION_ROLES;
- SELECT * FROM SESSION_ROLES;is the correct syntax. Do not forget to press the Enter key.
- Now try to query the
COIN
.BID table again, using the same syntax you learned in step 3 of this simulation.
- SELECT * FROM COIN.BID;
- )SELECT * FROM
COIN
.BID; is the correct syntax. Do not forget to press the Enter key.
- Without the SELECT privilege granted to the BROWSER role, you can no longer see the data in the
COIN
.
BID table. This is the end of the simulation. Click Exit.
DBA using Roles
The next lesson shows how to get rid of a role.