Managing Roles - PowerPoint PPT Presentation

About This Presentation
Title:

Managing Roles

Description:

Managing Roles Objectives Creating and modifying roles Controlling availability of roles Removing roles Using predefined roles Displaying role information from the ... – PowerPoint PPT presentation

Number of Views:189
Avg rating:3.0/5.0
Slides: 21
Provided by: bhe82
Category:
Tags: managing | roles

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com