251208 TIL

User 관리(Cont.)

유저 생성

CREATE USER 유저이름
IDENTIFIED BY 패스워드;

CREATE USER 유저이름
IDENTIFIED BY 패스워드
DEFAULT TABLESPACE 테이블스페이스
TEMPORARY TABLESPACE 임시테이블스페이스
QUOTA unlimited ON 테이블스페이스
QUOTA 10m ON 테이블스페이스
ACCOUNT UNLOCk | LOCK
PROFILE [프로파일 | DEFAULT]
PASSWORD EXPIRE;

• 유저이름 : 데이터베이스 레벨에서 고유한 이름으로 설정
• PASSWD EXPIRE : 유저가 로그인할 때 암호를 재설정하도록 하는 설정
• ACCOUNT : 계정을 LOCKING 할 수 있음
• PROFILE : 리소스, 패스워드 관리
• QUOTA : 테이블스페이스를 사용할 수 있는 권한(양)
• DEFAULT TABLESPACE : 세그먼트 생성 시에 테이블스페이스를 지정하지 않으면 이곳에 저장하도록 함

-- 예)
-- tablespace를 명시하지 않았으므로 hr의 default tablespace에 저장
CREATE TABLE hr.test(id number); 

-- users 테이블스페이스에 저장
CREATE TABLE hr.test(id number) TABLESPACE users;

• TEMPORARY TABLESPACE : 정렬, 해시, 비트맵 인덱스 생성, 병합 시 메모리에서 처리를 다 못 할 경우 사용하는 공간

유저 수정

ALTER USER 유저이름
IDENTIFIED BY 패스워드
DEFAULT TABLESPACE 테이블스페이스
TEMPORARY TABLESPACE 임시테이블스페이스
QUOTA unlimited ON 테이블스페이스
QUOTA 10m ON 테이블스페이스
ACCOUNT UNLOCk | LOCK
PROFILE [프로파일 | DEFAULT]
PASSWORD EXPIRE;

유저 삭제

DROP USER 유저이름 CASCADE;

• CASCADE : 유저가 생성한 객체들을 먼저 삭제한 후 유저 삭제하는 옵션
• 현재 접속 중인 유저는 삭제 불가

권한(privilege) 관리

• 권한은 특정한 SQL문을 실행하거나 다른 유저가 소유한 객체를 액세스할 수 있는 권한

시스템 권한

• 데이터베이스에 영향을 줄 수 있는 권한
• 권한 관리는 SYS가 함

with admin option

• with admin option으로 받은 시스템 권한에 대해서는 다른 유저에게 시스템 권한을 부여하고 권한을 취소할 수 있음

객체 권한

• 다른 유저가 소유한 객체를 액세스 할 수 있는 권한
• 권한 관리는 SYS, 객체 소유자가 함
• table : select, insert, update, delete, alter, index, references
• view : select, insert, update, delete
• sequence : select, alter
• procedure, function, package : execute

with grant option

• with grant option으로 받은 객체 권한에 대해서 다른 유저에게 객체 권한을 부여하고 취소할 수 있음

테이블 생성 시 필요한 권한

• create table
• tablespace quota

SYS - 테이블스페이스 생성

-- 테이블스페이스 생성
create tablespace insa_tbs
datafile '/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf' size 10m autoextend on
extent management local uniform size 1m
segment space management auto;

-- 임시 테이블스페이스 생성
create temporary tablespace insa_temp
tempfile '/u01/app/oracle/oradata/ORA19C/insa_temp01.dbf' size 10m autoextend on
extent management local uniform size 1m
segment space management manual;

select * from dba_data_files;
select * from dba_tablespaces;

SYS - INSA 유저 생성 및 create session 권한 부여

-- insa 유저 생성
create user insa
identified by oracle
default tablespace insa_tbs
temporary tablespace insa_temp
quota 1m on insa_tbs
password expire;

select * from dba_users where username = 'INSA';

-- insa 유저에게 create session 시스템 권한 부여
grant create session to insa;

select * from dba_sys_privs where grantee = 'INSA';

INSA - 유저 생성 후 첫 접속 시 비밀번호 재설정 필요

[oracle@ora19c ~]$ sqlplus insa/oracle

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 8 18:10:38 2025
Version 19.3.0.0.0

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

# 첫 접속 시 패스워드가 만료됨
ERROR:
ORA-28001: the password has expired

# 사용자가 패스워드 재설정
Changing password for insa
New password:
Retype new password:
Password changed

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

