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.

Current Role 1
1) Current Role 1
Current Role 2
2) Current Role 2
Current Role 3
3) Current Role 3
Current Role 4
4) Current Role 4
Current Role 5
5) Current Role 5
Current Role 6
6) Current Role 6
Current Role 7
7) Current Role 7
  1. 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.
  2. Enter BID1 in the User Name box.
  3. Enter BID1 in the Password box and click OK.
  4. 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.
  5. SELECT * FROM SESSION_ROLES;
  6. SELECT * FROM SESSION_ROLES;is the correct syntax. Don't forget to press the Enter key.
  7. 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.
  8. SELECT * FROM COIN.BID;
  9. SELECT * FROM COIN.BID;is the correct syntax. Don't forget to press the Enter key.
  10. 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.
  11. SET ROLE BIDDER;
  12. SET ROLE BIDDER;is the correct syntax. Don't forget to press the Enter key.
  13. 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.
  14. SELECT * FROM SESSION_ROLES;
  15. SELECT * FROM SESSION_ROLES;is the correct syntax. Do not forget to press the Enter key.
  16. Now try to query the COIN.BID table again, using the same syntax you learned in step 3 of this simulation.
  17. SELECT * FROM COIN.BID;
  18. )SELECT * FROM COIN.BID; is the correct syntax. Do not forget to press the Enter key.
  19. 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

  1. Start SQL*Plus from the menu choice on your machine. When the login box comes up, enter BID1 as both the User Name and Password and press Enter.
  2. Once the SQL*Plus environment starts, enter the command at the SQL prompt that lists the current session roles for the user. Make sure you end the command with a semicolon (;) and press the Enter key.
  3. 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 with a SQL command, ending it with a semi-colon, and press the Enter key.
  4. To remove this privilege, reassign the BIDDER role to the user with the SET ROLE SQL command and press Enter.
  5. To check the result of this command, query the SESSION_ROLES view again and press Enter.
  6. Now try to query the COIN.BID table again.
  7. 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.
The next lesson shows how to get rid of a role.