[SQL] 권한 관리

 

 

 

권한(Privileges) 관리

권한(Privilege)이란 데이터베이스에 접속하거나 데이터베이스의 객체에 접근 하거나 SQL 문을 실행 할 수 있도록 스키마(Schema)객체에 부여된 설정 들이다. DBA가 유저 객체에 직접 부여 할 수도 있고 ROLE(System Privilege나 Object Privilege를 저장하고 있는 객체)을 생성하여 롤에 권한을 부여한 뒤 그 롤을 유저에게 하당하는 간접적인 방법이 있다. 권한에는 오브젝트 권한(Object Privilege)과 시스템 권한(Sysetm Privilege)이 있다.

 

1. 시스템 권한(SYSTEM Privilege)

 시스템 권한은 DBMS에 대한 전반적인 관리 작업에 필요한 권한을 말한다. 이러한 작업에는 테이블, 뷰, 인덱스, 프로시저 등의 객체에 대한 생성, 삭제, 수정 작업들이 있다. 시스템 권한은 주로 DBA가 부여하며 system_privilege_map 뷰를 전체 시스템 권한을 통해 확인할 수 있다. 시스템 권한을 분류해 보면 CREATE SESSION, CREATE TABLESPACE 처럼 전 시스템에 영향을 주는 것과 CREATE TABLE 처럼 사용자 자신의 스키마 오브젝트에 대한 관리를 가능하게 해주는 권한이 있다. 또 ANY 권한이 포함된 모든 스키마 객체 관리를 권한이 있다.

  SQL>SELECT PRIVILEGE, NAME, PROPERTY FROM SYSTEM_PRIVILEGE_MAP;

 ∘ 주요 시스템 권한

 - CREATE SESSION :  DB에 연결할 수 있는 권한

 - RESTRICTED SESSION :  DB를 startup restricted로 시작할 수 있는 권한

 - CREATE TABLE : user소유 schema 안에서 table 또는 index를 생성할 수 있고

                      analyze 명령을 사용할 수 있는 권한.

 - SELECT ANY TABLE : 어떤 schema에서나 모든 table, view 또는snap shot에 대한 검색 권한

 - ALTER SYSTEM : alter system 명령을 사용하여 system 정의를 변경할 수 있는 권한

 - CREATE ROLE : 오라클 DATABASE ROLE을 생성할 수 있는 권한

 - INSERT ANY TABLE : 어떤 schema에서나 모든 table, view에 데이터를 입력할 수 있는 권한

 - CREATE USER : create user를 이용하여 user를 생성할 수 있는 권한

 - ALTER USER : alter user를 이용하여 생성한 user의 정의를 변경할 수 있는 권한

 - DROP USER : 생성한 user를 삭제시킬 수 있는 권한

 - DROP ANY TABLE : 테이블을 TRUNCATE 할 수 있는 권한

CREATE TABLE나 CREATE PROCEDURE, 또는 CREATE CLUSTER 같은 권한은 해당 오브젝트를  삭제하는 권한도 포함하며 CREATE TABLE은 CREATE INDEX와 ANALYZE 명령을 포함한다.  UNLIMITED TABLESPACE는 롤에 부여될 수 없다.

∘ 시스템 권한 부여

  GRANT문을 이용하여 System Privilege를 유저(User)나 롤(Role)에 주로 DBA가 부여한다. TO 뒤에 권한을 받을 유저나 롤을 지정한다. WITH ADMIN OPTION은 해당 시스템 권한을 다른 사용자나 롤에 재부여 할 수 있도록 허용한다. 시스템 권한은 DB 정의를 변경할 수 있기 때문에 권한 부여 시 주의가 필요하다.

  SQL>GRANT CREATE TABLE, CREATE VIEW TO SCOTT;    -- scott에게 권한을 부여함

  SQL>SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION       --권한부여 작업 성공여부 확인

       FROM DBA_SYS_PRIVS WHERE GRANTEE = 'HR';

  SQL>SELECT USERNAME, PRIVILEGE, ADMIN_OPTION     --유저의 권한 확인

       FROM USER_SYS_PRIVS;

