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
- Slides: 20