Database/Oracle Database

[오라클] 디렉토리 조회, 생성, 권한 관련 개념 및 실습 (create directory 포함)

Dong538 2022. 2. 18. 15:50

요약:

- 디렉토리 관련 딕셔너리 뷰 - ALL_DIRECTORIES

- 유저의 시스템 권한 조회 뷰 - DBA_SYS_PRIVS

- 디렉토리(오브젝트) 권한 조회 뷰 - DBA_TAB_PRIVS

- CREATE ANY DIRECTORY 권한 가진 유저로 디렉토리 생성시 본인(유저)은 자동으로 디렉토리에 대한 READ, WRITE 권한 부여받는다. 또한 해당 디렉토리에 대한 READ, WRITE등 오브젝트 권한을 다른 유저에게 줄 수도 있다. 

- GRANT CREATE ANY DIRECTORY로 권한 부여했으면, REVOKE도 마찬가지로 REVOKE CREATE ANY DIRECTORY로 권한 회수해야 한다. 

 

 

디렉토리 리스트 조회 

SQL> set line 200
SQL> col table_name for a30
SQL> col comments for a70
SQL> select * from dict where table_name like '%DIR%';

-- dict에서 디렉토리 관련 딕셔너리 검색 후 조회
SQL> desc ALL_DIRECTORIES
SQL> col directory_name for a30
SQL> col directory_path for a70
SQL> select directory_name, directory_path from all_directories;

 

-- 디렉토리 생성 및 권한 설정
-- 생성 전 유저 및 인스턴스 조회 

-- 디렉토리 생성 및 권한 설정
-- 생성 전 유저 및 인스턴스 조회 
SQL> show user
USER is "SYS"
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl

-- 생성된 모든 디렉토리 확인 
SQL> select directory_name, directory_path from all_directories;

-- 실제 export 할 경로 생성 
SQL> !
[oracle@enp0s3 ~]$ mkdir -p /u01/app/oracle/oradata/datapump

-- 생성 후 경로(디렉토리) 권한 확인 
[oracle@enp0s3 ~]$ ls -lra /u01/app/oracle/oradata/datapump/
합계 4
drwxr-x---. 5 oracle oinstall 4096  2월 14 10:54 ..
drwxr-xr-x. 2 oracle oinstall    6  2월 14 10:54 .
[oracle@enp0s3 ~]$ exit

-- (오라클에서) 디렉토리(오브젝트) 생성.. create directory 권한을 유저에게 주었다면 해당 유저로 접속해서 생성해도 됨 
SQL> create or replace directory test_dp_dir as '/u01/app/oracle/oradata/datapump';

Directory created.

-- 유저에 권한주기 전 유저의 오브젝트(디렉토리) 권한 조회 
SQL> select * from dict where table_name like '%PRIVS%';

-- 유저의 오브젝트 권한 확인 
SQL> col grantee for a25
SQL> col owner for a25
SQL> col grantor for a25
SQL> col privilege for a20
SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs where grantee='HR';

GRANTEE    OWNER   TABLE_NAME    GRANTOR   PRIVILEGE
---------- ------- ------------- --------- ----------
HR         SYS     DBMS_STATS    SYS       EXECUTE
HR         SYS     TEST_DIR      SYS       READ
HR         SYS     TEST_DIR      SYS       WRITE

-- hr 유저에게 방금 생성한 test_dp_dir 디렉토리에 대한 read, write 권한 부여 
SQL> grant read, write on directory test_dp_dir to hr;

Grant succeeded.

-- 권한 부여 후 오브젝트 권한 다시 조회해서 부여 확인 
SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs where grantee='HR';

GRANTEE                   OWNER                     TABLE_NAME                     GRANTOR                   PRIVILEGE
------------------------- ------------------------- ------------------------------ ------------------------- -----------
HR                        SYS                       DBMS_STATS                     SYS                       EXECUTE
HR                        SYS                       TEST_DIR                       SYS                       READ
HR                        SYS                       TEST_DP_DIR                    SYS                       READ
HR                        SYS                       TEST_DIR                       SYS                       WRITE
HR                        SYS                       TEST_DP_DIR                    SYS                       WRITE

-- create directory 권한 부여.. 이후 hr은 디렉토리 생성 권한 생기고 자신이 생성한 디렉토리에 read, write권한 자동 부여된다
SQL> grant create any directory to hr;

Grant succeeded.

-- create directory 권한 부여 확인 
SQL> select grantee, privilege from dba_sys_privs where grantee='HR';

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

 

CREATE ANY DIRECTORY 권한 관련 실습

-- 1. create directory, drop directory 실습.. sys로는 둘 다 가능, HR유저는 create만 권한 부여되어 create만 가능 
SQL> show user
USER is "SYS"
SQL> select grantee, privilege from dba_sys_privs where grantee='HR';

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

8 rows selected.

SQL> select directory_name, directory_path from all_directories;
SQL> !
[oracle@enp0s3 ~]$ mkdir /u01/app/oracle/oradata/datapump2
[oracle@enp0s3 ~]$ exit
SQL> create or replace directory test_dp_dir2 as '/u01/app/oracle/oradata/datapump2';

