my notes...

Characteristics:

• Roles can be granted to & revoked from users with same commands as privileges.

• Roles can be granted to any user or role. But it can't be granted to itself in any circular fashion.

• A role can have both system & object privileges.

• A role can be enabled or disabled for each user to whom it is granted

• A role can require a password to be enabled

• Role name must be unique among roles & users

• Roles have their description stored in data dictionary. They are not owned by anyone, and does not belong to any schema.

Benefits of roles:

• Easier privilege management : Granting the privileges to a role & then granting the role to several users.

• Dynamic Privilege management : Any role privileges modification becomes immediately reflected to the users granted the role.

• Selective availability of privileges : Roles can be enabled or disabled to turn privileges on/off temporarily.

• Can be granted through Operating system.

Creating Roles:

Syntax	Example
CREATE ROLE role [ NOT IDENTIFIED|IDENTIFIED {BY password |EXTERNALLY|GLOBALLY|USING package}];	CREATE ROLE hr_manager; (i.e. NOT IDENTIFIED; CREATE ROLE hr_manager IDENTIFIED BY 123s5; CREATE ROLE hr_manager IDENTIFIED EXTERNALLY;

NOT IDENTIFIED	:	No verification required for enabling the role
IDENTIFIED	:	Verification required for enabling the role
BY password	:	The password required for enabling the role
EXTERNALLY	:	The user must be authorized by an external service such as the Operating System or 3rd party service before enabling the role.
GLOBALLY	:	A user must be authorized to use the role by enterprise directory service before the role can be enabled.
USING package	:	Creates an application role, which is a role that can be enabled only by the applications using an authorized package.

Predefined Roles:

Role Name		Details
CONNECT, RESOURCE, DBA	:	For backward compatibility
EXP_FULL_DATABASE	:	Privileges to export the database
IMP_FULL_DATABASE	:	Privileges to import the database
DELETE_CATALOG_ROLE	:	DELETE privileges on data dictionary tables
EXECUTE_CATALOG_ROLE	:	EXECUTE privilege on data dictionary packages
SELECT_CATALOG_ROLE	:	SELECT privileges on data dictionary tables

Catalog roles are granted to non-DBA who need access to data dictionary views, tables & packages.

Other roles are defined by the SQL scripts provided with the database. One such is AQ_ADMINISTRATOR_ROLE providing privileges for administering advanced queuing.

Modifying Roles:

A role can be modified only to change its authentication method. You must have been granted the role with ADMIN option or have the ALTER ANY ROLE privilege.

Syntax	Example
ALTER ROLE role [ NOT IDENTIFIED|IDENTIFIED {BY password |EXTERNALLY|GLOBALLY|USING package}];	ALTER ROLE hr_manager NOT IDENTIFIED; ALTER ROLE hr_manager IDENTIFIED BY 123s5; ALTER ROLE hr_manager IDENTIFIED EXTERNALLY;

Assigning Roles:

Syntax	Example
GRANT role [, role] .... TO {user|role|PUBLIC} [, user|role|PUBLIC]... [WITH ADMIN OPTION] ;	GRANT hr_sales TO scott; GRANT hr_clerk TO hr_manager; GRANT hr_manager TO scott WITH ADMIN OPTION;

PUBLIC	:	Grants role to all users
WITH ADMIN OPTION	:	Enables the guarantee to grant/revoke role to other users/roles, and alter/drop the role.

Note:

• A user who creates the role is implicitly assigned that role with ADMIN OPTION.

• The maximum number of roles that users can enable is set by the initialization parameter MAX_ENABLED_ROLES

Establishing default Roles:

• A user can be assigned many roles all of which are enabled at logon by default. The default roles for a user can be limited by the ALTER USER command.

• All other roles are disabled except the default role(s) set using ALTER USER.

• No Password is required for default roles. They are enabled at login same as a role without a password.

• The DEFAULT ROLE clause can only be applied to roles granted directly to the user. The following is not possible make following roles as default:

• Roles granted through other roles

• Roles managed by an external service

ALTER USER user DEFAULT ROLE {role [,role]... | ALL [EXCEPT role [, role]...] | NONE}

user	:	User who is granted the roles.
role	:	Role which is to be made the default role for the user.
ALL	:	Make all the roles granted to the user default roles except those listed in EXCEPT list.
EXCEPT	:	The roles not to be included in the default roles
NONE	:	Make none of the roles granted to the user as default roles. So, at logon, the user will only have the privileges that are granted directly to him.

Application Roles:

The Using clause in CREATE ROLE statement creates an Application role. Such a role can only be enabled by an authorized PL/SQL package. This voids the need for embedding the password inside applications.

CREATE ROLE admin_role IDENTIFIED USING hr.employees;

So in this case the admin_role can only be enabled by modules that are defined inside the hr.employees PL/SQL package.

Enabling / Disabling Roles:

• Enable or disable a role to temporarily active/deactivate privileges associated with the role. The role must already have been granted to the user to be able to enable it.

• Roles are enabled for a session, next session the user's active roles revert back to the default roles.

• A password may be required to enable a role which must be included in the SET ROLE command.

• Restriction : Roles can be enabled from any tools or commands that allows PL/SQL, but not 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 & application procedures, but not from stored procedures. Error generated using SET ROLE within stored procedure:

  ORA-06565 : cannot execute SET ROLE from within stored procedures

Syntax	Example
SET ROLE {role [IDENTIFIED BY password] [, role [IDENTIFIED BY password]]... | ALL [EXCEPT role [, role]...] | NONE}	SET ROLE hr_sales; SET ROLE hr_manager IDENTIFIED BY mgr_pwd; SET ROLE ALL EXCEPT hr_manager;

role	:	Name of the role
IDENTIFIED BY password	:	Password, if any, required for enabling the role.
ALL	:	Enables all the roles granted to the user except those listed in EXCEPT clause.
EXCEPT role	:	Does not enable these roles
NONE	:	Disables all roles for the current session. So only privileges granted directly to the user is active.

Note : The ALL option with the EXCEPT clause works only when every role enabled does not have a password.

Revoking Roles:

Requires the ADMIN OPTION or GRANT ANY ROLE privilege.

Syntax	Example
REVOKE role [, role] .... FROM {user|role|PUBLIC} [, {user|role|PUBLIC}]... ;	REVOKE hr_sales FROM scott; REVOKE hr_manager FROM PUBLIC;

Removing Roles:

• Requires the ADMIN OPTION on the role or DROP ANY ROLE privilege.

• On Dropping a role, Oracle server revokes it from all the users and roles to whom it had been granted and removes it from the database.

Syntax	Example
DROP ROLE role;	DROP ROLE hr_sales;

Guidelines for Creating Roles:

Roles includes privileges that are needed to perform a task, so a role name is usually an application task or a job title.

Steps to be taken to create roles:

• Create a role for each application and name it accordingly such as INVENTORY.

• Assign the privileges necessary to perform the task to the role.

• Create a role for each type of user, corresponding to the job_title such as HR_CLERK.

• Grant application roles to User's roles

• Grant user's roles to users.

This way if there is any modifications to the application requiring a change in privileges, then the same may be assigned to the application role, in this case INVENTORY. All the users who are currently performing the task will receive the new privileges.

Guidelines for using Passwords & Default Roles:

• Passwords provide an additional level of security when enabling a role.

• Passwords allow a role to be enabled only through an application.

For example, for query purpose the normal read-only privileged role may be enabled, but for a record modification, the user may need to log into an application which automatically enables the read-write privileged role without any need to prompt the user for the password.

Views:

DBA_ROLES	:	All roles that exist in the database
DBA_ROLES_PRIVS	:	Roles granted to users & roles
ROLE_ROL_PRIVS	:	Roles that are granted to roles
DBA_SYS_PRIVS	:	System privileges granted to users & roles
ROLE_SYS_PRIVS	:	System privileges granted to roles
ROLE_TAB_PRIVS	:	Object privileges granted to roles
SESSION_ROLES	:	Roles that the user has currently enabled.