INSA - 유저 권한 조회

# INSA가 가진 시스템 권한 조회
INSA@ora19c> select * from user_sys_privs;

USERNAME                       PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
INSA                           CREATE SESSION                 NO  NO  NO

# INSA 유저의 기본 테이블스페이스와 임시 테이블스페이스 조회
INSA@ora19c> select default_tablespace, temporary_tablespace from user_users;

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------
INSA_TBS                       INSA_TEMP

# INSA 유저가 할당받은 테이블스페이스 공간 조회
INSA@ora19c> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
INSA_TBS                                0    1048576          0        128 NO

INSA - 테이블 생성 불가

# 테이블 생성 불가 -> create table 시스템 권한이 없음
INSA@ora19c> create table insa_new(id number, name varchar2(30));
create table insa_new(id number, name varchar2(30))
*
ERROR at line 1:
ORA-01031: insufficient privileges

SYS - create table 권한 부여

-- insa에게 create table 시스템 권한 부여
grant create table to insa;
select * from dba_sys_privs where grantee = 'INSA';

with admin option으로 시스템 권한 부여

SYS - with admin option으로 create table 권한 부여

-- create table 권한 회수
revoke create table from insa;

-- insa에게 with admin option으로 create table 시스템 권한 부여
grant create table to insa with admin option;
select * from dba_sys_privs where grantee = 'INSA';

INSA - 테이블 생성 가능

# create table 권한 생김
INSA@ora19c> select * from user_sys_privs;

USERNAME                       PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
INSA                           CREATE TABLE                   YES NO  NO
INSA                           CREATE SESSION                 NO  NO  NO

# 테이블 생성 가능
INSA@ora19c> create table insa_new(id number, name varchar2(30));

Table created.

SYS - INSA_BUHA 유저 생성

-- insa_buha 유저 생성
create user insa_buha
identified by oracle
default tablespace insa_tbs
temporary tablespace insa_temp
quota 1m on insa_tbs;

select * from dba_ts_quotas where username = 'INSA_BUHA';

-- insa_buha에게 create session 시스템 권한 부여
grant create session to insa_buha;

select * from dba_sys_privs where grantee = 'INSA_BUHA';

INSA_BUHA - 유저가 가진 시스템 권한 조회(create table X)

INSA_BUHA@ora19c> select * from user_sys_privs;

USERNAME                       PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
INSA_BUHA                      CREATE SESSION                 NO  NO  NO

INSA - INSA_BUHA에게 create table 권한 부여

• sys로부터 with admin option으로 create table 권한을 받았기 때문에 다른 유저에게 create table 권한 부여 가능

INSA@ora19c> grant create table to insa_buha;

Grant succeeded.

INSA_BUHA - 테이블 생성 가능

# create table 권한 생김
INSA_BUHA@ora19c> select * from user_sys_privs;

USERNAME                       PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
INSA_BUHA                      CREATE TABLE                   NO  NO  NO
INSA_BUHA                      CREATE SESSION                 NO  NO  NO

INSA_BUHA@ora19c> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
INSA_TBS                                0    1048576          0        128 NO

INSA_BUHA@ora19c> select default_tablespace, temporary_tablespace from user_users;

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------
INSA_TBS                       INSA_TEMP

# 테이블 생성 가능
INSA_BUHA@ora19c> create table buha(id number, name varchar2(30));

Table created.

INSA_BUHA@ora19c> select tablespace_name from user_tables where table_name = 'BUHA';

TABLESPACE_NAME
------------------------------
INSA_TBS 

INSA_BUHA@ora19c> insert into buha(id, name) values(1, 'scott');

1 row created.

INSA_BUHA@ora19c> commit;

Commit complete.

INSA_BUHA@ora19c> select * from buha;

        ID NAME
---------- ------------------------------
         1 scott       

INSA - INSA_BUHA에 대한 create table 권한 회수

• sys로부터 with admin option으로 create table 권한을 받았기 때문에 다른 유저에게 부여했던 create table 권한 회수도 가능

# INSA_BUHA에 대한 create table 권한 회수
INSA@ora19c> revoke create table from insa_buha;

Revoke succeeded.

INSA_BUHA - create table 권한 회수됨

INSA_BUHA@ora19c> select * from user_sys_privs;

USERNAME                       PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
INSA_BUHA                      CREATE SESSION                 NO  NO  NO

SYS - INSA에게 with admin option으로 create sequence 시스템 권한 부여

