65 - [Oracle 19C] Creating private roles examples

 

 

conn as sysdba

 

show con_name
/*
CON_NAME 
------------------------------
CDB$ROOT
*/


alter session set container=orclpdb;
/*
Session altered.
*/


show con_name
/*
CON_NAME 
------------------------------
ORCLPDB
*/

 

 

 

Create Role manager

 

create role manager;
/*
Role MANAGER created.
*/


grant create table, create view, create sequence to manager;
/*
Grant succeeded.
*/


SQL> select * from role_sys_privs where role='MANAGER';

ROLE		     PRIVILEGE		  ADM COM INH
-------------------- -------------------- --- --- ---
MANAGER 	     CREATE TABLE	  NO  NO  NO
MANAGER 	     CREATE VIEW	  NO  NO  NO
MANAGER 	     CREATE SEQUENCE	  NO  NO  NO

 

 

 

create user & grant role to new user

 

CREATE USER demo1 identified by demo1234;
/*
User DEMO1 created.
*/


grant create session to demo1;
/*
Grant succeeded.
*/


grant unlimited tablespace to demo1;
/*
Grant succeeded.
*/


grant manager to demo1;
/*
Grant succeeded.
*/


CREATE ROLE QONLY;
/*
Role QONLY created.
*/


GRANT SELECT ANY TABLE TO QONLY;
/*
Grant succeeded.
*/


GRANT QONLY TO demo1;
/*
Grant succeeded.
*/


CREATE ROLE IUD_EMP;
/*
Role IUD_EMP created.
*/


GRANT INSERT,UPDATE, DELETE
ON HR.EMPLOYEES TO IUD_EMP;
/*
Grant succeeded.
*/


GRANT IUD_EMP TO demo1;
/*
Grant succeeded.
*/

 

 

 

query PRIVILEGE of new user by sys

 

SQL> SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE='IUD_EMP';

ROLE	   OWNER      TABLE_NAME	   COLUMN_NAME		PRIVILEGE	GRANTABLE   COMMON	INHERITED
---------- ---------- -------------------- -------------------- --------------- ----------- ----------- -----------
IUD_EMP    HR	      EMPLOYEES 				DELETE		NO	    NO		NO
IUD_EMP    HR	      EMPLOYEES 				INSERT		NO	    NO		NO
IUD_EMP    HR	      EMPLOYEES 				UPDATE		NO	    NO		NO

 

 

 

Login as new user, DEMO1

 

[oracle@test ~]$ sqlplus demo1/demo1234@orclpdb

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 4 15:27:44 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Mon Dec 04 2023 14:45:43 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

 

 

 

query PRIVILEGE of new user by DEMO1 , session_privs 와 user_sys_privs 의 차이

 

-- 사용자가 받은 시스템 권한을 표시(직접 또는 롤을 통해서 할당된 모든 시스템 권한 표시)
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
SELECT ANY TABLE
CREATE VIEW
CREATE SEQUENCE

6 rows selected.



-- 사용자가 받은 시스템 권한을 표시(직접 할당된 모든 시스템 권한 표시)
-- role 을 통해서 할당된 권한은 표시되지 않는다. 
SQL> select * from user_sys_privs;

USERNAME   PRIVILEGE		ADMIN_OPTION   COMMON	  INHERITED
---------- -------------------- -------------- ---------- -------------
DEMO1	   CREATE SESSION	NO	       NO	  NO
DEMO1	   UNLIMITED TABLESPACE NO	       NO	  NO

 

 

# 본인에게 할달된 role 을 확인
select * from user_role_privs;
/*
USERNAME   GRANTED_ROLE    ADM DEL DEF OS_ COM INH
---------- --------------- --- --- --- --- --- ---
DEMO1      IUD_EMP         NO  NO  YES NO  NO  NO 
DEMO1      MANAGER         NO  NO  YES NO  NO  NO 
DEMO1      QONLY           NO  NO  YES NO  NO  NO 
*/


# 본인에게 할달된 role 하위에 속해있는 시스템 권한 확인
select * from role_sys_privs;
/*
ROLE       PRIVILEGE            ADMIN_OPTION         COMMON               INHERITED           
---------- -------------------- -------------------- -------------------- --------------------
MANAGER    CREATE TABLE         NO                   NO                   NO                  
MANAGER    CREATE VIEW          NO                   NO                   NO                  
QONLY      SELECT ANY TABLE     NO                   NO                   NO                  
MANAGER    CREATE SEQUENCE      NO                   NO                   NO                  
*/


# 본인에게 할달된 role 하위에 속해있는 테이블 권한 확인
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE='IUD_EMP'
/*
ROLE       OWNER      TABLE_NAME      COLUMN_NAME     PRIVILEGE            GRANTABLE       COMMON               INHERITED           
---------- ---------- --------------- --------------- -------------------- --------------- -------------------- --------------------
IUD_EMP    HR         EMPLOYEES                       DELETE               NO              NO                   NO                  
IUD_EMP    HR         EMPLOYEES                       INSERT               NO              NO                   NO                  
IUD_EMP    HR         EMPLOYEES                       UPDATE               NO              NO                   NO                  
*/

 

 

 

create table studnet
( student_id number,
  studnet_name varchar2(100)
);
/*
Table STUDNET created.
*/


grant select on studnet to public; 
/*
해당 테이블에 대한 모든 권한을 다른 유저들이 사용가능하게 할당.

Grant succeeded.
*/


select * from hr.locations
/*
상기 테이블은 QONLY role 에 의해서 접근 가능, 다른 스키마의 테이블에 대한 접근권한이 생성됨
*/


select * from demo.emp
/*
상기 테이블은 QONLY role 에 의해서 접근 가능, 다른 스키마의 테이블에 대한 접근권한이 생성됨
*/


update hr.employees set salary=salary+10
where employee_id=100;
/*
상기 테이블은 IUD_EMP role 에 의해서 접근 가능.
*/