User Privileges and Roles in Oracle

A user privilege is a right to execute a particular type of SQL statement, or a right to access another user’s object. The types of privileges are defined by Oracle.

Roles, on the other hand, are created by users (usually administrators) and are used to group together privileges or other roles.

Restricting System Privileges

O7_DICTIONARY_ACCESSIBILITY initialization parameter is set to FALSE.

When this parameter is not set to FALSE, the ANY privilege applies to the data dictionary, and a malicious user with ANYprivilege could access or alter data dictionary tables. The default for O7_DICTIONARY_ACCESSIBILITY is FALSE.

Creating a Role

A Roles can be specified to be authorized by:‎

– The database using a password

CREATE ROLE SHAHID_ROLE1 IDENTIFIED BY AHMED;‎

– An application using a specified package

CREATE ROLE admin_role IDENTIFIED USING hr.admin;‎

– Externally by the operating system, network, or other external source

ALTER ROLE clerk IDENTIFIED EXTERNALLY;‎

– Globally by an enterprise directory service

CREATE ROLE supervisor IDENTIFIED GLOBALLY;‎

Dropping Roles

DROP ROLE clerk;

Granting System Privileges and Roles

GRANT CREATE SESSION, accts_pay TO SHAHID1; GRANT new_dba TO SHAHID1 WITH ADMIN OPTION;

Here in above to grant option first statement granting system privilege along with a role accts_pay to user SHAHID1 where second statement granting a role new_dba to user SHAHID with admin option that means the user SHAHID1 cannot only use all the privilege implicit in new_dba role but can grant, revoke, or drop the new_dba too.

The SET ROLE Statement

During the session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. You can retrieve the current roles that are active for a user in a session by using SESSION_ROLES

SQL> connect hrms/hrms@orcl3;

SQL> select * from session_roles;

ROLE

——————————

EXECUTE_CATALOG_ROLE

DELETE_CATALOG_ROLE

EXP_FULL_DATABASE

IMP_FULL_DATABASE

You can change the roles active in the current session by “SET ROLE”

SQL> select * from session_roles;

ROLE

——————————

SHAHID_ROLE1

Enable specific set of roles in the current session (SHAHID_ROLE1 is created)

SQL> set role SHAHID_ROLE1, EXECUTE_CATALOG_ROLE

Role set.

SQL> select * from session_roles;

ROLE

——————————

EXECUTE_CATALOG_ROLE

SHAHID_ROLE1

Enable all roles in the current session

SQL> set role all;

Role set.

You can disable all roles with the following statement:

SET ROLE NONE;

Note: A user can enable as many roles as specified by the initialization parameter MAX_ENABLED_ROLES.

Using OS Role Management

OS_ROLES = TRUE

If OS_ROLES is set to TRUE, the operating system completely manages the grants and revokes of roles to users. Any previous grants of roles to users using GRANT statements do not apply; however, they are still listed in the data dictionary. Only the role grants made at the operating system level to users apply. Users can still grant privileges to roles and users.

Note: If you choose to have the operating system to manage roles, by default users cannot connect to the database through the shared server. This restriction is the default because a remote user could impersonate another operating system user over a non-secure connection.

SELECT * FROM DBA_SYS_PRIVS; Listing all system Grants SELECT * FROM DBA_ROLE_PRIVS; Listing all Role Grants SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS WHERE GRANTEE = ‘JWARD’; Listing object privilege Granted to user SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE FROM DBA_COL_PRIVS; listing all column specific privilege that have been granted SELECT * FROM SESSION_ROLES; Listing all role currently enabled.‎ SELECT * FROM SESSION_PRIVS; Listing all system privilege currently available.‎ SELECT * FROM DBA_ROLES; listing Role of database SELECT GRANTED_ROLE, ADMIN_OPTION FROM ROLE_ROLE_PRIVS WHERE ROLE = ‘SYSTEM_ADMIN’;‎ The above query will display information about the privilege domains of role.‎

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s