Oracle PL/SQL Tutorial/User Privilege/Roles

Assign CONNECT and RESOURCE Roles

SQL>
SQL> CREATE USER Alice IDENTIFIED BY simplepassword;
User created.
SQL>
SQL> GRANT CONNECT, RESOURCE to Alice;
Grant succeeded.
SQL>
SQL>
SQL> drop user alice;
User dropped.
SQL>
SQL>

Assign object privileges to roles

SQL> GRANT SELECT, INSERT ON employee TO entry;

SQL> GRANT SELECT, INSERT ON customer TO entry;
SQL> GRANT SELECT, UPDATE ON employee TO maintenance;
SQL> GRANT SELECT, UPDATE ON customer TO maintenance;
SQL> GRANT EXECUTE ON validate_salary TO maintenance;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO manager;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON customer TO manager;
SQL> GRANT EXECUTE ON validate_salary TO manager;
SQL> GRANT EXECUTE ON adjust_salary TO manager;
SQL>
SQL>
SQL>

Assign Role to User

SQL>
SQL> GRANT TestRole TO Alice;
SQL>

Checking Object Privileges Granted to a Role

You can check which object privileges have been granted to a role by querying role_tab_privs.

SQL> desc role_tab_privs;
 Name               Null?    Type
 ROLE               NOT NULL VARCHAR2(30)  --User to whom the privilege was granted.                                          
 OWNER              NOT NULL VARCHAR2(30)  --User who owns the object.                                                        
 TABLE_NAME         NOT NULL VARCHAR2(30)  --Name of the object on which privilege was granted.                          
 COLUMN_NAME                 VARCHAR2(30)  --Name of the column (if applicable).                                        
 PRIVILEGE          NOT NULL VARCHAR2(40)  --Privilege on the object.                                                     
 GRANTABLE                   VARCHAR2(3)   --Whether the privilege was granted with the GRANT option. Equal to YES or NO. 
SELECT *
FROM role_tab_privs;

Checking Roles Granted to a User

You can check which roles have been granted to a user by querying user_role_privs. A user who creates a role is also granted that role by default.

SQL> desc user_role_privs;
 Name                  Type              Description
 USERNAME                    VARCHAR2(30) --Name of the user to whom the role has been granted.
 GRANTED_ROLE                VARCHAR2(30) --Name of the role granted to the user.
 ADMIN_OPTION                VARCHAR2(3)  --Whether the user is able to grant the role to another user or role. Equal to YES or NO.
 DEFAULT_ROLE                VARCHAR2(3)  --Whether the role is enabled by default when the user connects to the database. Equal to YES or NO.
 OS_GRANTED                  VARCHAR2(3)  --Whether the role was granted by the operating system.
SELECT * FROM user_role_privs;

Checking System Privileges Granted to a Role

You can check which system privileges have been granted to a role by querying role_sys_privs.

SQL> desc role_sys_privs;
 Name               Null?    Type
 ROLE               NOT NULL VARCHAR2(30) --Name of the role.
 PRIVILEGE          NOT NULL VARCHAR2(40) --System privilege granted to the role.
 ADMIN_OPTION       VARCHAR2(3)           --Whether the privilege was granted with the ADMIN option. Equal to YES or NO.

SELECT *
FROM role_sys_privs;

Creating Roles

GRANT CREATE ROLE TO store;
GRANT CREATE USER TO store WITH ADMIN OPTION;
CREATE ROLE product_manager;
CREATE ROLE hr_manager;
CREATE ROLE overall_manager IDENTIFIED by manager_password;

Default Roles

ALTER USER steve DEFAULT ROLE ALL EXCEPT overall_manager;
SET ROLE NONE;
SET ROLE ALL EXCEPT overall_manager;

Dropping a Role

CREATE ROLE product_manager;
GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO product_manager;
REVOKE ALL ON products FROM product_manager;
DROP ROLE product_manager;

Drop Role

SQL>
SQL> DROP ROLE TestRole;
Role dropped.

Granting Privileges to Roles

You can grant both system and object privileges to a role, as well as grant another role to a role.

GRANT SELECT, INSERT, UPDATE, DELETE ON tableNames TO product_manager;
GRANT CREATE USER TO hr_manager;
GRANT product_manager, hr_manager TO overall_manager;

Granting Roles to a User

You grant a role to a user using GRANT.

GRANT overall_manager TO userName;

Output session roles from procedure

SQL>
SQL> create or replace procedure myProcedure
  2    authid current_user
  3    as
  4    begin
  5      for rec in (select * from session_roles)
  6      loop
  7        dbms_output.put_line(rec.role);
  8      end loop;
  9    end;
 10    /
Procedure created.
SQL> execute myProcedure
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
XDBADMIN
XDBWEBSERVICES
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop procedure myProcedure;
Procedure dropped.
SQL>

Query user granted roles

SQL>
SQL> 
SQL> COL grantee          FORMAT A8
SQL> COL granted_role     FORMAT A30
SQL> COL grantor          FORMAT A8
SQL> COL privilege        FORMAT A12
SQL> COL owner            FORMAT A4
SQL> COL table_name       FORMAT A30
SQL>
SQL> 
SQL> SELECT   grantee
  2  ,        granted_role
  3  FROM     dba_role_privs
  4  WHERE    grantee = "PLSQL";
GRANTEE  GRANTED_ROLE
-------- ------------------------------
PLSQL    CTXAPP
PLSQL    CONNECT
PLSQL    RESOURCE
3 rows selected.
SQL>
SQL> -- Query resources.
SQL> SELECT   grantor
  2  ,        owner
  3  ,        table_name
  4  ,        grantee
  5  ,        privilege
  6  FROM     dba_tab_privs
  7  WHERE    grantee = "PLSQL";