Directory created.
SQL> select directory_name, directory_path from all_directories;

-- 2. create directory 권한 받은 유저로 생성해보기 
SQL> drop directory test_dp_dir2;

Directory dropped.

SQL> conn hr/hr
Connected.
SQL> create or replace directory test_dp_dir2 as '/u01/app/oracle/oradata/datapump2';

Directory created.

-- hr유저는 create 권한만 가지고 있어서 drop 명령 에러 발생 
SQL> drop directory test_dp_dir2;
drop directory test_dp_dir2
*
ERROR at line 1:
ORA-01031: insufficient privileges


-- 3. create any directory 권한 부여하면 디렉토리 생성 가능하고 디렉토리에 대한 read, write, execute 권한 자동 부여 -- 검증 -->
SQL> conn / as sysdba
Connected.

-- HR2 유저 생성 
SQL> grant create session to hr2 identified by "hr2";

Grant succeeded.

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

GRANTEE                   PRIVILEGE
------------------------- --------------------
HR2                       CREATE SESSION

SQL> conn hr2/hr2
Connected.
SQL> select grantee, owner, table_name, grantor, privilege, type from user_tab_privs;

GRANTEE    OWNER                     TABLE_NAME                     GRANTOR                   PRIVILEGE            TYPE
---------- ------------------------- ------------------------------ ------------------------- -------------------- ------------------------
PUBLIC     SYS                       HR2                            HR2                       INHERIT PRIVILEGES   USER

-- 이상 방금 생성한 hr2 유저의 시스템 권한, 오브젝트 권한 확인함 
-- sys로 접속해서 hr2에게 create any directory 권한 부여 
SQL> conn / as sysdba
Connected.
SQL> grant create any directory to hr2;

Grant succeeded.

SQL> col username for a15
SQL> select username, privilege from user_sys_privs;

USERNAME        PRIVILEGE
--------------- --------------------
HR2             CREATE ANY DIRECTORY
HR2             CREATE SESSION

-- 실제 경로 생성 및 hr2유저로 directory 생성하고 read, write 권한 확인 
SQL> conn hr2/hr2
Connected.
SQL> !
[oracle@enp0s3 ~]$ mkdir /u01/app/oracle/oradata/datapump3
[oracle@enp0s3 ~]$ exit
exit
SQL> create or replace directory test_dp_dir3 as '/u01/app/oracle/oradata/datapump3';

Directory created.

SQL> select grantee, owner, table_name, grantor, privilege from user_tab_privs;

GRANTEE                   OWNER                     TABLE_NAME                     GRANTOR                   PRIVILEGE
------------------------- ------------------------- ------------------------------ ------------------------- --------------------
HR2                       SYS                       TEST_DP_DIR3                   SYS                       EXECUTE
HR2                       SYS                       TEST_DP_DIR3                   SYS                       READ
HR2                       SYS                       TEST_DP_DIR3                   SYS                       WRITE
PUBLIC                    SYS                       HR2                            HR2                       INHERIT PRIVILEGES

-- 자동으로 hr2에 생긴 test_dp_dir3 디렉토리에 대한 권한을 hr 유저에게 부여도 가능 
-- 우선 hr 유저한테 test_dp_dir3 디렉토리에 대한 오브젝트 권한 없는 것 확인
SQL> conn hr/hr
Connected.
SQL> select grantee, owner, table_name, grantor, privilege from user_tab_privs;

GRANTEE                   OWNER                     TABLE_NAME                     GRANTOR                   PRIVILEGE
------------------------- ------------------------- ------------------------------ ------------------------- -------------------
HR                        SYS                       DBMS_STATS                     SYS                       EXECUTE
HR                        SYS                       TEST_DP_DIR2                   SYS                       EXECUTE
HR                        SYS                       TEST_DIR                       SYS                       READ
HR                        SYS                       TEST_DP_DIR2                   SYS                       READ
HR                        SYS                       TEST_DP_DIR                    SYS                       READ
HR                        SYS                       TEST_DIR                       SYS                       WRITE
HR                        SYS                       TEST_DP_DIR2                   SYS                       WRITE
HR                        SYS                       TEST_DP_DIR                    SYS                       WRITE
PUBLIC                    SYS                       HR                             HR                        INHERIT PRIVILEGES

SQL> conn hr2/hr2
Connected.
SQL> grant read, write on directory test_dp_dir3 to hr;

Grant succeeded.

SQL> conn hr/hr
Connected.
SQL> select grantee, owner, table_name, grantor, privilege from user_tab_privs;

GRANTEE                   OWNER                     TABLE_NAME                     GRANTOR                   PRIVILEGE
------------------------- ------------------------- ------------------------------ ------------------------- -------------------
HR                        SYS                       DBMS_STATS                     SYS                       EXECUTE
HR                        SYS                       TEST_DP_DIR2                   SYS                       EXECUTE
HR                        SYS                       TEST_DIR                       SYS                       READ
HR                        SYS                       TEST_DP_DIR2                   SYS                       READ
HR                        SYS                       TEST_DP_DIR                    SYS                       READ
HR                        SYS                       TEST_DP_DIR3                   HR2                       READ
HR                        SYS                       TEST_DIR                       SYS                       WRITE
HR                        SYS                       TEST_DP_DIR2                   SYS                       WRITE
HR                        SYS                       TEST_DP_DIR                    SYS                       WRITE
HR                        SYS                       TEST_DP_DIR3                   HR2                       WRITE
PUBLIC                    SYS                       HR                             HR                        INHERIT PRIVILEGES

 