∘ 시스템 권한 회수

REVOKE 명령을 사용하여 유저나 롤에 부여된 시스템 권한을 회수한다. REVOKE 명령은 GRANT 명령으로 직접 부여한 권한만을 철회 할 수 있다. 시스템 권한을 회수하면 일부 종속된 객체에 영향을 줄 수 있는데 유저 A가 유저 B로부터 SELECT ANY TABLE 권한을 부여 받은 상태에서, A가 B의 table을 select해서 view를 생성하였는데 B가 A의 권한을 회수하면 A는 더 이상 그 view를 사용할 수 없게 된다. WITH ADMIN OPTION의 사용 여부와 상관없이 시스템 권한이 회수 될 때는 제3자의 권한도 함께 회수되지 않는다. 예를 들어 A가 권한을 회수할 때 B의 권한만 회수하지 C의 권한은 회수하지는 않는다. C는 여전히 그 권한을 갖고 있게 된다. 시스템 권한을 확인 하는 방법으로 데이터베이스 레벨에서 롤과 사용자에게 부여된 모든 시스템 권한을 보여주는 DBA_SYS_PRIVS 가 있다. 또 SESSION_PRIVS 는 현재 세션에서 사용자에게 가능한 권한을 보여 준다.

  SQL>REVOKE CREATE TABLE, CREATE VIEW FROM 'HR';     --권한회수

  SQL>SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION         --권한회수 작업 성공여부 확인

       FROM DBA_SYS_PRIVS WHERE GRANTEE = 'HR';

2. 객체 권한 (Object Privilege)

 객체권한 이란 특정한 Table, View, Sequence, Procedure, Function, Package등의 DB Object에 대해서 SELECT, INSERT, EXECUTE 같은 특정 작업을 수행하는 권한이다. 객체에 대해 권한 부여 및 회수 방법은 SYSTEM 권한과 유사하며 다만 대상 객체를 명시한다는 점이 다르다. 객체의 소유자는 객체에 대한 모든 권한을 가진다. 객체 권한을 부여하기 위해서는 객체의 소유자이거나  WITH ADMIN OPTION으로 권한을 부여 받아야 한다.

 ∘ 주요 오브젝트 권한

   ALTER : 테이블, 시퀀스에 적용되며 객체를  변경할 수 있도록 함

   DELETE : 테이블, 뷰에 적용되며 Data를 Delete할 수 있는 권한

   EXECUTE : procedure, function, package에 대해 실행할 수 있도록 허용

   INDEX : 권한을 받은 사용자가 table에 대해 인덱스를 생성할 수 있도록 허용

   INSERT :  테이블과 뷰에 대해서 데이터를 Insert 할수 있도록 허용

   REFERENCES : 테이블에 대한 FKey를 생성할 수 있는 권한

   SELECT : 테이블, 뷰, 시퀀스에 대한 조회 권한

   AUDIT : 권한을 받은 사용자가 감사를 할 수 있도록 허용

   COMMENT : 권한을 받은 사용자가 주석을 달 수 있도록 허용

   LOCK : 권한을 받은 사용자가 locking할 수 있도록 허용

  ∘ 객체 권한 부여

권한을 부여하려면 객체가 자신의 Schema에 있거나 WITH GRANT OPTION 권한을 갖고 있어야 한다. SYSTEM 권한과 마찬가지로 GRANT문을 사용하지만 ON구 다음에 대상객체를 명시한다는 점이 다르다. GRANT절 뒤에는 권한을 일일이 나열하는 대신에 ALL이라는 keyword를 사용하여 ON 뒤에 나오는 객체에 대해서 모든 객체권한을 한꺼번에 부여할 수 있다.  INSERT, UPDATE, REFERENCES권한은 Table 전체가 아닌 특정 칼럼에 대해 지정이 가능하다. 다른 사용자에 대해 권한을 부여하려면, schema.객체명 형식을 사용한다. WITH GRANT OPTION은 롤에 권한을 부여할 때는 사용할 수 없다.

  SQL>GRANT CONNECT, RESOURCE TO HR WITH GRANT OPTION;

  SQL>GRANT UPDATE(ename, sal) ON emp TO HR WITH GRANT OPTION;

 