no rows selected

Regina and Kristen are manager level users

SQL> CREATE ROLE manager;
Role created.
SQL>
SQL> CREATE USER regina identified by regina;
User created.
SQL> CREATE USER kristen identified by kristen;
User created.
SQL> GRANT CONNECT TO regina, kristen;
Grant succeeded.
SQL> GRANT manager TO regina, kristen;
Grant succeeded.
SQL>
SQL> drop user regina;
User dropped.
SQL> drop user kristen;
User dropped.
SQL>
SQL> drop role manager;
Role dropped.

Revoking a Role

REVOKE overall_manager FROM steve;

Revoking Privileges from a Role

CREATE ROLE product_manager;
GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO product_manager;
REVOKE ALL ON products FROM product_manager;
DROP ROLE product_manager;

Rich and Brad are maintenance level users

SQL> CREATE ROLE maintenance;
Role created.
SQL>
SQL> CREATE USER rich identified by rich;
User created.
SQL> CREATE USER brad identified by brad;
User created.
SQL> GRANT CONNECT TO rich, brad;
Grant succeeded.
SQL> GRANT maintenance TO rich, brad;
Grant succeeded.
SQL>
SQL> drop user rich;
User dropped.
SQL> drop user brad;
User dropped.
SQL> drop role maintenance;
Role dropped.

Roles

1. A role is a group of privileges that you can assign to a user or another role.
2. You can grant a role to multiple users and roles.
3. When adding or deleting a privilege from a role, all users and roles assigned that role automatically receive or lose that privilege.
4. You can assign multiple roles to a user or role.
5. You can assign a password to a role.

36. 7. Roles 36. 7. 1. Roles 36. 7. 2. <A href="/Tutorial/Oracle/0700__User-Privilege/CreatingRoles.htm">Creating Roles</a> 36. 7. 3. <A href="/Tutorial/Oracle/0700__User-Privilege/GrantingPrivilegestoRoles.htm">Granting Privileges to Roles</a> 36. 7. 4. <A href="/Tutorial/Oracle/0700__User-Privilege/GrantingRolestoaUser.htm">Granting Roles to a User</a> 36. 7. 5. <A href="/Tutorial/Oracle/0700__User-Privilege/AssignRoletoUser.htm">Assign Role to User</a> 36. 7. 6. <A href="/Tutorial/Oracle/0700__User-Privilege/CheckingRolesGrantedtoaUser.htm">Checking Roles Granted to a User</a> 36. 7. 7. <A href="/Tutorial/Oracle/0700__User-Privilege/CheckingSystemPrivilegesGrantedtoaRole.htm">Checking System Privileges Granted to a Role</a> 36. 7. 8. <A href="/Tutorial/Oracle/0700__User-Privilege/CheckingObjectPrivilegesGrantedtoaRole.htm">Checking Object Privileges Granted to a Role</a> 36. 7. 9. <A href="/Tutorial/Oracle/0700__User-Privilege/AssignCONNECTandRESOURCERoles.htm">Assign CONNECT and RESOURCE Roles</a> 36. 7. 10. <A href="/Tutorial/Oracle/0700__User-Privilege/DefaultRoles.htm">Default Roles</a> 36. 7. 11. <A href="/Tutorial/Oracle/0700__User-Privilege/RevokingaRole.htm">Revoking a Role</a> 36. 7. 12. <A href="/Tutorial/Oracle/0700__User-Privilege/RevokingPrivilegesfromaRole.htm">Revoking Privileges from a Role</a> 36. 7. 13. <A href="/Tutorial/Oracle/0700__User-Privilege/DroppingaRole.htm">Dropping a Role</a> 36. 7. 14. <A href="/Tutorial/Oracle/0700__User-Privilege/ReginaandKristenaremanagerlevelusers.htm">Regina and Kristen are manager level users</a> 36. 7. 15. <A href="/Tutorial/Oracle/0700__User-Privilege/RichandBradaremaintenancelevelusers.htm">Rich and Brad are maintenance level users</a> 36. 7. 16. <A href="/Tutorial/Oracle/0700__User-Privilege/UnassignRole.htm">Unassign Role</a> 36. 7. 17. <A href="/Tutorial/Oracle/0700__User-Privilege/DropRole.htm">Drop Role</a> 36. 7. 18. <A href="/Tutorial/Oracle/0700__User-Privilege/Assignobjectprivilegestoroles.htm">Assign object privileges to roles</a> 36. 7. 19. <A href="/Tutorial/Oracle/0700__User-Privilege/UnassignRolefromUser.htm">Unassign Role from User</a> 36. 7. 20. <A href="/Tutorial/Oracle/0700__User-Privilege/Outputsessionrolesfromprocedure.htm">Output session roles from procedure</a> 36. 7. 21. <A href="/Tutorial/Oracle/0700__User-Privilege/Queryusergrantedroles.htm">Query user granted roles</a>

Unassign Role

SQL>
SQL> CREATE USER Alice IDENTIFIED BY simplepassword;
User created.
SQL>
SQL> GRANT CONNECT, RESOURCE to Alice;
Grant succeeded.
SQL>
SQL> REVOKE RESOURCE FROM Alice;
Revoke succeeded.
SQL>
SQL> drop user alice;
User dropped.
SQL>
SQL>
SQL>

Unassign Role from User

SQL>
SQL> REVOKE TestRole FROM Alice;