# INSA에게 create sequence 시스템 권한 부여
grant create sequence to insa with admin option;
select * from dba_sys_privs where grantee = 'INSA'

INSA - 시퀀스 생성 가능

INSA@ora19c> select * from user_sys_privs;

USERNAME                       PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
INSA                           CREATE SEQUENCE                YES NO  NO
INSA                           CREATE TABLE                   YES NO  NO
INSA                           CREATE SESSION                 NO  NO  NO

# 시퀀스 생성
INSA@ora19c> create sequence id_seq
start with 1
maxvalue 10
increment by 1
nocycle
nocache; 

# 사용자가 소유한 시퀀스 조회
INSA@ora19c> select * from user_sequences where sequence_name = 'ID_SEQ';

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S S K
------------------------------ ---------- ---------- ------------ - - ---------- ----------- - - - - -
ID_SEQ                                  1         10            1 N N          0           1 N N N N N

# 사용자가 소유한 객체 조회
INSA@ora19c> select * from tab;

TNAME                          TABTYPE        CLUSTERID
------------------------------ ------------- ----------
INSA_NEW                       TABLE

# 데이터 입력
INSA@ora19c> insert into insa_new(id, name) values(id_seq.nextval, 'sophia');

1 row created.

INSA@ora19c> insert into insa_new(id, name) values(id_seq.nextval, 'liam');

1 row created.

INSA@ora19c> insert into insa_new(id, name) values(id_seq.nextval, 'noah');

1 row created.

INSA@ora19c> commit;

Commit complete.

INSA@ora19c> select * from insa_new;

        ID NAME
---------- ------------------------------
         1 sophia
         2 liam
         3 noah

# 현재 사용한 시퀀스 번호 조회
INSA@ora19c> select id_seq.currval from dual;

   CURRVAL
----------
         3

# 다음 사용 가능한 시퀀스 번호 조회(nocache일 경우)         
INSA@ora19c> select last_number from user_sequences where sequence_name = 'ID_SEQ';

LAST_NUMBER
-----------
          4         

INSA - INSA_BUHA에게 create sequence 권한 부여

INSA@ora19c> select * from user_sys_privs;

USERNAME                       PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
INSA                           CREATE SEQUENCE                YES NO  NO
INSA                           CREATE TABLE                   YES NO  NO
INSA                           CREATE SESSION                 NO  NO  NO

INSA@ora19c> grant create sequence to insa_buha;

Grant succeeded.

INSA_BUHA - 시퀀스 생성 가능

INSA_BUHA@ora19c> select * from user_sys_privs;

USERNAME                       PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
INSA_BUHA                      CREATE SEQUENCE                NO  NO  NO
INSA_BUHA                      CREATE SESSION                 NO  NO  NO

INSA_BUHA@ora19c> create sequence buha_seq
start with 2
maxvalue 10
increment by 1
nocycle
nocache;

INSA_BUHA@ora19c> select * from user_sequences where sequence_name = 'BUHA_SEQ';

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S S K
------------------------------ ---------- ---------- ------------ - - ---------- ----------- - - - - -
BUHA_SEQ                                1         10            1 N N          0           2 N N N N N

INSA_BUHA@ora19c> select * from buha;

        ID NAME
---------- ------------------------------
         1 scott

INSA_BUHA@ora19c> insert into buha(id, name) values(buha_seq.nextval, 'emma');

1 row created.

INSA_BUHA@ora19c> insert into buha(id, name) values(buha_seq.nextval, 'james');

1 row created.

INSA_BUHA@ora19c> commit;

Commit complete.

INSA_BUHA@ora19c> select * from buha;

        ID NAME
---------- ------------------------------
         1 scott
         2 emma
         3 james

with admin option으로 부여된 시스템 권한 회수

SYS - create sequence 권한 회수

select * from dba_sys_privs where grantee in ('INSA', 'INSA_BUHA');

revoke create sequence from insa;
select * from dba_sys_privs where grantee in ('INSA', 'INSA_BUHA');

• with admin option을 사용하여 시스템 권한을 부여했는지 여부와 무관하게 시스템 권한을 회수할 때는 연쇄적으로 취소되지 않음
• 개별로 권한을 회수해야 함

revoke create sequence from insa_buha;
select * from dba_sys_privs where grantee in ('INSA', 'INSA_BUHA')

with grant option으로 객체 권한 부여

SYS - INSA에게 테이블 조회 객체 권한 부여

