Oracle 권한

시스템 권한

Oracle에서 사용자에게 부여될 수 있는 시스템 권한의 종류는 90여 가지나 된다. 이 중에서 자주 사용되는 권한을 정리하면 표 12-1과 같다.

시스템 권한
내 용
CREATE SESSION
데이터베이스에 접근할 수 있는 권한
CREATE TABLE

DROP ANY TABLE
테이블을 생성/제거할 수 있는 권한
CREATE USER

DROP USER
사용자를 생성/제거할 수 있는 권한
CREATE VIEW

DROP ANY VIEW
뷰를 생성/제거할 수 있는 권한
CREATE SYNONYM

DROP ANY SYNONYM
시노님을 생성/제거할 수 있는 권한
CREATE SEQUENCE

DROP ANY SEQUENCE
시퀸스를 생성/제거할 수 있는 권한
CREATE ROLE

DROP ANY ROLE
롤을 생성/제거할 수 있는 권한
GRANT ANY PRIVILEGE
권한을 부여할 수 있는 권한
LOCK ANY TABLE
테이블과 뷰에 lock을 할 수 있는 권한
UNLIMITED TABLESPACE
데이터베이스 사용공간을 제한하지 않는 권한
표 12-1 주요 시스템 권한
사용자에게 시스템 권한을 부여하고 회수하는 방법은 다음과 같다.

구문법
GRANT { system_privilege [, system_privilege, . . . ] | ALL }

TO { user_name [, user_name, . . . ] | PUBLIC }

[WITH ADMIN OPTION];

REVOKE { system_privilege [, system_privilege, . . . ] | ALL }

FROM { user_name [, user_name, . . . ] | PUBLIC };

system_privilege
사용자가 부여/회수되는 권한의 종류
user_name
권한을 부여/회수되는 사용자 이름
ALL
모든 권한
PUBLIC
모든 사용자
위의 문법에서 [WITH ADMIN OPTION]은 선택사항으로서 권한을 부여받는 사용자 즉, user_name 사용자가 부여받은 권한을 다시 다른 사용자에 권한을 이양할 수 있도록 하는 추가적인 권한이다.

여기에서 ALL은 모든 권한을 의미하고, PUBLIC는 모든 사용자를 의미한다.

REVOKE는 부여된 권한을 회수하는 명령으로서 만약, 사용자에게 [WITH ADMIN OPTION]으로 부여된 권한을 회수할 경우는 회수되는 사용자가 이양했던 권한까지 모두 회수되는 결과를 갖는다.

?실습예제: 앞에 예제에서 생성한 USER_A 사용자에게 데이터베이스에 접근할 수 있는 권한을 부여하고 USER_A 사용자로 로그온 하여 보시오.

SQL
GRANT CREATE SESSION TO USER_A;

CONNECT USER_A/usera

SQL> GRANT CONNECT TO USER_A;

권한 부여 성공입니다.

SQL> CONNECT USER_A/usera

Connected.

?실습예제: USER_A 사용자로 로그온을 한 후에 테이블을 하나 생성하여보고 테이블이 생성되지 않으면 생성할 수 있는 권한을 부여하여 보시오.

SQL
CREATE TABLE test( t1 CHAR(7));

CONNECT po7/po7

GRANT CREATE TABLE, UNLIMITED TABLESPACE

TO USER_A;

CONNECT USER_A/usera

CREATE TABLE test( t1 CHAR(7));

SQL> CREATE TABLE test( t1 CHAR(7));

CREATE TABLE test( t1 CHAR(7))

*

줄 1에 오류:

ORA-01031: insufficient privileges

SQL> CONNECT po7/po7

Connected.

SQL> GRANT CREATE TABLE, UNLIMITED TABLESPACE

2 TO USER_A;

권한 부여 성공입니다.

SQL> CONNECT USER_A/usera

Connected.

SQL> CREATE TABLE test( t1 CHAR(7));

테이블이 생성되었습니다.

위의 실습예제 결과를 분석하여 보면 CREATE SESSION 권한만 있는 USER_A 사용자는 데이터베이스에 대한 접근 권한만 있으므로 테이블 생성을 할 수 없었고, DBA인 PO7이 테이블 생성 및 데이터베이스 공간을 제한 없이 사용하도록 권한을 부여함으로서 test 테이블을 생성할 수 있었다.

12.2.2 개체 권한

