DBAI_les17_Rev1_1

Views:
 
Category: Entertainment
     
 

Presentation Description

No description available.

Comments

Presentation Transcript

Managing Roles : 

Managing Roles

Objectives : 

Objectives After completing this lesson, you should be able to do the following: Create and modify roles Control availability of roles Remove roles Use predefined roles Display role information from the data dictionary

Roles : 

Users Privileges Roles UPDATE ON JOBS INSERT ON JOBS SELECT ON JOBS CREATE TABLE CREATE SESSION HR_CLERK HR_MGR A B C Roles

Benefits of Roles : 

Easier privilege management Dynamic privilege management Selective availability of privileges Can be granted through the operating system Benefits of Roles

Creating Roles : 

Roles with ADMIN option: Not identified: By password: Identified externally: CREATE ROLE oe_clerk; CREATE ROLE hr_clerk IDENTIFIED BY bonus; CREATE ROLE hr_manager IDENTIFIED EXTERNALLY; Creating Roles

Predefined Roles : 

Predefined Roles

Modifying Roles : 

ALTER ROLE hr_clerk IDENTIFIED EXTERNALLY; ALTER ROLE hr_manager NOT IDENTIFIED; ALTER ROLE oe_clerkIDENTIFIED BY order; Modifying Roles Use ALTER ROLE to modify the authentication method. Requires the ADMIN option or ALTER ANY ROLE privilege.

Assigning Roles : 

GRANT hr_clerk TO hr_manager; GRANT oe_clerk TO scott; GRANT hr_manager TO scott WITH ADMIN OPTION; Assigning Roles Use GRANT command to assign a role

Establishing Default Roles : 

ALTER USER scott DEFAULT ROLE hr_clerk, oe_clerk; ALTER USER scott DEFAULT ROLE ALL; ALTER USER scott DEFAULT ROLE ALL EXCEPT hr_clerk; ALTER USER scott DEFAULT ROLE NONE; Establishing Default Roles A user can be assigned many roles. A user can be assigned a default role. Limit the number of default roles for a user.

Application Roles : 

Application roles can be enabled only by authorized PL/SQL packages. The USING package clause creates an application role. CREATE ROLE admin_role IDENTIFIED USING hr.employee; Application Roles

Enabling and Disabling Roles : 

Enabling and Disabling Roles Disable a role to revoke the role from a user temporarily. Enable a role to grant it temporarily. 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.

Enabling and Disabling Roles : 

SET ROLE hr_clerk; SET ROLE oe_clerk IDENTIFIED BY order; SET ROLE ALL EXCEPT oe_clerk; Enabling and Disabling Roles

Revoking Roles from Users : 

Revoking roles from users requires the ADMIN OPTION or GRANT ANY ROLE privilege. To revoke a role: REVOKE hr_manager FROM PUBLIC; REVOKE oe_clerk FROM scott; Revoking Roles from Users

Removing Roles : 

DROP ROLE hr_manager; Removing Roles Dropping a role: Removes it from all users and roles it was granted Removes it from the database Requires the ADMIN OPTION or DROP ANY ROLE privilege To drop a role:

Guidelines for Creating Roles : 

HR_MANAGER HR_CLERK PAY_CLERK User roles Application roles Application privileges Users Payroll privileges Benefits privileges Guidelines for Creating Roles BENEFITS PAYROLL

Guidelines for Using Passwords and Default Roles : 

Default role Password protected (not default) Select privileges INSERT, UPDATE, DELETE,and SELECT privileges PAY_CLERK PAY_CLERK_RO Guidelines for Using Passwords and Default Roles

Obtaining Role Information : 

Obtaining Role Information Information about roles can be obtained by querying the following views: DBA_ROLES: All roles that exist in the database DBA_ROLES_PRIVS: Roles granted to users and roles ROLE_ROL_PRIVS: Roles that 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: Object privileges granted to roles SESSION_ROLES: Roles that the user currently has enabled

Summary : 

In this lesson, you should have learned how to: Create roles Assign privileges to roles Assign roles to users or roles Establish default roles Summary

Practice 17 Overview : 

Practice 17 Overview This practice covers the following topics: Listing system privileges for a role Creating, assigning, and dropping roles Creating application roles

authorStream Live Help