# INSA에게 select on hr.employees, hr.departments 객체 권한 부여
grant select on hr.employees to insa with grant option;
grant select on hr.departments to insa with grant option;

select * from dba_tab_privs where grantee = 'INSA';

INSA - INSA_BUHA에게 객체 권한 부여

• sys로부터 with grant option으로 select 객체 권한을 부여 받았기 때문에 다른 유저에게 select 객체 권한 부여 가능

# INSA가 가진 객체 권한 조회
INSA@ora19c> select * from user_tab_privs;

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                    PRIVILEGE                       GRA HIE COM TYPE                     INH
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- --- ------------------------ ---
INSA                           HR                             DEPARTMENTS                    HR                         SELECT                          YES NO  NO  TABLE                    NO
INSA                           HR                             EMPLOYEES                      HR                         SELECT                          YES NO  NO  TABLE                    NO
PUBLIC                         SYS                            INSA                           INSA                       INHERIT PRIVILEGES              NO  NO  NO  USER                     NO

# INSA_BUHA에게 hr.employees, hr.departments 테이블에 대한 select 객체 권한 부여
INSA@ora19c> grant select on hr.employees to insa_buha;

Grant succeeded.

INSA@ora19c> grant select on hr.departments to insa_buha;

Grant succeeded.

INSA_BUHA - 유저가 가진 객체 권한 조회(select on hr.employees, select on hr.departments)

# INSA_BUHA가 가진 객체 권한 조회
INSA_BUHA@ora19c> select * from user_tab_privs;

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                    PRIVILEGE                       GRA HIE COM TYPE                     INH
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- --- ------------------------ ---
INSA_BUHA                      HR                             DEPARTMENTS                    INSA                       SELECT                          NO  NO  NO  TABLE                    NO
INSA_BUHA                      HR                             EMPLOYEES                      INSA                       SELECT                          NO  NO  NO  TABLE                    NO
PUBLIC                         SYS                            INSA_BUHA                      INSA_BUHA                  INHERIT PRIVILEGES              NO  NO  NO  USER                     NO

INSA - INSA_BUHA에게 부여했던 객체 권한 회수

• sys로부터 with grant option으로 객체 권한을 부여 받았기 때문에 다른 유저에게 부여했던 객체 권한 회수 가능

# INSA_BUHA에게 부여했던 select on hr.employees 객체 권한 회수
INSA@ora19c> revoke select on hr.employees from insa_buha;

Revoke succeeded.

INSA_BUHA - hr.employees 조회 불가

INSA_BUHA@ora19c> select * from hr.employees;
select * from hr.employees
                 *
ERROR at line 1:
ORA-00942: table or view does not exist

INSA_BUHA@ora19c> select * from user_tab_privs;

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                    PRIVILEGE                       GRA HIE COM TYPE                     INH
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- --- ------------------------ ---
INSA_BUHA                      HR                             DEPARTMENTS                    INSA                       SELECT                          NO  NO  NO  TABLE                    NO
PUBLIC                         SYS                            INSA_BUHA                      INSA_BUHA                  INHERIT PRIVILEGES              NO  NO  NO  USER                     NO

with grant option으로 부여된 객체 권한 회수

SYS - INSA에게 부여했던 객체 권한 회수

select * from dba_tab_privs where grantee in ('INSA', 'INSA_BUHA');

# INSA가 가진 select on hr.departments 객체 권한 회수
revoke select on hr.departments from insa;
select * from dba_tab_privs where grantee in ('INSA', 'INSA_BUHA');

• with grant option을 사용하여 부여한 객체 권한을 회수하면 연쇄적으로 회수됨

Role

• 관련성이 있는 권한들을 하나로 묶어서 관리하는 객체
• 관리의 편리성
• 예) 프로그래머들에게 부여할 시스템 권한
  • create session
  • create procedure
  • create trigger
  • create view

Role 생성

SYS - prog 롤 생성 및 권한 부여

-- prog 롤 생성
create role prog;
select * from dba_roles where role = 'PROG';

-- 롤에 시스템 권한 부여
grant create session, create procedure, create trigger, create view to prog;
select * from dba_sys_privs where grantee = 'PROG';

-- 롤에 객체 권한 부여
grant select on hr.departments to prog;
select * from dba_tab_privs where grantee = 'PROG';

-- 롤을 유저에게 부여
grant prog to insa;
select * from dba_role_privs where grantee = 'INSA';

INSA - 현재 세션이 가진 롤 조회

• 롤 부여 후에는 재접속 필요

INSA@ora19c> select * from session_roles;