사용자가 다른 사용자에게 자신이 소유한 개체에 대해서 사용권한을 부여할 수 있는 종류는 SELECT, UPDATE, INSERT, DELETE 등이 있다.

사용자가 데이터베이스 개체에 대해 다른 사용자에게 권한을 부여하고 회수하는 방법은 다음과 같다.

구문법
GRANT { object_privilege [, object_privilege, . . . ] | ALL }

ON object_name

TO { user_name [, user_name, . . . ] | PUBLIC }

[WITH ADMIN OPTION];

REVOKE { object_privilege [, object_privilege, . . . ] | ALL }

ON object_name

FROM { user_name [, user_name, . . . ] | PUBLIC };

object_privilege
사용자가 부여/회수하는 권한의 종류
object_name
대상이 되는 개체 이름
user_name
권한을 부여/회수하는 사용자 이름
ALL
모든 권한
PUBLIC
모든 사용자
개체에 대한 권한 부여 및 회수 방법은 시스템 권한과 유사하며, 대상 개체를 명시하는 점만 틀리다

?실습예제: USER_A 사용자가 보유하고 있는 test 테이블을 SCOTT 사용자에게 select, insert 할 수 있는 권한을 부여하고, 권한이 제대로 부여되었는지를 확인하여 보시오.

SQL
GRANT SELECT, INSERT ON test TO SCOTT;

CONNECT OSQL/OSQL

SELECT * FROM USER_A.test;

CONNECT SCOTT/tiger

SELECT * FROM USER_A.test;

INSERT INTO USER_A.test values('test');

SELECT * FROM USER_A.test;

SQL> GRANT SELECT, INSERT ON test TO SCOTT;

권한 부여 성공입니다.

SQL> CONNECT OSQL/OSQL

Connected.

SQL> SELECT * FROM USER_A.test;

SELECT * FROM USER_A.test

*

줄 1에 오류:

ORA-00942: table or view does not exist

SQL> CONNECT SCOTT/tiger

Connected.

SQL> SELECT * FROM USER_A.test;

선택된 레코드가 없습니다.

SQL> INSERT INTO USER_A.test values('test');

1 개의 행이 작성되었습니다.

SQL> SELECT * FROM USER_A.test;

T1

-------

test

◐실습문제: USER_A 사용자의 test 테이블에 대해서 다른 모든 사용자에게 부여된 권한을 모두 회수하는 SQL 구문을 작성하시오.

◐실습문제: SCOTT 사용자가 소유하고 있는 dept 테이블에 대한 SELECT 권한을 USER_A 사용자에게 부여하는 SQL 구문을 작성하시오.

◐실습문제: SCOTT 사용자가 USER_A에게 dept 테이블에 대해 부여한 모든 권한을 회수하는 SQL 구문을 작성하시오.

12.3 사용자 롤 관리

사용자에게 부여될 수 있는 권한의 종류가 90여 개나 되므로 각 권한을 일일이 부여하기가 복잡해진다. 이를 해소할 수 있는 방법이 롤이라는 개념이다.

롤(role)은 사용자에게 부여할 수 있는 여러 개의 관련 권한들에 대한 그룹으로 몇 개의 롤을 만들어 놓고 사용자의 특성에 따라 개별 권한 대신에 롤을 부여함으로서 권한 부여가 단순해진다.

롤을 사용하려면 우선 롤을 만들고, 사용자에게 권한을 부여하는 것처럼 롤에 여러 권한을 부여하여 롤을 사용자에게 권한을 부여하듯이 부여하면 된다.

롤을 생성하고 회수하는 방법과 생성된 롤에게 권한을 부여하고 회수하는 방법은 다음과 같다.

구문법
CREATE ROLE role_name;

DROP ROLE role_name;

GRANT { system_privilege [, system_privilege, . . . ] | ALL }

TO role_name [WITH ADMIN OPTION];

REVOKE { system_privilege [, system_privilege, . . . ] | ALL }

FROM role_name;

GRANT { object_privilege [, object_privilege, . . . ] | ALL }

ON object_name TO role_name

[WITH ADMIN OPTION];

REVOKE { object_privilege [, object_privilege, . . . ] | ALL }

FROM role_name;

