20 Managing Roles Objectives Creating and modifying roles

20 Managing Roles

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

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 20 -3

Roles Users A Roles HR_MGR Privileges CREATE TABLE 20 -4 C B HR_CLERK SELECT ON EMP INSERT ON EMP CREATE SESSION UPDATE ON EMP

Benefits of Roles • Reduced granting of privileges • Dynamic privilege management • Selective availability of privileges • Granted through the OS • No cascading revokes • Improved performance 20 -5

Creating Roles CREATE ROLE sales_clerk; CREATE ROLE hr_clerk IDENTIFIED BY bonus; CREATE ROLE hr_manager IDENTIFIED EXTERNALLY; 20 -6

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 Privileges to export the DB IMP_FULL_DATABASE Privileges to import the DB DELETE_CATALOG_ROLE DELETE privileges on DD tables EXECUTE_CATALOG_ROLE EXECUTE privilege on DD packages SELECT_CATALOG_ROLE SELECT privilege on DD tables 20 -7

Modifying Roles ALTER ROLE sales_clerk IDENTIFIED BY commission; ALTER ROLE hr_clerk IDENTIFIED EXTERNALLY; ALTER ROLE hr_manager NOT IDENTIFIED; 20 -8

Assigning Roles GRANT sales_clerk TO scott; GRANT hr_clerk, TO hr_manager; GRANT hr_manager TO scott WITH ADMIN OPTION; 20 -9

Assigning Privileges to Roles GRANT create table, create any index TO hr_clerk; GRANT create_session TO hr_manager; 20 -10

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} 20 -11

Examples ALTER USER scott DEFAULT ROLE hr_clerk, sales_clerk; 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 20 -12

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 and disables roles. • Default roles are enabled for a user at login. • A password may be required to enable a role. 20 -13

Enabling and Disabling Roles: Examples 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; SET ROLE NONE; 20 -14 Disable all roles for current session

Removing Roles from Users REVOKE sales_clerk FROM scott; REVOKE hr_manager FROM PUBLIC; 20 -15

Removing Roles DROP ROLE hr_manager; 20 -16

Guidelines for Creating Roles User roles Application roles HR_CLERK HR_MANAGER PAY_CLERK BENEFITS PAYROLL Benefits privileges Payroll privileges Application privileges 20 -17

Guidelines for using Passwords and Default Roles Password protected non-default 20 -18 Default role PAY_CLERK_RO Insert, update, delete and select privileges Select privileges

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_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 -19

Summary • Creating roles • Assigning privileges to roles • Assigning roles to users or roles • Establishing default roles 20 -20