no rows selected

INSA@ora19c> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEL DEF OS_ COM INH
------------------------------ ------------------------------ --- --- --- --- --- ---
INSA                           PROG                           NO  NO  YES NO  NO  NO

INSA@ora19c> conn insa/insa
Connected.
INSA@ora19c> select * from session_roles;

ROLE
------------------------------
PROG

INSA@ora19c> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEL DEF OS_ COM INH
------------------------------ ------------------------------ --- --- --- --- --- ---
INSA                           PROG                           NO  NO  YES NO  NO  NO

SYS - mgr 롤 생성 및 권한 부여(select any table)

-- mgr 롤 생성
create role mgr;

-- mgr 롤에 select any table 시스템 권한 부여
grant select any table to mgr;
select * from dba_roles where role = 'MGR';

-- mgr 롤을 유저에게 부여
grant mgr to insa;
select * from dba_role_privs where grantee = 'INSA';

INSA

# 재접속
INSA@ora19c> conn insa/insa

# 롤이 가진 시스템 권한 조회
INSA@ora19c> select * from role_sys_privs;

ROLE                           PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
PROG                           CREATE VIEW                    NO  NO  NO
PROG                           CREATE SESSION                 NO  NO  NO
PROG                           CREATE TRIGGER                 NO  NO  NO
MGR                            SELECT ANY TABLE               NO  NO  NO
PROG                           CREATE PROCEDURE               NO  NO  NO

# 롤이 가진 객체 권한 조회
INSA@ora19c> select * from role_tab_privs;

ROLE                           OWNER                          TABLE_NAME                     COLUMN_NAME                PRIVILEGE                       GRA COM INH
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- ---
PROG                           HR                             DEPARTMENTS                                               SELECT                          NO  NO  NO

# 유저가 가진 시스템 권한 조회
INSA@ora19c> select * from user_sys_privs;

USERNAME                       PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
INSA                           CREATE TABLE                   YES NO  NO
INSA                           CREATE SESSION                 NO  NO  NO

# 유저가 가진 객체 권한 조회
INSA@ora19c> select * from user_tab_privs;

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                    PRIVILEGE                       GRA HIE COM TYPE                     INH
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- --- ------------------------ ---
INSA                           HR                             EMPLOYEES                      HR                         SELECT                          YES NO  NO  TABLE                    NO
PUBLIC                         SYS                            INSA                           INSA                       INHERIT PRIVILEGES              NO  NO  NO  USER                     NO

# mgr 롤을 통해 모든 테이블 조회 가능
INSA@ora19c> select count(*) from hr.locations;

  COUNT(*)
----------
        23

# select any table 권한이 있어도 딕셔너리 테이블은 조회 불가
INSA@ora19c> select * from sys.obj$;
select * from sys.obj$
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

• select any table 권한이 있을 경우 딕셔너리 테이블을 제외한 모든 테이블에 대해 조회 가능

Default Role

SYS - default role 활성화

• 일반적으로 role을 유저한테 부여하면 default role로 활성화됨

select * from dba_role_privs where grantee = 'INSA';

• 유저에게 부여한 롤 전부에 대해 default role 활성화

alter user insa default role all;

SYS - mgr을 제외하고 모든 롤에 대해 default role 활성화

alter user insa default role all except mgr;
select * from dba_role_privs where grantee = 'INSA';

INSA - mgr 롤이 비활성화 되었기 때문에 hr.locations 조회 불가

# 재접속
INSA@ora19c> conn insa/insa
Connected.

# mgr롤 비활성화
INSA@ora19c> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEL DEF OS_ COM INH
------------------------------ ------------------------------ --- --- --- --- --- ---
INSA                           MGR                            NO  NO  NO  NO  NO  NO
INSA                           PROG                           NO  NO  YES NO  NO  NO

# hr.locations 테이블 조회 불가
# insa 유저와 prog 롤이 가지고 있는 객체 권한을 통해 hr.employees와 hr.departments 테이블은 조회 가능
INSA@ora19c> select count(*) from hr.locations;
select count(*) from hr.locations
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

SYS - default role 비활성화

alter user insa default role none;
select * from dba_role_privs where grantee = 'INSA';

INSA - 롤이 가진 권한 모두 사용 불가

# 재접속
INSA@ora19c> conn insa/insa
Connected.

# 모든 롤이 비활성화
INSA@ora19c> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEL DEF OS_ COM INH
------------------------------ ------------------------------ --- --- --- --- --- ---
INSA                           MGR                            NO  NO  NO  NO  NO  NO
INSA                           PROG                           NO  NO  NO  NO  NO  NO