-- create directory 권한 회수 
-- 중요한 것은, grant 줬을 때랑 동일하게 revoke해야한는 것이다.

-- create directory 권한 회수 
-- grant 줬을 때랑 동일하게 revoke해야한다 

SQL> conn / as sysdba
Connected.

--sys로 hr2 유저에게 권한 부여 
SQL> grant create any directory to hr2;
SQL> conn hr2/hr2
Connected.
SQL> select username, privilege from user_sys_privs;

USERNAME        PRIVILEGE
--------------- --------------------
HR2             CREATE ANY DIRECTORY
HR2             CREATE SESSION

-- sys계정으로 hr2 권한 회수
SQL> conn / as sysdba
Connected.
SQL> revoke create any directory from hr2;

Revoke succeeded.

SQL> conn hr2/hr2
Connected.

-- 권한 없어짐 확인 
SQL> select username, privilege from user_sys_privs;

USERNAME        PRIVILEGE
--------------- --------------------
HR2             CREATE SESSION

 

-- HR2 유저의 CREATE ANY DIRECTORY 권한 REVOKE 시 HR2로 생성한 TEST_DP_DIR3 디렉토리의 READ, WRITE 권한을 HR에게 GRANT 해줬던 것(오브젝트 권한)은 회수되지 않는다. 필요시 직접 REVOKE 해야 한다. 

-- REVOKE CREATE ANY DIRECTORY 이후, HR2 유저 자기 소유의 TEST_DP_DIR3 디렉토리에 대한 READ, WRITE 권한을 HR3(신규 유저)에게 주는 것은 가능하다. 

즉, REVOKE CREATE ANY DIRECTORY와 이미 생성된 디렉토리에 대한 오브젝트 권한과는 무관하다. REVOKE 전까지 다른 유저에게 부여했던 오브젝트 권한이 사라지지도 않고, 이후로도 (SYS로 뺏지 않는다면) 다른 유저에게 해당 디렉토리 권한을 부여할 수 있다. 

 

위 내용에 대한 실습은 다음과 같다:

SQL> conn / as sysdba
Connected.
SQL> revoke create any directory from hr2
  2  ;

Revoke succeeded.

SQL> conn hr2/hr2
Connected.
SQL> select username, privilege from user_sys_privs;

USERNAME        PRIVILEGE
--------------- --------------------
HR2             CREATE SESSION

SQL> conn hr/hr
Connected.

SQL> select grantee,owner,table_name,grantor,privilege,type from user_tab_privs;

GRANTEE    OWNER                     TABLE_NAME                     GRANTOR                   PRIVILEGE            TYPE
---------- ------------------------- ------------------------------ ------------------------- -------------------- ------------------------
HR         SYS                       DBMS_STATS                     SYS                       EXECUTE              PACKAGE
HR         SYS                       TEST_DP_DIR2                   SYS                       EXECUTE              DIRECTORY
HR         SYS                       TEST_DIR                       SYS                       READ                 DIRECTORY
HR         SYS                       TEST_DP_DIR2                   SYS                       READ                 DIRECTORY
HR         SYS                       TEST_DP_DIR                    SYS                       READ                 DIRECTORY
HR         SYS                       TEST_DP_DIR3                   HR2                       READ                 DIRECTORY
HR         SYS                       TEST_DIR                       SYS                       WRITE                DIRECTORY
HR         SYS                       TEST_DP_DIR2                   SYS                       WRITE                DIRECTORY
HR         SYS                       TEST_DP_DIR                    SYS                       WRITE                DIRECTORY
HR         SYS                       TEST_DP_DIR3                   HR2                       WRITE                DIRECTORY
PUBLIC     SYS                       HR                             HR                        INHERIT PRIVILEGES   USER

11 rows selected.

SQL> conn / as sysdba
Connected.
SQL> grant create session to hr3 identified by "hr3";

Grant succeeded.

SQL> conn hr2/hr2
Connected.
SQL> grant read, write on directory test_dp_dir3 to hr3;

Grant succeeded.

SQL> conn hr3/hr3
Connected.
SQL> select grantee,owner,table_name,grantor,privilege,type from user_tab_privs;

GRANTEE    OWNER                     TABLE_NAME                     GRANTOR                   PRIVILEGE            TYPE
---------- ------------------------- ------------------------------ ------------------------- -------------------- ------------------------
HR3        SYS                       TEST_DP_DIR3                   HR2                       READ                 DIRECTORY
HR3        SYS                       TEST_DP_DIR3                   HR2                       WRITE                DIRECTORY
PUBLIC     SYS                       HR3                            HR3                       INHERIT PRIVILEGES   USER