Tuesday, August 15, 2006

Roles

Characteristics:

Benefits of roles:

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:

Establishing default Roles:

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:

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:

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:

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:

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.


Comments: Post a Comment



<< Home
Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google