INSA@ora19c> select * from role_sys_privs;

ROLE                           PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
PROG                           CREATE VIEW                    NO  NO  NO
PROG                           CREATE SESSION                 NO  NO  NO
PROG                           CREATE TRIGGER                 NO  NO  NO
MGR                            SELECT ANY TABLE               NO  NO  NO
PROG                           CREATE PROCEDURE               NO  NO  NO

INSA@ora19c> select * from role_tab_privs;

ROLE                           OWNER                          TABLE_NAME                     COLUMN_NAME                PRIVILEGE                       GRA COM INH
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- ---
PROG                           HR                             DEPARTMENTS                                               SELECT                          NO  NO  NO

INSA@ora19c> select * from user_sys_privs;

USERNAME                       PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
INSA                           CREATE TABLE                   YES NO  NO
INSA                           CREATE SESSION                 NO  NO  NO

INSA@ora19c> select * from user_tab_privs;

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                    PRIVILEGE                       GRA HIE COM TYPE                     INH
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- --- ------------------------ ---
INSA                           HR                             EMPLOYEES                      HR                         SELECT                          YES NO  NO  TABLE                    NO
PUBLIC                         SYS                            INSA                           INSA                       INHERIT PRIVILEGES              NO  NO  NO  USER                     NO

# hr.locations 조회 불가 -> mgr의 select any table 시스템 권한 비활성화
INSA@ora19c> select count(*) from hr.locations;
select count(*) from hr.locations
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

# hr.departments 조회 불가 -> prog의 select on hr.departments 객체 권한 비활성화
INSA@ora19c> select count(*) from hr.departments;
select count(*) from hr.departments
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

# hr.employees 조회 가능 -> insa 유저가 select on hr.employees 객체 권한을 가지고 있기 때문
INSA@ora19c> select count(*) from hr.employees;

  COUNT(*)
----------
       107

SYS - prog를 제외하고 모든 롤에 대해 default role 활성화

alter user insa default role all except prog;
select * from dba_role_privs where grantee = 'INSA';

INSA - prog 롤이 비활성화 되었기 때문에 create view, create trigger, create procedure 사용 불가

# 재접속
INSA@ora19c> conn insa/insa
Connected.

# prog롤 비활성화
INSA@ora19c> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEL DEF OS_ COM INH
------------------------------ ------------------------------ --- --- --- --- --- ---
INSA                           MGR                            NO  NO  YES NO  NO  NO
INSA                           PROG                           NO  NO  NO  NO  NO  NO

INSA@ora19c> select * from role_sys_privs;

ROLE                           PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
PROG                           CREATE VIEW                    NO  NO  NO
PROG                           CREATE SESSION                 NO  NO  NO
PROG                           CREATE TRIGGER                 NO  NO  NO
MGR                            SELECT ANY TABLE               NO  NO  NO
PROG                           CREATE PROCEDURE               NO  NO  NO

INSA@ora19c> select * from role_tab_privs;

ROLE                           OWNER                          TABLE_NAME                     COLUMN_NAME                PRIVILEGE                       GRA COM INH
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- ---
PROG                           HR                             DEPARTMENTS                                               SELECT                          NO  NO  NO

INSA@ora19c> select * from user_sys_privs;

USERNAME                       PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
INSA                           CREATE TABLE                   YES NO  NO
INSA                           CREATE SESSION                 NO  NO  NO

INSA@ora19c> select * from user_tab_privs;

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                    PRIVILEGE                       GRA HIE COM TYPE                     INH
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- --- ------------------------ ---
INSA                           HR                             EMPLOYEES                      HR                         SELECT                          YES NO  NO  TABLE                    NO
PUBLIC                         SYS                            INSA                           INSA                       INHERIT PRIVILEGES              NO  NO  NO  USER                     NO

# mgr이 select any table 권한이 있기 때문에 모든 테이블 조회 가능
INSA@ora19c> select count(*) from hr.locations;

  COUNT(*)
----------
        23

INSA@ora19c> select count(*) from hr.departments;

  COUNT(*)
----------
        27

INSA@ora19c> select count(*) from hr.employees;

  COUNT(*)
----------
       107

-- prog 롤이 비활성화되었기 때문에 create view 권한 사용 불가
INSA@ora19c> create or replace view emp_view as select * from hr.employees;
create or replace view emp_view as select * from hr.employees
                       *
