Managing Roles
DownLoad Link at the Last:
Youtube Video Link:
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
What is a Role?
Oracle provides for easy and controlled privilege management through roles. Roles are named groups of related privileges that are granted to users or to other roles. They are designed to ease the administration of privileges in the database.
Role characteristics:
Roles can be granted to and revoked from users with the same commands that are used to grant and revoke system privileges.
Roles can be granted to any user or role. However, a role cannot be granted to itself and cannot be granted circularly.
A role can consist of both system and object privileges.
A role can be enabled or disabled for each user who is granted the role.
A role can require a password to be enabled.
Each role name must be unique among existing usernames and role names.
Roles are not owned by anyone; and they are not in any schema.
Roles have their descriptions stored in the data dictionary
Benefits of Roles
Easier privilege management
Dynamic privilege management
Selective availability of privileges
Can be granted through the operating system
Benefits of Roles
Easier privilege management:
Use roles to simplify privilege management. Rather than granting the same set of privileges to several users, you can grant the privileges to a role, and then grant that role to each user.
Dynamic privilege management:
If the privileges associated with a role are modified, all the users who are granted the role acquire the modified privileges automatically and immediately.
Selective availability of privileges:
Roles can be enabled and disabled to turn privileges on and off temporarily. Enabling a role can also be used to verify that a user has been granted that role.
Can be granted through the operating system:
Operating system commands or utilities can be used to assign roles to users in the database.
Creating Roles
Creating Roles
Use the CREATE ROLE statement to create roles. You must have the CREATE ROLE system privilege to create roles. When you create a role that is NOT IDENTIFIED or is IDENTIFIED EXTERNALLY or BY password, the role is granted with the ADMIN option.
Use the following command to create a role:
CREATE ROLE role [NOT IDENTIFIED | IDENTIFIED
{BY password | EXTERNALLY | GLOBALLY | USING package}]
where:
role: Is the name of the role
NOT IDENTIFIED: Indicates that no verification is required when enabling the role
IDENTIFIED: Indicates that verification is required when enabling the role
BY password: Provides the password that the user must specify when enabling the role
USING package: Creates an application role, which is a role that can be enabled only by applications using an authorized package
EXTERNALLY: Indicates that a user must be authorized by an external service (such as the operating system or a third-party service) before enabling the role
GLOBALLY: Indicates that a user must be authorized to use the role by the enterprise directory service before the role is enabled with the SET ROLE statement, or at login
Creating Roles
Using Oracle Enterprise Manager to Create a Role
From the OEM Console:
Navigate to Databases > Security > Roles.
Select Create from the right-mouse menu.
5. Complete the information for creating a role.
6. Click Create.
Predefined Roles
Predefined Roles
The roles listed are defined automatically for Oracle databases when you run database creation scripts. CONNECT, RESOURCE, and DBA roles are provided for backward compatibility to earlier versions of the Oracle server.
The EXP_FULL_DATABASE and IMP_FULL_DATABASE roles are provided for convenience in using the Import and Export utilities.
The roles DELETE_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, and SELECT_CATALOG_ROLE are provided for accessing data dictionary views and packages. These roles can be granted to users who do not have the DBA role but who require access to the views and tables in the data dictionary.
Other special roles:
The Oracle server also creates other roles that authorize you to administer the database. On many operating systems, these roles are called OSOPER and OSDBA. Their names may be different on your operating system.
Other roles are defined by SQL scripts provided with the database. For example, AQ_ADMINISTRATOR_ROLE provides privileges to administer advanced queuing. AQ_USER_ROLE is obsolete but is kept mainly for release 8.0 compatibility.
Modifying Roles
You can modify a role only to change its authentication method. You must have either been granted the role with the ADMIN option or have the ALTER ANY ROLE system privilege.
Use the following command to modify a role:
ALTER ROLE role {NOT IDENTIFIED | IDENTIFIED
{BY password |USING package| EXTERNALLY | GLOBALLY }};
where:
role: Is the name of the role
NOT IDENTIFIED: Indicates that no verification is required when enabling the role
IDENTIFIED: Indicates that verification is required when enabling the role
BY password: Provides the password used when enabling the role
EXTERNALLY: Indicates that a user must be authorized by an external service
(such as the operating system or a third-party service) before enabling the role
GLOBALLY: Indicates that a user must be authorized to use the role by the
enterprise directory service before the role is enabled with the SET ROLE statement, or
at login
Using Oracle Enterprise Manager to Modify a Role
From the OEM Console:
Navigate to Databases > Security > Roles.
Select Role to be modified.
Select View/Edit Details from the right-mouse menu.
Make the modifications.
Click OK.
Assigning Roles
Assigning Roles
To grant a role to a user, use the same syntax command that you used to grant a system privilege to a user:
GRANT role [, role ]...
TO {user|role|PUBLIC}
[, {user|role|PUBLIC} ]...
[WITH ADMIN OPTION]
where:
role: Is a collection of roles to be granted
PUBLIC: Grants the role to all users
WITH ADMIN OPTION: Enables the grantee to grant the role to other users or roles. (If you grant a role with this option, then the grantee can grant and revoke the role from other users and alter or drop the role.)
Assigning Roles (continued)
The user who creates a role is implicitly assigned the role with ADMIN OPTION. A user who has not been granted a role with ADMIN OPTION requires the GRANT ANY ROLE system privilege to grant and revoke roles to and from others.
Note: The maximum number of database roles that users can enable is set by the initialization parameter MAX_ENABLED_ROLES.
Using Oracle Enterprise Manager to Assign a Role
Navigate to Databases > Security > Roles.
Select Role to be assigned.
Select Grantees from the right-mouse menu.
Select the user to be assigned the role from the Users page.
Click the down arrow to move the user into the Users that have the role window.
Click OK.
Establishing Default Roles
Default Roles
A user can be assigned many roles. A default role is a subset of these roles that is automatically enabled when the user logs on. By default, all the roles assigned to a user are enabled at logon without the need of a password. Limit the default roles for a user with the ALTER USER command.
The DEFAULT ROLE clause applies only to roles that have been granted directly to the user with a GRANT statement. The DEFAULT ROLE clause cannot be used to enable the following:
Roles not granted to the user
Roles granted through other roles
Roles managed by an external service (such as the operating system)
Use the following syntax to assign default roles to a user:
ALTER USER user DEFAULT ROLE
{role [,role]... | ALL [EXCEPT role [,role]... ] | NONE}
where:
user: Is the name of the user who is granted the roles
role: Is the role to be made the default role for the user
Default Roles (continued)
ALL: Makes all of the roles granted to the user default roles, except those listed in the EXCEPT clause (This is the default.)
EXCEPT: Indicates that the following roles should not be included in the default roles
NONE: Makes none of the roles that are granted to the user default roles (The only privileges that the user has at login are those privileges that are assigned directly to the user.)
Because the roles must be granted before they can be made defaults, you cannot set default roles with the CREATE USER command.
Application Roles
Application Roles
The USING package clause in the CREATE ROLE statement creates an application role. An application role can be enabled only by applications by using an authorized PL/SQL package. Application developers do not need to secure a role by embedding passwords inside applications. Instead, they can create an application role and specify which PL/SQL package is authorized to enable the role.
SQL> CREATE ROLE admin_role IDENTIFIED USING hr.employees;
In this example, admin_role is an application role and the role can be enabled only by modules that are defined inside the hr.employee PL/SQL package.
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
Enable or disable roles to activate and deactivate temporarily the privileges associated with the roles. To enable a role, the role must first be granted to the user.
When a role is enabled, the user can use the privileges granted to that role. If a role is disabled, the user cannot use the privileges associated with that role unless those privileges are granted directly to the user or to another role enabled for that user. Roles are enabled for a session. At the next session, the user’s active roles revert to default roles.
Specifying roles to be enabled:
The SET ROLE command and the DBMS_SESSION.SET_ROLE procedure enable all of the roles included in the command and disable all other roles. Roles can be enabled from any tool or program that allows PL/SQL commands; however, a role cannot be enabled in a stored procedure.
You can use the ALTER USER...DEFAULT ROLE command to indicate which roles will be enabled for a user at login. All other roles are disabled.
A password may be required to enable a role. The password must be included in the SET ROLE command to enable the role. Default roles assigned to a user do not require a password; they are enabled at login, the same as a role without a password.
Enabling and Disabling Roles (continued)
Restrictions:
A role cannot be enabled from a stored procedure, because this action may change the security domain (set of privileges) that allowed the procedure to be called in the first place. So, in PL/SQL, roles can be enabled and disabled in anonymous blocks and application procedures (for example, Oracle Forms procedures), but not in stored procedures.
If a stored procedure contains the command SET ROLE, the following error is generated at run time:
ORA-06565: cannot execute SET ROLE from within stored procedure
Enabling and Disabling Roles
Enabling and Disabling Roles
The SET ROLE command turns off any other roles granted to the user.
SET ROLE {role [ IDENTIFIED BY password ]
[, role [ IDENTIFIED BY password ]]...
| ALL [ EXCEPT role [, role ] ...]
| NONE }
where:
role: Is the name of the role
IDENTIFIED BY password: Provides the password required when enabling the role
ALL: Enables all roles that are granted to the current user, except those listed in the EXCEPT clause (You cannot use this option to enable roles with passwords.)
EXCEPT role: Does not enable these roles
NONE: Disables all roles for the current session (Only privileges granted directly to the user are active.)
The ALL option without the EXCEPT clause works only when every role that is enabled does not have a password.
Revoking Roles from Users
Removing Roles from Users
To revoke a role from a user, use the SQL statement REVOKE. Any user with the ADMIN option for a role can revoke the role from any other database user or role. Also users with the GRANT ANY ROLE can revoke any role.
REVOKE role [, role ]
FROM {user|role|PUBLIC}
[, {user|role|PUBLIC} ]
where
role: Is the role to be revoked or the role from which roles are revoked
user: Is the user from which the system privileges or roles are revoked
PUBLIC: Revokes the privilege or role from all users
Revoking Roles from Users
Using Oracle Enterprise Manager to Revoke a Role from a User
From the OEM Console:
Navigate to Databases > Security > Users.
Select the user for whom a role is to be revoked.
Select the role to be revoked.
Select Revoke from the right-mouse menu.
Select Yes to confirm revoke.
Removing Roles
Removing Roles
To remove a role from the database, use the following syntax:
DROP ROLE role
When you drop a role, the Oracle server revokes it from all users and roles to whom it has been granted and removes it from the database.
You must have been granted the role with ADMIN OPTION or have the DROP ANY ROLE system privilege to drop the role.
Removing Roles (continued)
Using Oracle Enterprise Manager to Remove a Role
From the OEM Console:
Navigate to Databases > Security > Roles.
Select the role to be removed.
Select Remove from the right-mouse menu.
Select Yes to confirm remove.
Guidelines for Creating Roles
Guidelines for Creating Roles
Because a role includes the privileges that are necessary to perform a task, the role name is usually an application task or a job title. The example in the slide uses both application tasks and job titles for role names. Use the following steps to create, assign, and grant users roles:
Create a role for each application task. The name of the application role corresponds to a task in the application, such as PAYROLL.
Assign the privileges necessary to perform the task to the application role.
Create a role for each type of user. The name of the user role corresponds to a job title, such as PAY_CLERK.
Grant application roles to user’s roles.
Grant user’s roles to users.
If a modification to the application requires that new privileges are needed to perform the payroll task, then the DBA only needs to assign the new privileges to the application role, PAYROLL. All of the users that are currently performing this task will receive the new privileges.
Guidelines for Using Passwords and Default Roles
Guidelines for Using Passwords and Default Roles
Passwords provide an additional level of security when enabling a role. For example, the application might require a user to enter a password when enabling the PAY_CLERK role, because this role can be used to issue checks.
Passwords allow a role to be enabled only through an application. This technique is shown in the example in the slide.
The DBA has granted the user two roles, PAY_CLERK and PAY_CLERK_RO.
The PAY_CLERK has been granted all of the privileges that are necessary to perform the payroll clerk function.
The PAY_CLERK_RO (RO for read only) has been granted only SELECT privileges on the tables required to perform the payroll clerk function.
The user can log in to SQL*Plus to perform queries, but cannot modify any of the data, because the PAY_CLERK is not a default role, and the user does not know the password for PAY_CLERK.
When the user logs in to the payroll application, it enables the PAY_CLERK by providing the password. It is coded in the program; the user is not prompted for it.
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
Query Role Information
Many of the data dictionary views that contain information on privileges that are granted to users also contain information about whether the role requires a password.
SQL> SELECT role, password_required
2 FROM dba_roles;
ROLE PASSWORD
------------------------------ -----------
CONNECT NO
RESOURCE NO
DBA NO
SELECT_CATALOG_ROLE NO
EXECUTE_CATALOG_ROLE NO
DELETE_CATALOG_ROLE NO
IMP_FULL_DATABASE NO
EXP_FULL_DATABASE NO
SALES_CLERK YES
HR_CLERK EXTERNAL
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
Practice 17 Overview
This practice covers the following topics:
Listing system privileges for a role
Creating, assigning, and dropping roles
Creating application roles
Practice 17 Overview
Note: Practice can be accomplished using SQL*Plus or using Oracle Enterprise Manager and SQL*Plus Worksheet.
Practice 17: Managing Roles
1 Examine the data dictionary view and list the system privileges of the
RESOURCE role.
2 Create a role called DEV, which will enable a user assigned the role to create a table, create a view, and select from Emi’s CUSTOMERS1 table.
3 a Assign the RESOURCE and DEV roles to Bob, but make only the
RESOURCE role to be automatically enabled when he logs on.
b Give Bob the ability to read all the data dictionary information.
4 Bob must check the undo segments that are currently used by the instance.
Connect as Bob and list the undo segments that are used.
Hint: Use SET ROLE SELECT_CATALOG_ROLE
5 As SYSTEM, try to create a view CUST_VIEW on Emi’s CUSTOMERS table. What
happens?
6 As user Emi grant select on CUSTOMERS1 to SYSTEM. As SYSTEM try to create the view
CUST_VIEW on Emi’s CUSTOMERS1 table. What happens?
Download Link:
0 Comments