∘ 객체 권한 회수

REVOKE 명령을 사용하여 객체권한을 회수한다. 자신이 권한을 부여했던 사용자에게만 권한을 회수할 수 있다. REFERENCES 권한을 회수할 때 Foreign Key 무결성 제약조건에 위배되는 경우가 생길 수 있는데 CASCADE CONSTRAINTS 옵션으로 제약조건까지 삭제 한다. WITH GRANT OPTION으로 부여된 오브젝트 권한을 회수 하면 해당 유저가 부여한 객체권한들은 연쇄적(cascade)으로 회수 된다.

간단한 예로 USER1이 EMP 테이블에 대해 GRANT OPTION과 함께 SELECT 오브젝트 권한을 부여 받은 후 USER2 SELECT 권한을 부여 한 경우 USER1에게서 SELECT 권한이 제거 되면 USER2도 SELECT 권한이 제거된다.

 

   SQL>REVOKE execute ON dbms_pipe FROM HR;

  

∘ 객체 권한 조회

   데이터베이스 내의 모든 개체 권한을 보여주는 딕셔너리 뷰인 DBA_TAB_PRIVS와 컬럼에 지정된 모든 객체 권한은 DBA_COL_PRIVS을 이용하여 객체 권한 내역을 확인 할 수 있다.

  --사용자의 객체에 대한 권한 부여 내역을 조회

  SQL>SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE, GRANTABLE, HIERARCHY

       FROM USER_TAB_PRIVS;

  --사용자의 객체에 대한 권한 부여 내역을 조회

  SQL>SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE, GRANTABLE

       FROM USER_COL_PRIVS;

  --부여받은 객체권한 확인

  SQL>SELECT OWNER, TABLE_NAME, GRANTOR, PRIVILEGE, GRANTABLE, HIERARCHY

       FROM USER_TAB_PRIVS_RECD;

  --사용자가 부여한 객체권한 내역을 조회

  SQL>SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE, GRANTABLE, HIERARCHY

       FROM ALL_TAB_PRIVS_MADE;

 

 

  

DCL(Data Control Language, 데이터 제어어)

: 데이터베이스에 있는 데이터에 접근을 제어하는 언어

 

- grant : 접근제어나 어떤 작업을 허용하는 권한을 주는 역할

- revoke : 허용된 권한을 제거

 

SQL> conn /as sysdba
연결되었습니다.

SQL> create user test identified by test;

사용자가 생성되었습니다.

 

SQL> grant create session to test;

권한이 부여되었습니다.
SQL> revoke create session from test;

권한이 취소되었습니다.

 

SQL> grant resource to test;

권한이 부여되었습니다.

SQL> grant connect to test;

권한이 부여되었습니다.

 

SQL> conn test/test
연결되었습니다.
SQL> show user
USER은 "TEST"입니다

 

SQL> select grantee,privilege
  2  from dba_sys_privs
  3  where grantee='CONNECT';

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
CONNECT                        CREATE SESSION

SQL> select grantee,privilege
  2  from dba_sys_privs
  3  where grantee='RESOURCE';

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
RESOURCE                       CREATE TRIGGER
RESOURCE                       CREATE SEQUENCE
RESOURCE                       CREATE TYPE
RESOURCE                       CREATE PROCEDURE
RESOURCE                       CREATE CLUSTER
RESOURCE                       CREATE OPERATOR
RESOURCE                       CREATE INDEXTYPE
RESOURCE                       CREATE TABLE

8 개의 행이 선택되었습니다.