ERROR at line 1:
ORA-01031: insufficient privileges       

SYS - 유저에게 부여한 롤 전부에 대해 default role 활성화

alter user insa default role all;
select * from dba_role_privs where grantee = 'INSA';

INSA - 유저 및 롤이 가진 권한 모두 사용 가능

INSA@ora19c> conn insa/insa
Connected.
INSA@ora19c> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEL DEF OS_ COM INH
------------------------------ ------------------------------ --- --- --- --- --- ---
INSA                           MGR                            NO  NO  YES NO  NO  NO
INSA                           PROG                           NO  NO  YES NO  NO  NO

INSA@ora19c> select * from role_sys_privs;

ROLE                           PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
PROG                           CREATE VIEW                    NO  NO  NO
PROG                           CREATE SESSION                 NO  NO  NO
PROG                           CREATE TRIGGER                 NO  NO  NO
MGR                            SELECT ANY TABLE               NO  NO  NO
PROG                           CREATE PROCEDURE               NO  NO  NO

INSA@ora19c> select * from role_tab_privs;

ROLE                           OWNER                          TABLE_NAME                     COLUMN_NAME                PRIVILEGE                       GRA COM INH
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- ---
PROG                           HR                             DEPARTMENTS                                               SELECT                          NO  NO  NO

INSA@ora19c> select * from user_sys_privs;

USERNAME                       PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
INSA                           CREATE TABLE                   YES NO  NO
INSA                           CREATE SESSION                 NO  NO  NO

INSA@ora19c> select * from user_tab_privs;

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                    PRIVILEGE                       GRA HIE COM TYPE                     INH
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- --- ------------------------ ---
INSA                           HR                             EMPLOYEES                      HR                         SELECT                          YES NO  NO  TABLE                    NO
PUBLIC                         SYS                            INSA                           INSA                       INHERIT PRIVILEGES              NO  NO  NO  USER                     NO

INSA@ora19c> select count(*) from hr.locations;

  COUNT(*)
----------
        23

INSA@ora19c> select count(*) from hr.departments;

  COUNT(*)
----------
        27

INSA@ora19c> select count(*) from hr.employees;

  COUNT(*)
----------
       107

INSA@ora19c> create or replace view emp_view as select * from hr.employees;

View created.

View 생성 시 주의 사항

INSA로부터 hr.employee 조회 권한 회수

revoke select on hr.employees from insa;
select * from dba_tab_privs where grantee in ('INSA', 'PROG', 'MGR');

뷰 생성 불가

INSA@ora19c> create or replace view emp_view
as
select * from hr.employees;
select * from hr.employees
                 *
ERROR at line 3:
ORA-01031: insufficient privileges

INSA@ora19c> create or replace view dept_view
as
select * from hr.departments;
select * from hr.departments
                 *
ERROR at line 3:
ORA-01031: insufficient privileges

• 서브 쿼리 테이블에 대해 객체 권한을 직접 부여해줘야 함
• role이나 select any table을 통해 부여한 경우에는 권한 불충분 오류 발생

직접 권한 부여

grant select on hr.employees to insa;
grant select on hr.departments to insa;
select * from dba_tab_privs where grantee = 'INSA';

INSA@ora19c> create or replace view emp_view
as
select * from hr.employees;

View created.

INSA@ora19c> create or replace view dept_view
as
select * from hr.departments;

View created.

Procedure 생성 시 주의 사항

INSA로부터 hr.employee, hr.departments 조회 권한 회수

revoke select on hr.employees from insa;
revoke select on hr.departments from insa;
select * from dba_tab_privs where grantee = 'INSA';

프로시저 생성 불가

• 프로시저에서 호출하는 객체에 대한 권한은 직접 받은 객체 권한 필요

INSA@ora19c> create or replace procedure dept_proc(p_id number)
is
    v_rec hr.departments%rowtype;
begin
    select *
    into v_rec
    from hr.departments
    where department_id = p_id;

    dbms_output.put_line(p_id || ' ' || v_rec.department_name);
exception
    when no_data_found then
        raise_application_error(-20000, '부서는 존재하지 않습니다.');
end dept_proc;
/

Warning: Procedure created with compilation errors.

INSA@ora19c> show error
Errors for PROCEDURE DEPT_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/11     PL/SQL: Item ignored
3/11     PLS-00201: identifier 'HR.DEPARTMENTS' must be declared
5/5      PL/SQL: SQL Statement ignored
7/13     PL/SQL: ORA-00942: table or view does not exist
10/5     PL/SQL: Statement ignored
10/41    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

