Oracle PL/SQL/User Previliege/Role

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 Role to User

   
SQL>
SQL> GRANT TestRole TO Alice;
SQL>

Creating a Role

  
CREATE ROLE new_dba;
drop role new_dba
--

define a role as a global role, which means that a user can only be authorized to use the role by an enterprise directory service.

  
CREATE ROLE supervisor IDENTIFIED GLOBALLY;
drop role supervisor;
--

Drop Role

   
SQL>
SQL> DROP ROLE TestRole;
Role dropped.

Get two database users and show their roles.

   
SQL> REM
SQL> accept user_1 prompt "Enter the first user: "
Enter the first user: accept user_2 prompt "Enter the second: "
SQL> select grantee, granted_role from dba_role_privs where
  2  grantee in (upper("&user_1"),upper("&user_2"))
  3  order by granted_role, grantee
  4  /

Grant Permissions to Role

   
SQL>
SQL> CREATE ROLE TestRole;
SQL>
SQL> GRANT UPDATE ON emp TO TestRole;
SQL>
SQL>
SQL> drop role Textrole;

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>

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>

use a password when a role is authorized by the database

  
CREATE ROLE clerk IDENTIFIED BY password;
drop row clerk;