User & Privilege & Role [펌]

[출처] http://outspace.egloos.com/2250817

 

종류              |     주고 받는 대상                |    관련 딕셔너리              |  Role 관련  딕셔너리
시스템 권한     |  권한 -> user                      |  dba_sys_privs                | role_sys_privs
                      |---------------------------
                      |  권한 -> role                       | 

객체 권한         |  권한 -> user                      | dba_tab_privs                | role_tab_privs
                       |---------------------------
                       |  권한 -> role                       |

Role  할당        |  role  -> user                      | dba_role_privs               | role_role_privs
                       |---------------------------
                       | role  -> role                       |

* 권한/role 은 user/role 에게 할당될수 있다.
* 시스템 권한은 create session, create table 등의 권한
* 객체 권한은 반드시 대상이 있다. ( 해당되는 테이블등 )
* 보통 권한의 묶음을 role 로써 관리한다.
* role_*_privs는 dba_*_privs 의 롤에 대한 정보를 보관하는 부분집합이다.
* 권한은 본래 db상에 존재하는 것이고 role은 만들고 삭제하는 것이다.

1. 모든   User의 정보/상태/테이블스페이스 보기
 sql> select username, user_id, account_status, default_tablespace, temporary_tablespace from dba_users;

SQL> select username, user_id, account_status, default_tablespace, temporary_tablespace from dba_users;

USERNAME USER_ID ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
-------------------- ---------- -------------------- -------------------- --------------------
SYS 0 OPEN SYSTEM TEMP
SYSTEM 5 OPEN SYSTEM TEMP
TE 61 OPEN TE TEMP
ST1 63 OPEN ST TEMP
ST0 62 OPEN ST TEMP
SCOTT 54 EXPIRED USERS TEMP
BI 60 EXPIRED USERS TEMP
PM 59 EXPIRED USERS TEMP
MDDATA 53 EXPIRED USERS TEMP
IX 57 EXPIRED USERS TEMP
SH 58 EXPIRED USERS TEMP
DIP 19 EXPIRED USERS TEMP
OE 56 EXPIRED USERS TEMP
HR 55 EXPIRED USERS TEMP
ST2 64 LOCKED ST TEMP
OUTLN 11 EXPIRED & LOCKED SYSTEM TEMP
MDSYS 47 EXPIRED & LOCKED SYSAUX TEMP
ORDSYS 44 EXPIRED & LOCKED SYSAUX TEMP
EXFSYS 35 EXPIRED & LOCKED SYSAUX TEMP
DMSYS 36 EXPIRED & LOCKED SYSAUX TEMP
DBSNMP 24 EXPIRED & LOCKED SYSAUX TEMP
WMSYS 26 EXPIRED & LOCKED SYSAUX TEMP
CTXSYS 37 EXPIRED & LOCKED SYSAUX TEMP
ANONYMOUS 40 EXPIRED & LOCKED SYSAUX TEMP
XDB 39 EXPIRED & LOCKED SYSAUX TEMP
ORDPLUGINS 45 EXPIRED & LOCKED SYSAUX TEMP
SI_INFORMTN_SCHEMA 46 EXPIRED & LOCKED SYSAUX TEMP
OLAPSYS 49 EXPIRED & LOCKED SYSAUX TEMP
ORACLE_OCM 25 EXPIRED & LOCKED USERS TEMP
TSMSYS 21 EXPIRED & LOCKED USERS TEMP

2. User 별 tablespace 쿼터 조회
 sql>  select username, tablespace_name, max_bytes, max_blocks from dba_ts_quotas;

SQL> select username, tablespace_name, max_bytes, max_blocks from dba_ts_quotas;

USERNAME         TABLESPACE_NAME       MAX_BYTES MAX_BLOCKS
-------------------- -------------------- ---------- ----------
ST2             INDTBS             2097152        256
TE             ST              2097152        256
ST1             INDTBS             2097152        256
TE             INDTBS             2097152        256
TE             TE               -1         -1
ST1             ST               -1         -1
DMSYS             SYSAUX           209715200      25600
OLAPSYS          SYSAUX              -1         -1
ST2             ST               -1         -1

* unlimited : -1

3. User별 granted role 조회
 sql> select grantee, granted_role from dba_role_privs;

SQL> select grantee, granted_role from dba_role_privs where grantee ='SYS' order by 1;

