Privilege(권한)
--------------------------------------------------------------------------------
Session/System Control Language(SCL)에 관한 자세한 내용은 이곳을 클릭한다.
--------------------------------------------------------------------------------
권한이란 SQL 문을 실행하거나, 데이터베이스나 데이터베이스의 객체에 접근할 수 있는 권한을 의미한다.
이처럼 권한은
1) DBA가 사용자에게 직접 부여 할 수도 있고
2) ROLE을 생성하여 롤에 권한을 부여한 뒤 그 롤을 사용자에게 부여하는 것처럼 간접적으로 부여 하는 방법도 있다.
권한 또는 롤을 부여 받은 사용자는 데이터베이스에서 데이터 조작이 가능하게 된다.
즉, 사용자는 권한을 받아야만 DB에 access(접근), create(생성), select(검색), insert(삽입)등의 작업을 할 수 있다.
권한은 system privilege와 object privilege로 구분되며, 사용자는 DB에 접근하기 위해 시스템 권한이 필요하고, 데이터베이스의 객체를 조작하기 위해 객체 권한이 필요하게 된다.
즉, 시스템 권한은 데이터베이스 안의 모든 테이블을 검색할 수 있는 권한이며,
객체 권한은 사용자에게 하나의 특정 테이블만을 검색할 수 있게 하는 권한이다.
권한의 종류 권한의 종류 설명
시스템 권한(SYSTEM privilege) 데이터베이스 객체를 생성, 수정, 삭제 권한
객체 권한(OBJECT privilege) object 내용을 조작(추가, 변경, 삭제, 검색)할 수 있는 권한
시스템(system) 권한
• 시스템 권한은 특정 DB 연산 또는 작업을 수행하기 위하여 필요한 권한이다.
• 이러한 작업에는 테이블, 뷰, undo segment, 프로시져의 생성, 삭제, 수정등이 있다.
• 시스템 권한은 주로 DBA가 부여한다.
• system_privilege_map 뷰를 통해 전체 시스템 권한을 확인할 수 있다.
SQL> desc system_privilege_map;
Name Null? Type
----------------------------------------- -------- ----------------------------
PRIVILEGE NOT NULL NUMBER
NAME NOT NULL VARCHAR2(40)
PROPERTY NOT NULL NUMBER
SQL> select count(*) from system_privilege_map;
COUNT(*)
----------
206
SQL>
• 다음은 시스템 권한 중에서 몇개에 대해서 설명한다.
시스템 권한 설명 CREATE SESSION DB에 연결할수 있는 권한 RESTRICTED SESSION DB를 startup restricted로 시작할 수 있는 권한 CREATE TABLE user소유 schema 안에서 table 또는 index를 생성할 수 있는 권한 SELECT ANY TABLE 어떤 schema에서나 모든 table, view 또는snap shot에 대한 검색을 실행할 수 있는 권한 ALTER SYSTEM alter system 명령을 사용하여 system 정의를 변경할 수 있는 권한 CREATE ROLE 오라클 DATABASE ROLE을 생성할 수 있는 권한 INSERT ANY TABLE 어떤 schema에서나 모든 table, view에 데이터를 입력할 수 있는 권한 DROP USER 생성한 user를 삭제시킬 수 있는 권한 CREATE USER create user를 이용하여 user를 생성할 수 있는 권한 ALTER USER alter user를 이용하여 생성한 user의 정의를 변경할 수 있는 권한
• create table 권한에 create index와 analyze 명령을 사용할 수 있는 권한이 포함되어 있기 때문에 index 생성과 관련한 시스템 권한이 없다.
--------------------------------------------------------------------------------
시스템 권한 부여
다음 문자 형식을 이용하여 system privilege와 role을 부여한다.
【형식】
GRANT 시스템권한명 또는 롤명 TO 사용자명 또는 롤명 또는 PUBLIC
[WITH ADMIN OPTION];
• system 권한은 주로 DBA가 다른 user가 부여한다.
• GRANT 문으로 권한을 지정한다.
• TO 뒤에 권한을 받을 user를 지정한다.
• WITH ADMIN OPTION은 해당 시스템 권한을 다른 사용자나 롤에 재부여를 허용
• PUBLIC은 모든 사용자에게 권한을 주기 위한 것으로 권한을 주는 쪽은 신중해야 한다. PUBLIC으로 선언된 권한은 이 후에 새로 생성된 사용자에게도 자동으로 해당 권한이 부여되기 때문이다.
• 시스템 권한은 DB 정의를 변경할 수 있기 때문에 권한을 받은 쪽의 통제가 요구된다.
【예제】
SQL> connect / as sysdba
SQL> create user kim ☜ 새로운 user인 kim을 만듬
2 identified by kun114$;
User created.
SQL> GRANT CREATE table, CREATE view TO jijoe; ☜ jijoe에게 권한을 부여함
Grant succeeded.
SQL> connect jijoe/password
Connected.
SQL> select * from USER_sys_privs; ☜ ADMIN 권한을 받았는지 확인하는 뷰
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
JIJOE CREATE VIEW NO
JIJOE CREATE TABLE NO
JIJOE CREATE SESSION NO
JIJOE UNLIMITED TABLESPACE NO
☜ ADM 필드가 NO이면 ADMIN 권항이 부여 되지 않았음을 나타냄
SQL> GRANT CREATE table, CREATE view TO kim; ☜ ADMIN 권한이 없는 상태에서 kim에게 권한을 주려고 함
GRANT CREATE table, CREATE view TO kim
*
ERROR at line 1:
ORA-01031: insufficient privileges ☜ 권한이 없다고 알려 줌
SQL> conn system/password as sysdba
Connected.
SQL> grant create table, create view TO jijoe
2 WITH ADMIN OPTION; ☜ ADMIN 옵션으로 table,view를 만들 수 있는 권한을 jijoe에게 줌
Grant succeeded.
SQL> conn jijoe/password
Connected.
SQL> select * from user_sys_privs; ☜ ADMIN 권한을 받았는지 확인하는 뷰
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
JIJOE CREATE VIEW YES
JIJOE CREATE TABLE YES
JIJOE CREATE SESSION NO
JIJOE UNLIMITED TABLESPACE NO
☜ ADM 필드가 YES이면 ADMIN 권한이 부여 되었음을 나타냄
SQL> GRANT CREATE table, CREATE view TO kim;
☜ ADMIN 권한을 부여 받은 jijoe가 사용자 kim에게 권한을 부여할 수 있음
Grant succeeded.
SQL>
다음 예제는 DBA가 모든 user에게 새로운 user 생성 권한을 예이다.(절대 권장하지 않음)
【예제】
SQL> connect system/password as sysdba
Connected.
SQL> CREATE USER kim2 identified by kim2##;
User created.
SQL> GRANT create session TO kim2;
Grant succeeded.
SQL> GRANT create USER TO PUBLIC;
Grant succeeded.
SQL> connect kim2/kim2##;
Connected.
SQL> SELECT * FROM USER_SYS_PRIVS;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
KIM2 CREATE SESSION NO
PUBLIC CREATE USER NO
SQL> create user kim3 IDENTIFIED BY kim3###;
User created.
SQL> conn kim3/kim3###;
ERROR:
ORA-01045: user KIM3 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn system/password as sysdba
SQL> GRANT connect TO kim3;
Grant succeeded.
SQL> conn kim3/kim3###;
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
PUBLIC CREATE USER NO
SQL>
PUBLIC을 사용하여 CREATE USER라는 시스템 권한을 주었을 때 모든 user들이 그 권한을 부여 받게 된다. 따라서 어떠한 user로 접속해도 새로운 user를 생성할 수 있게 된다.
그러나, kim2가 CREATE USER 권한은 부여 받지 않았음을 알 수 있다.
--------------------------------------------------------------------------------
DBA_sys_PRIVS 롤이나 사용자에게 부여된 모든 시스템 권한을 조회
session_PRIVS Session Level의 시스템 권한을 확인
SYSTEM_PRIVILEGE_MAP 전체 시스템 권한을 확인
--------------------------------------------------------------------------------
시스템 권한 회수
REVOKE 명령을 사용하여 부여된 시스템 권한을 회수하는 문장의 형식은 다음과 같다.
【형식】
REVOKE 시스템권한명, 롤명 FROM 사용자명, 롤명, PUBLIC
【예제】
SQL> connect kim3/kim3###
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
PUBLIC CREATE USER NO
SQL> connect kim2/kim2##
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
KIM2 CREATE SESSION NO
PUBLIC CREATE USER NO
SQL> conn system/password as sysdba
Connected.
SQL> REVOKE CREATE user FROM PUBLIC;
Revoke succeeded.
SQL> conn kim2/kim2##
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
KIM2 CREATE SESSION NO
SQL> conn kim3/kim3###
Connected.
SQL> select * from user_sys_privs;
no rows selected
SQL>
--------------------------------------------------------------------------------
SQL> conn jijoe/password
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
JIJOE CREATE VIEW YES
JIJOE CREATE TABLE YES
JIJOE CREATE SESSION NO
JIJOE UNLIMITED TABLESPACE NO
SQL> conn system/password as sysdba
Connected.
SQL> REVOKE CREATE TABLE,CREATE VIEW FROM jijoe;
Revoke succeeded.
SQL> conn jijoe/password
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
JIJOE CREATE SESSION NO
JIJOE UNLIMITED TABLESPACE NO
SQL>
• 만약 여러개의 권한을 부여했다면, REVOKE로 선택적으로 회수할 수 있다.
• Role에 여러 권한을 부여 했다면, REVOKE ROLE을 수행할 때, 특정 권한만 따로 회수 할 수 없다.
• 시스템 권한을 회수하면 일부 종속된 객체에 영향을 줄 수 있다. 예를 들어, A가 B로부터 table select 권한을 부여 받은 상태에서, A가 B의 table을 select해서 view를 생성하였는데, B가 A의 권한을 회수하면, A는 더이상 그 view를 사용할 수 없게 된다.
• WITH ADMIN OPTION의 사용 여부와 상관없이 시스템 권한이 회수 될 때는 제3자의 권한도 함께 회수되지 않는다. 예를 들어, A가 권한을 회수할 때 B의 권한만 회수하지 C의 권한은 회수하지는 않는다. C는 여전히 그 권한을 갖고 있게 된다.
객체(object) 권한
• Object privilege란 특정한 table view, sequence, procedure, function, package등에 대한 특정한 작업을 수행하는 권한이다. 예를 들어, 사용자 kim의 PERSONNEL table을 SELECT 할 수 있도록 하거나 SYSTEM user의 TEST procedure를 실행할 수 있는 권한처럼 특정 object에 대한 권한을 뜻한다.
• 객체에 대해 권한 부여 및 회수 방법은 SYSTEM 권한과 유사하며, 다만 대상 객체를 명시한다는 점이 다르다.
• 객체에 대한 소유자는 객체에 대한 모든 권한을 가진다.
• 객체 권한을 부여하기 위해서는 객체의 소유자이거나, WITH ADMIN OPTION으로 권한을 부여 받아야 한다.
• 객체 소유자는 객체에 대한 특정한 권한을 제공할 수 있다.
객체 권한의 종류와 범위 TABLE 권한 VIEW 권한 SEQUENCE 권한 PROCEDURE 권한 SNAPSHOT 권한
INSERT 0 0
UPDATE 0 0
DELETE 0 0
ALTER 0 0 0 0
INDEX 0 0
SELECT 0 0 0 0
EXECUTE 0
REFERENCES 0
다음은 각각의 권한이 행할 수 있는 기능이다.
alter 권한을 받은 사용자가 변경할 수 있도록 허용
audit 권한을 받은 사용자가 감사를 할 수 있도록 허용
comment 권한을 받은 사용자가 주석을 달 수 있도록 허용
delete 권한을 받은 사용자가 삭제할 수 있도록 허용
grant 권한을 받은 사용자가 부여할 수 있도록 허용
index 권한을 받은 사용자가 table에 대해 인덱스를 생성할 수 있도록 허용
insert 권한을 받은 사용자가 삽입할 수 있도록 허용
lock 권한을 받은 사용자가 locking할 수 있도록 허용
rename 권한을 받은 사용자가 이름을 변경할 수 있도록 허용
select 권한을 받은 사용자가 조회할 수 있도록 허용
update 권한을 받은 사용자가 갱신할 수 있도록 허용
references 권한을 받은 사용자가 자료를 참조할 수 있도록 허용
execute 권한을 받은 사용자가 procedure,function,package에 대해 실행할 수 있도록 허용
--------------------------------------------------------------------------------
객체 권한 부여(GRANT)
【형식】
GRANT 객체권한, ALL[컬럼명,...]
ON 대상객체
TO 사용자명, 롤명, PUBLIC
WITH GRANT OPTION;
• 권한을 부여하려면, 객체가 자신의 schema에 있거나 WITH GRANT OPTION권한을 갖고 있어야 한다.
• 객체의 소유자는 DB내의 어떤 사용자나 role에게도 해당 객체에 대한 어떤 것이라도 부여할 수 있다.
• SYSTEM 권한과 마찬가지로 객체 권한의 GRANT도 ON절을 사용하여 대상객체를 명시한다는 점만 다르다.
• GRANT절 뒤에는 권한을 일일이 나열하는 대신에 ALL이라는 keyword를 사용하여 ON 뒤에 나오는 객체에 대해서 모든 객체권한을 다 부여할 수 있다.
• GRANT시 객체권한을 table 전체가 아닌 특정 컬럼에 대해 지정이 가능하다.
예를 들어, 사용자 KIM의 AA table에 대해 INSERT 권한을 줄때 PNAME과 PNO에 대해서만 INSERT 권한을 부여할 수 있다. 그러나,이는 INSERT,UPDATE,REFERENCES권한에 대해서만 가능하다.
• 다른 사용자에 대해 권한을 부여하려면, schema.객체명 형식을 사용한다.
【예제】
SQL> connect system/password as sysdba
SQL> create user kim identified by gun;
SQL> grant connect,resource to kim;
SQL> create user kim2 identified by gun2;
SQL> grant connect,resource to kim2;
SQL> conn kim/gun
SQL> create table aa(pno number(3),pname varchar2(10));
SQL> insert into aa values(111,'COREA');
SQL> insert into aa values(222,'CHINA');
SQL> select * from aa;
PNO PNAME
---------- ----------
111 COREA
222 CHINA
SQL> select * from user_tab_privs;
no rows selected
SQL> select * from user_tab_privs_recd;
no rows selected
SQL> grant select ON aa TO kim2; ☜ aa 객체에 대한 권한을 kim2에게 부여함
Grant succeeded.
SQL> conn kim2/gun2;
SQL> select * from user_tab_privs; ☜ 사용자에게 주어진 객체권한 조회
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
------- ------- ----------- -------- ----------- --- ---
KIM2 KIM AA KIM SELECT NO NO
SQL> select * from user_tab_privs_recd; ☜ 부여받은 객체 권한 정보를 조회
OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- ------------ --------- ----------- --- ---
KIM AA KIM SELECT NO NO
SQL> select * from kim.aa; ☜ kim.aa 객체를 kim2가 조회할 수 있음
PNO PNAME
---------- ----------
111 COREA
222 CHINA
--------------------------------------------------------------------------------
SQL> conn kim/gun
Connected.
SQL>
SQL> grant insert(pno,pname),select ON aa
2 TO kim2; ☜ kim.aa객체에 kim2사용자에게 insert와 select 권한을 부여함
Grant succeeded.
SQL> conn kim2/gun2
Connected.
SQL> select * from kim.aa;
PNO PNAME
---------- ----------
111 COREA
222 CHINA
SQL> insert into kim.aa values(333,'JAPAN'); ☜ kim2가 kim.aa 객체에 insert를 실행함
1 row created.
SQL> select * from kim.aa;
PNO PNAME
---------- ----------
111 COREA
222 CHINA
333 JAPAN
SQL> conn kim/gun
Connected.
SQL> select * from aa;
PNO PNAME
---------- ----------
111 COREA
222 CHINA
333 JAPAN
SQL> conn kim2/gun2;
Connected.
SQL> select * from user_tab_privs;
GRANTEE OWNER TABLE GRANT PRIVILEGE GRA HIE
-------- ----- ----- ----- ---------- --- ---
KIM2 KIM AA KIM SELECT NO NO
SQL> select * from user_tab_privs_recd;
OWNER TABLE GRANT PRIVILEGE GRA HIE
----- ----- ----- ---------- --- ---
KIM AA KIM SELECT NO NO
SQL>
--------------------------------------------------------------------------------
객체 권한 회수(REVOKE)
【형식】
REVOKE 객체권한명
ON 객체명
FROM 사용자명, 롤명, PUBLIC
[CASCADE CONSTRAINTS;
• REVOKE 명령을 사용하여 객체권한을 회수한다.
• 권한 부여자는 자신이 권한을 부여했던 사용자에게만 권한을 회수할 수 있다.
• PUBLIC으로 부여된 객체권한은 SYSTEM 권한과 달리 각각 회수할 수도 있다.
• CASCADE CONSTRAINTS 옵션은 REFERENCES권한을 회수할 때 사용한다.
여기서 references권한이란 어느 table에 대해서 foreign key를 생성할 수 있는 권한을 의미한다. 만약 이 권한을 다른 사용자에게 주었다가 다시 회수하면 그 권한을 이용해 만든 foreign key에 문제가 발생할 수 있다. 그래서 references권한을 회수해 오면서 그 권한과 관련된참조무결성제약을 함께 삭제하기 위하여 CASCADE CONSTRAINTS 옵션을 사용한다.
【예제】
SQL> conn kim/gun
Connected.
SQL> select * from user_tab_privs; ☜ kim사용자가 부여한 객체 내용을 확인함
GRANTEE OWNER TABLE GRANT PRIVILEGE GRA HIE
-------- ----- ----- ----- ---------- --- ---
KIM2 KIM AA KIM SELECT NO NO
SQL> select * from user_tab_privs_recd; ☜ kim이 받은 객체를 확인함
no rows selected
SQL> REVOKE select ON aa
2 FROM kim2; ☜ kim2에게 부여된 aa객체의 select 권한을 회수함
Revoke succeeded.
SQL> select * from user_tab_privs; ☜ kim이 어떤 객체도 권한부여한 것이 없음(아직도 insert(pno,pname) 객체 권한은 남아 있음)
no rows selected
SQL> conn kim2/gun2
Connected.
SQL> insert into kim.aa values(444,'Honkong'); ☜ kim이 kim2에게 aa객체에 insert권한은 부여되어 있음
1 row created.
SQL> select * from kim.aa; ☜ kim이 kim2로부터 aa객체의 select 권한이 회수된 상태임
select * from kim.aa
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn kim/gun
Connected.
SQL> select * from aa; ☜ kim2가 kim의 aa객체에 insert 객체 권한이 부여된 상태임을 알 수 있음
PNO PNAME
---------- ----------
111 COREA
222 CHINA
333 JAPAN
444 Honkong
SQL> select * from user_col_privs; ☜ 객체의 컬럼 권한을 확인함
GRANTEE OWNER TABLE COLUMN_NAME GRANT PRIVILEGE GRA
-------- ----- ----- ------------------------------ ----- ---------- ---
KIM2 KIM AA PNAME KIM INSERT NO
KIM2 KIM AA PNO KIM INSERT NO
SQL> conn kim2/gun2
Connected.
SQL> select * from user_col_privs;
GRANTEE OWNER TABLE COLUMN_NAME GRANT PRIVILEGE GRA
-------- ----- ----- ------------------------------ ----- ---------- ---
KIM2 KIM AA PNAME KIM INSERT NO
KIM2 KIM AA PNO KIM INSERT NO
SQL>
--------------------------------------------------------------------------------
객체 권한 조회
데이터베이스 내의 모든 개체 권한을 보여주는 DBA_TAB_PRIVS와 컬럼에 지정된 모든 개체 권한은 DBA_COL_PRIVS에 표시된다.
모든 사용자를 위한 객체 권한 뷰 data dictionary view 설명 ALL_TAB_PRIVS 사용자 또는 public으로 부여된 객체 권한 뷰 ALL_TAB_PRIVS_MADE 각 사용자 권한과 사용자 소유의 객체 권한 뷰 ALL_TAB_PRIVS_RECD 사용자 또는 public으로 주어진 객체에 대한 객체 권한 뷰 TABLE_PRIVILEGES 객체 권한 소유자, 부여자, 피부여자이거나 public으로 부여된 객체 권한 뷰 ALL_COL_PRIVS 사용자 또는 public으로 부여된 컬럼 객체 권한 뷰 ALL_COL_PRIVS_MADE 각 사용자 권한과 사용자 소유의 컬럼 객체 권한 뷰 ALL_COL_PRIVS_RECD 사용자 또는 public으로 주어진 객체에 대한 컬럼 객체 권한 뷰 COLUMN_PRIVILEGES 객체 권한 소유자, 부여자, 피부여자이거나 public으로 부여된 컬럼 객체 권한 뷰
일반 사용자 객체 권한 뷰 data dictionary view 설명 USER_TAB_PRIVS 객체 권한 소유자, 부여자, 피부여자의 객체 권한 뷰 USER_TAB_PRIVS_MADE 사용자가 소유자인 객체 권한 뷰 USER_TAB_PRIVS_RECD 객체 권한 피부여자를 위한 뷰 USER_COL_PRIVS 객체 권한 소유자, 부여자,피부여자의 컬럼에 권한 뷰 USER_COL_PRIVS_MADE 사용자가 소유한 컬럼에 권한 뷰 USER_COL_PRIVS_RECD 객체 권한 피부여자를 위한 컬럼의 뷰
|
