[DB] 시스템 권한 및 뷰(View) [Oracle]

 

 

Oracle DBMS 기준으로 권한은 시스템 권한과 객체권한으로 나뉜다. 지난 두 개의 포스팅에서 객체 권한과 관련된 내용을 다루어 보았다. 이번 포스팅에서는 시스템 권한을 다루어 볼까 한다.

 

시스테권한(System Privileges)은 '조작'보다는 '생성'과 '삭제'와 같이 시스템 구성에 영향을 끼치는 명령을 다루는 권한이다. 그러므로 데이터 관리자 권한이 있는 계정이 아니면 접근할 수 없는 중요한 권한이다. (SYS, SYSTEM)

 

사용자의 생성,삭제 (CREATE user, DROP user)

세션 생성 (CREATE session) (DB 로그인 권한)

테이블 생성, 삭제, 읽기 권한 (CREATE, DROP, SELECT)

뷰 생성 삭제 권한(CREATE, DROP)

테이블 스페이스 생성 삭제 권한 (CREATE, DROP)

롤 생성, 삭제 권한 (CREATE, DROP)

 

사용자 생성

 

CREATE USER lee

INDENTIFIED BY 1234

DEFAULT TABLESPACE users

QUOTA unlimited ON users;

 

users 테이블 스페이스는 Oracle DBMS를 설치하면 자동 생성되어있는 테이블 스페이스이다. Oracle은 사용자 생성 시, 사용자가 활동할 테이블 스페이스를 할당해주어야 한다. 테이블 스페이스에서 만든 테이블들은 데이터 파일(.DBF)로 저장되어 DISK에 저장된다. 데이터 파일(.DBF)는 테이블 스페이스 생성시 동시에 생성된다. 테이블 스페이스는 한 개 이상의 데이터 파일을 가질 수 있다.

 

접속 권한 부여

 

이렇게 사용자가 생성되었지만 어떤 권한도 없는 상태이다. 심지어는 DB에 접속조차 할 수 없다. 그 이유는 사용자가 DB에 접속할 때마다 사용자 세션이 생성되어야 DBMS가 사용자 관리를 할 수 있기 때문이다. 하지만 사용자는 세션 생성 권한이 없기에 DB 접속이 불가능하다. 그러므로 세션을 만드는 권한을 주어 DB 접속 권한을 준다.

 

GRANT create session TO lee;

 

create session 권한은 connect 롤 안에 부여되어 있다. 그래서 주로 접속권한을 부여할 때는 아래와 같이 한다.

 

GRANT connect TO lee;

 

객체 생성 권한 부여

출처 입력

이제 DB접속이 가능하다. 하지만 할 수 있는 일이 아무것도 없다. 데이터를 관리할 수 있도록 데이터를 담는 논리적 단위인 '객체'를 생성할 수 있는 권한을 주어야 한다. 각종 객체를 생성할 수 있는 권리를 갖는 롤이 resource 롤이다.

GRANT resource TO lee;

 

위와 같은 명령은 lee가 각종 객체를 생성할 수 있는 권한을 준다. 만약 TABLE만 생성할 수 있는 권한을 준다면 아래와 같이 해야한다.

 

GRANT create table TO lee;

 

이렇게 테이블 생성권한을 가진 lee는 본인이 생성한 테이블에 관한 모든 권한을 갖는다. 그리고 그 권한을 다른 사용자에게 부여할 수도 있다. 사용자 생성 권한이 있는 sys 계정으로 kim 사용자를 만들고 접속권한을 주었다고 가정해보자. lee는 테이블 생성 권한으로 student 테이블을 만들었다. 그러면 lee는 kim 계정으로 아래와 같이 권한 부여가 가능하다.

 

GRANT select ON student TO kim;

 

kim 계정은 select 권한을 부여받았으니 lee가 생성한 student 테이블에서 레코드를 추출할 수 있다.

 

CONN kim/1234; 로그인 (계정/비밀번호)

SELECT * FROM lee.student;

권한전파 권한 부여

출처 입력

lee는 권한을 부여할 때 kim에게 권한을 전파할 권한도 부여할 수 있다.

 

GRANT select ON student TO kim WITH GRANT OPTION;

 

이제 kim은 다른 사용자에게 권한을 부여할 수 있다.

 

GRANT select ON lee.student TO chang

 

WITH GRANT OPTION은 객체권한 전파만 가능하다. 한마디로 lee는 시스템 권한으로 create table 권한을 갖지만 WITH GRANT OPTION으로 다른 사용자에게 시스템 권한 전파 권한을 부여할 수 없다는 말이다. 시스템 권한 전파 권한을 부여하려면 WITH ADMIN OPTION을 사용해야한다.

 

GRANT create table TO kim WITH ADMIN OPTION;

 

REVOKE 권한 회수

 

부연된 권한은 REVOKE를 통해 회수할 수 있다. 이때 연쇄적 회수가 이루어진다. lee가 kim에게 kim이 chang에게 권한을 주었다. lee가 kim의 권한을 회수하면 자동으로 chang의 권한도 회수된다.

 

REVOKE select ON student FROM kim;

 

뷰(View)

뷰는 기존 테이블로 부터 생성된 가상의 테이블이다. 뷰는 기존의 테이블과 동등하게 사용될수 있다. INSERT, UADATE, DELETE 문을 실행할 수 있다.

 

CREATE OR REPLACE view <뷰이름> AS <SELECT문> WITH READ ONLY;

 

SELECT문을 통해 추출된 레코드들이 뷰를 구성한다. OR REPLACE 가 들어 있으면, 같은 뷰가 존재하는 경우 기존의 뷰를 삭제하고 새로 생성한다. WITH READ ONLY가 쓰이면 읽기 전용 뷰로 DML 사용이 불가능하다. 이렇듯 뷰는 실제 테이블에서 SELECT된 레코드들로 이루어진 가상의 테이블이다. 그러므로 이를 잘 활용하면 강력한 보안 수단이 된다.

 

DB 사용자가 데이터를 관리하려면 테이블에 접근해야한다. 테이블은 실제 데이터가 저장되어 있는 논리적인 단위이다. DB 사용자에게 테이블 관리 권한을 주면 테이블 안의 데이터는 DB 사용자의 입맛에 따라 바뀔 수 있다. 그러므로 이를 방지하기 위해서 테이블이 아닌 뷰를 생성하여 DB 사용자에게 뷰를 관리할 권한을 주는 것이다. 뷰는 테이블에서 추출된 레코드를 다루고 있으므로 실제 데이터는 DB 사용자로부터 안전하게 지킬 수 있다.

 

[ 뷰 생성 권한 부여]

GRANT create view TO lee;

 

뷰 생성권한은 시스템 권한이다. 그러므로 뷰를 생성하려면 DBA 관리자 권한을 가진 계정으로부터 권한을 부여 받아야 한다.

 

[ 뷰 생성 ]

CREATE VIEW junior AS SELECT stu_id, stu_name, year FROM student WHERE year >= 3;

 

[ kim에게 뷰 select 권한 부여 ]

GRANT select ON junior TO kim;

 

[ 뷰 삭제 권한 부여 ]

GRANT drop view TO lee;

 

DBA 권한 계정으로부터 뷰 생성 권한만 부여받았으므로 뷰 삭제 권한을 부여받아야 한다.

 

[ 뷰 삭제 ]

DROP VIEW junior;

 

 

---

참고자료

 

Understanding of Database

저자 : 이상구, 장재영, 김한준, 정재헌

출판 : 이한미디어발매2012.08.20.