GRANTEE                  GRANTED_ROLE
----------------------------------------
SYS                     XDBADMIN
SYS                     IMP_FULL_DATABASE
SYS                     JAVADEBUGPRIV
SYS                     JAVAIDPRIV
SYS                     RECOVERY_CATALOG_OWNER
SYS                     DELETE_CATALOG_ROLE
SYS                     DBA
SYS                     AUTHENTICATEDUSER
SYS                     JAVAUSERPRIV
SYS                     CWM_USER
SYS                     OLAP_DBA
SYS                     XDBWEBSERVICES
SYS                     HS_ADMIN_ROLE
SYS                     EXP_FULL_DATABASE
SYS                     CTXAPP
SYS                     JAVA_ADMIN
SYS                     OEM_MONITOR
SYS                     AQ_ADMINISTRATOR_ROLE
SYS                     OLAP_USER
SYS                     JAVA_DEPLOY
SYS                     EJBCLIENT
SYS                     JAVASYSPRIV
SYS                     SCHEDULER_ADMIN
SYS                     AQ_USER_ROLE
SYS                     LOGSTDBY_ADMINISTRATOR
SYS                     EXECUTE_CATALOG_ROLE
SYS                     RESOURCE
SYS                     OLAPI_TRACE_USER
SYS                     OEM_ADVISOR
SYS                     GATHER_SYSTEM_STATISTICS
SYS                     SELECT_CATALOG_ROLE
SYS                     CONNECT

4. User별 시스템 권한 조회
 sql> select grantee, privilege from dba_sys_privs where grantee = 'SYS';

SQL> select grantee, privilege from dba_sys_privs where grantee = 'HR';

GRANTEE                  PRIVILEGE
---------------------------------------- ------------------------------------------------------------
HR                     CREATE VIEW
HR                     UNLIMITED TABLESPACE
HR                     CREATE DATABASE LINK
HR                     CREATE SEQUENCE
HR                     CREATE SESSION
HR                     ALTER SESSION
HR                     CREATE SYNONYM

5. User 생성 및 삭제
 생성 sql> create user te identified by te default tablespace temporary tablespae temp quota unlimited on te quota 2M on st;
 -- te user/ password = te / default tablespace = te / temp tablespace = temp / te tablespace 할당량 무제한/ st tablespace 2M

 삭제 sql> drop user te cascade
 -- te user 삭제 / cascade 옵션을 쓰면 시키마의 세그먼트도 삭제

6. user에게 시스템 권한 할당
  sql> grant create session to user1;

7. role에게 시스템 권한 할당
  sql> grant create table to role1;
  * grant create any table to role1 과 같이 주면 어떤 스키마에도 만들수가 있는 권한을 준다.
  * 마지막에 with admin option 을 포함하면 권한을 받은 사용자가 다른 사용자에게도 권한을 줄수 있다.

8. 유저와 롤에 대한 시스템 권한 확인

SQL> select grantee, privilege, admin_option from dba_sys_privs where grantee in ('U1','U2','U3','R1','R2');

GRANTEE                  PRIVILEGE                              ADMIN_OPT
---------------------------------------- ------------------------------------------------------------ ---------
R1                     CREATE TABLE                              NO
U1                     CREATE SESSION                           NO
U3                     CREATE SESSION                           NO
U2                     CREATE SESSION                           NO

9. user에게 객체 권한 할당
  sql> grant select on user2.table21 to user1;
  * with grant option 을 사용하면 user1이 다른 유저에게 select 권한 부여 가능

10. role 에게 객체 권한 할당
  sql> grant select on user3.user31 to role2;

11. 유저와 롤에 대한 객체 권한 확인

SQL> select grantee, owner, table_name, privilege, grantor from dba_tab_privs where grantee in ('U1','U2','U3','R1','R2') order by 1;

GRANTEE                  OWNER        TABLE_NAME         PRIVILEGE          GRANTOR
---------------------------------------- ---------- -------------------- -------------------- --------------------
R2                     U3        U31          SELECT           U3
U1                     U2        U21          SELECT           U2

12. User에게 Role 할당
  sql> grant role1, role2 to user1;

13. Role에게 Role 할당
  sql> grant role2 to role1;

14. 유저와 롤에 할당된 role 조회

SQL> select grantee, granted_role from dba_role_privs where grantee in ('U1','U2','U3','R1','R2') order by 1;

GRANTEE                  GRANTED_ROLE
---------    ----------------------------
U1                     R1
U1                     R2
U2                     R2

15. 유저에게 default role 지정
  sql> alter user user1 default role role1;