롤명
권한 범위
CONNECT
데이터베이스에 연결할 수 있는 권한들
RESOURCE
개체들을 생성, 변경하고 제거할 수 있는 권한
DBA
데이터베이스 관리자 권한
표 12-2 시스템이 제공하는 롤
데이터베이스 시스템은 기본적으로 다음 세 가지의 롤을 제공하고 있다.

시스템이 제공하는 세 가지 롤 중에서 CONNECT 롤은 일반사용자(end user)에게 부여하는 최소한의 권한만을 갖는 롤이고, 테이블과 같은 개체를 만들고 데이터를 구축해야하는 응용프로그래머 사용자에게는 CONNECT와 RESOURCE 롤을 부여하면 된다. DBA 롤은 데이터베이스 관리자에게 부여되는 최상위의 권한을 갖는 롤이다.

CONNECT와 RESOURCE 롤에 대한 권한을 갖는 사용자가 롤을 생성하려면 롤을 생성할 수 있는 권한을 DBA로부터 추가로 부여받아야 한다.

?실습예제: SCOTT 사용자가 보유하고 있는 dept 테이블에 대해서 select, update, insert, delete 할 수 있는 롤을 role_dept 이름으로 생성하고, 이 롤을 OSQL 사용자에게 부여하는 SQL구문을 작성하시오.

SQL
CONNECT po7/po7

GRANT CREATE ROLE TO SCOTT;

CONNECT SCOTT/tiger

CREATE ROLE role_dept;

GRANT SELECT, INSERT, UPDATE, DELETE ON dept

TO role_dept;

GRANT role_dept TO OSQL;

CONNECT OSQL/OSQL

SELECT * FROM SCOTT.dept;

INSERT INTO SCOTT.dept values(90, 'test', 'role');

SELECT * FROM SCOTT.dept;

SQL> CONNECT po7/po7

Connected.

SQL> GRANT CREATE ROLE TO SCOTT;

권한 부여 성공입니다.

SQL> CONNECT SCOTT/tiger

Connected.

SQL> CREATE ROLE role_dept;

롤이 생성되었습니다.

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON dept TO role_dept;

권한 부여 성공입니다.

SQL> GRANT role_dept TO OSQL;

권한 부여 성공입니다.

SQL> CONNECT OSQL/OSQL

Connected.

SQL> SELECT * FROM SCOTT.dept;

DEPTNO DNAME LOC

--------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> INSERT INTO SCOTT.dept values(90, 'test', 'role');

1 개의 행이 작성되었습니다.

SQL> SELECT * FROM SCOTT.dept;

DEPTNO DNAME LOC

--------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

90 test role

◐실습문제: SCOTT가 OSQL 사용자에게 부여한 role_dept 롤 권한을 회수하는 SQL 구문을 작성하시오.

☞권한 및 롤 정보 보기

사용자, 개체 또는 롤에 부여된 권한 및 롤에 대한 정보를 보는 SQL 구문은 다음과 같은 것들이 있다.

SQL>SELECT * FROM USER_SYS_PRIVS;

SQL>SELECT * FROM USER_TAB_PRIVS;

SQL>SELECT * FROM USER_COL_PRIVS;

SQL>SELECT * FROM USER_ROLE_PRIVS;

SQL>SELECT * FROM ROLE_SYS_PRIVS;

SQL>SELECT * FROM ROLE_TAB_PRIVS;

SQL>SELECT * FROM ROLE_ROLE_PRIVS;

** Data Control Language **

권한을 DICTIONARY에서 검색

SELECT * FROM USER_ROLE_PRIVS;
SELECT * FROM ROLE_SYS_PRIVS;

개별적 권한검색
SELECT * FROM SESSION_PRIVS;

권한주기
GRANT SELECT ON EMP TO STD07,STD08;

GRANT SELECT ON EMP TO STD07,STD08
 WITH GRANT OPTION;
받은 사람이 다시 부여할 수 있다.
권한 준사람이 회수시 --> 시스템권한:당사자만권한회수
                         객체권한:모두회수

권한 회수하기
REVOKE SELECT ON EMP FROM STD07,STD08;

REFERENCE가 된 경우는 바로 회수 안됨 --> 무시하고 회수
 REVOKE SELECT ON EMP FROM STD07,STD08
 CASCADE CONSTRAINTS;

내가 준 권한 보기
 SELECT * FROM USER_TAB_PRIVS_MADE;
받은 권한 확인
 SELECT * FROM USER_TAB_PRIVS_RECD;