Managing Roles

1
Managing Roles
2
Objectives

• Creating and modifying roles
• Controlling availability of roles
• Removing roles
• Using predefined roles
• Displaying role information from the
  data dictionary

3
Roles

• Role named groups of related privileges
• Granted/revoked with same commands as for
  privileges
• Maybe granted to user or role (except itself)
• Can consist of object and system privileges
• May be enabled/disabled
• Can require password to enable
• Not owned by anyone

4
Roles
Users
C
A
B
Roles
HR_CLERK
HR_MGR
Privileges
5
Benefits of Roles

• Reduced granting of privileges
• Dynamic privilege management
• Selective availability of privileges
• Granted through the OS
• No cascading revokes
• Improved performance

6
Creating Roles
CREATE ROLE sales_clerk
CREATE ROLE hr_clerk IDENTIFIED BY bonus
CREATE ROLE hr_manager IDENTIFIED EXTERNALLY
7
Using Predefined Roles
Role Name Description CONNECT These two
roles are provided RESOURCE for backward
compatibility. DBA All system privileges
WITH ADMIN OPTION EXP_FULL_DATABASE Privilege
s to export the DB IMP_FULL_DATABASE Privileges
to import the DB DELETE_CATALOG_ROLE DELETE
privileges on DD tables EXECUTE_CATALOG_ROLE E
XECUTE privilege on DD packages SELECT_CATALOG
_ROLE SELECT privilege on DD tables
8
Modifying Roles
ALTER ROLE sales_clerk IDENTIFIED BY commission
ALTER ROLE hr_clerk IDENTIFIED EXTERNALLY
ALTER ROLE hr_manager NOT IDENTIFIED
9
Assigning Roles
GRANT sales_clerk TO scott
GRANT hr_clerk, TO hr_manager
GRANT hr_manager TO scottWITH ADMIN OPTION
10
Assigning Privileges to Roles
GRANT create table, create any index TO hr_clerk
GRANT create_session TO hr_manager
11
Establishing Default Roles

• User may have many roles. A default role is a
  subset of those that is automatically enabled
  when user logs in. By default, all roles assigned
  to user are enabled at logon. Limit the default
  role for a user
• ALTER USER user DEFAULT ROLE role,role.. ALL
  EXCEPT role ,role.. NONE

12
Examples

• ALTER USER scott DEFAULT ROLE hr_clerk,
  sales_clerk
• ALTER USER scott DEFAULT ROLE ALL
• ALTER USER scott DEFAULT ROLE ALL EXCEPT
  hr_clerk
• ALTER USER scott DEFAULT ROLE NONE -- no default
  roles may have other roles for which a password
  may be needed

13
Enabling and Disabling Roles

• Disable a role to temporarily revoke the role
  from a user.
• Enable a role to temporarily grant it.
• The SET ROLE command enables anddisables roles.
• Default roles are enabled for a user at login.
• A password may be required to enable a role.

14
Enabling and Disabling RolesExamples
SET ROLE sales_clerk IDENTIFIED BY commission
Enable this is how users would activate their
role

• SET ROLE hr_clerk

SET ROLE ALL EXCEPT sales_clerk
Disable all roles for current session
SET ROLE NONE
15
Removing Roles from Users
REVOKE sales_clerk FROM scott
REVOKE hr_manager FROM PUBLIC
16
Removing Roles
DROP ROLE hr_manager
17
Guidelines for Creating Roles
Users
User roles
HR_MANAGER
HR_CLERK
PAY_CLERK
Application roles
BENEFITS
PAYROLL
Application privileges
Payroll privileges
18
Guidelines for using Passwords and Default Roles
Default role
Password protected non-default
PAY_CLERK
PAY_CLERK_RO
19
Displaying Role Information
Role View Description DBA_ROLES All roles which
exist in the database DBA_ROLE_PRIVS Roles
granted to users and roles ROLE_ROLE_PRIVS Roles
which are granted to roles DBA_SYS_PRIVS System
privileges granted to users and roles
ROLE_SYS_PRIVS System
privileges granted to roles ROLE_TAB_PRIVS Table
privileges granted to roles SESSION_ROLES Roles
which the user currently has enabled.

Select role, password_required from dba_roles
20
Summary

• Creating roles
• Assigning privileges to roles
• Assigning roles to users or roles
• Establishing default roles