직접 권한 부여

grant select on hr.departments to insa;
select * from dba_tab_privs where grantee = 'INSA';

INSA@ora19c> create or replace procedure dept_proc(p_id number)
is
    v_rec hr.departments%rowtype;
begin
    select *
        into v_rec
    from hr.departments
    where department_id = p_id;

    dbms_output.put_line(p_id || ' ' || v_rec.department_name);
exception
    when no_data_found then
        raise_application_error(-20000, '부서는 존재하지 않습니다.');
end dept_proc;
/

Procedure created.

INSA@ora19c> set serveroutput on

INSA@ora19c> exec dept_proc(10)
10 Administration

PL/SQL procedure successfully completed.

INSA@ora19c> exec dept_proc(300)
BEGIN dept_proc(300); END;

*
ERROR at line 1:
ORA-20000: 부서는 존재하지 않습니다.
ORA-06512: at "INSA.DEPT_PROC", line 13
ORA-06512: at line 1

권한 회수 → INVALID

-- sys
revoke select on hr.departments from insa;

-- insa
select * from user_objects where object_name = 'DEPT_PROC';

권한 부여 → 재컴파일 → VALID

-- sys
grant select on hr.departments to insa;

-- insa
-- 프로시저 재컴파일
create or replace procedure dept_proc(p_id number)
is
    ...
begin
    ...
end dept_proc;
/

select * from user_objects where object_name = 'DEPT_PROC';

Role 삭제

drop role mgr;

패스워드를 설정하여 롤 생성

create role mgr identified by oracle;
select * from dba_roles where role = 'MGR';

grant select any table to mgr;
grant mgr to insa;

select * from dba_role_privs where grantee = 'INSA';

• 패스워드가 설정된 롤은 유저에게 부여했을 때 default role에서 자동으로 제외됨

INSA@ora19c> conn insa/insa
Connected.

# mgr default_role이 비활성화 상태
INSA@ora19c> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEL DEF OS_ COM INH
------------------------------ ------------------------------ --- --- --- --- --- ---
INSA                           MGR                            NO  NO  NO  NO  NO  NO
INSA                           PROG                           NO  NO  YES NO  NO  NO

# select any table 권한 존재
INSA@ora19c> select * from role_sys_privs;

ROLE                           PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
PROG                           CREATE VIEW                    NO  NO  NO
PROG                           CREATE SESSION                 NO  NO  NO
PROG                           CREATE TRIGGER                 NO  NO  NO
MGR                            SELECT ANY TABLE               NO  NO  NO
PROG                           CREATE PROCEDURE               NO  NO  NO

# hr.locations 조회 불가
INSA@ora19c> select count(*) from hr.locations;
select count(*) from hr.locations
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

롤에 설정된 패스워드 해제

alter role mgr not identified;

select * from dba_roles where role = 'MGR';
select * from dba_role_privs where grantee = 'INSA';

• 패스워드 해제한 롤은 자동으로 default role로 활성화됨

INSA@ora19c> conn insa/insa
Connected.

# mgr default_role이 활성화 상태
INSA@ora19c> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEL DEF OS_ COM INH
------------------------------ ------------------------------ --- --- --- --- --- ---
INSA                           MGR                            NO  NO  YES NO  NO  NO
INSA                           PROG                           NO  NO  YES NO  NO  NO

# select any table 권한 존재
INSA@ora19c> select * from role_sys_privs;

ROLE                           PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
PROG                           CREATE VIEW                    NO  NO  NO
PROG                           CREATE SESSION                 NO  NO  NO
PROG                           CREATE TRIGGER                 NO  NO  NO
MGR                            SELECT ANY TABLE               NO  NO  NO
PROG                           CREATE PROCEDURE               NO  NO  NO

# hr.locations 조회 가능
INSA@ora19c> select count(*) from hr.locations;

  COUNT(*)
----------
        23

권한 관련 뷰

-- 세션이 가진 롤
select * from session_roles;
-- 유저가 가진 롤
select * from user_role_privs;
-- 롤이 가진 시스템 권한
select * from role_sys_privs;
-- 유저가 가진 시스템 권한
select * from user_sys_privs;
-- 세션이 가진 시스템 권한
select * from session_privs;
-- 롤이 가진 객체 권한
select * from role_tab_privs;
-- 유저가 가진 객체 권한
select * from user_tab_privs;