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/[email protected];
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.
