DCL> 사용자 관리와 Grant, Revoke

DCL, Data Control Language

Grant, Revoke

 

 

 

적절한 데이터베이스 보안을 위해 사용자마다 서로 다른 권한과 역할을 부여받는다.

 

사용자가 테이블이나 SQL문을 사용하는 데에 제한을 두는데,

생성과 제거, DB접근, 각종 객체를 생성할 수 있는 권한 등 이러한 것들은 DBA에 의해 부여된다. (sys, system.)

 

권한은 크게 시스템 권한, 객체 권한 두 가지로 나눌 수 있다.

 

 

 

1. 시스템 권한 

데이터베이스 관리자(DBA)가 가지고 있는 권한. (create user, drop user...)

시스템 관리자가 일반 사용자에게 부여해야 하는 권한도 있다. (create session, create table...)

새로 계정을 생성하고 나서는 아무런 권한이 없다. 권한을 주지 않으면 접속도 할 수가 없다.

 

시스템 권한	기능
CREATE USER	새롭게 사용자를 생성하는 권한
DROP USER	사용자를 삭제하는 권한
DROP ANY TABLE	임의의 테이블을 삭제할 수 있는 권한
QUERY REWRITE	함수 기반 인덱스를 생성하는 권한
BACKUP ANY TALBE	임의의 테이블을 백업할 수 있는 권한
CREATE SESSION	데이터베이스에 접속할 수 있는 권한
CREATE TABLE	테이블을 생성할 수 있는 권한
CREATE VIEW	뷰를 생성할 수 있는 권한
CREATE SEQUENCE	시퀀스를 생성할 수 있는 권한
CREATE PROCEDURE	함수를 생성할 수 있는 권한

 

 

 

 

이 권한을 알아보기 위해 평소 사용하던 scott 계정이 아닌 system 계정으로 워크시트를 생성하여 실습해 보았다.

 

 

 

1) 계정 생성

 

계정 생성
create user 아이디 identified by 비밀번호;

비밀번호 변경
alter user 아이디 indentified by 새비밀번호;

 

 

 

 

이 명령어를 권한이 없는 scott 계정에서 실행하면 오류가 난다.

 

 

 

 

 

 

2) 생성된 계정 확인

 

select*from dba_users;

 

또는 좌측의 메뉴에서도 확인 가능하다.

 

 

 

 

 

 

3) 아직 SQL Developer 프로그램에 새 계정을 등록하지 않았기 때문에

cmd를 열어 접속을 시도해본다.

그러나 로그인을 할 수가 없다.

system 계정에서 계정 생성은 했지만, 접근 권한 (create session) 을 부여하지 않았기 때문이다.

 

 

 

 

 

4) 권한 부여 명령어인 grant를 사용하여 로그인이 가능하도록 하였다.

일전에 뷰 생성 권한을 scott 계정에게 부여할 때 실습해본 기능이다.

 

grant create session to user01;

 

 

 

 

접속 권한을 부여한 후 cmd 창을 새로 열어 로그인을 시도해보면 접속이 가능한 상태가 된 것을 확인할 수 있다.

 

 

권한들은 , 로 계속 나열해서 한번의 명령어로 여러개의 권한을 부여할 수도 있다.

grant create session, create table to user01;

그러나 테이블 생성 권한을 부여받았다고 해서 바로 테이블을 생성할 수 있는 것은 아니다.

이것은 나중에 다루어 보도록 한다.

 

 

 

 

 

 

WITH ADMIN OPTION

 

grant 명령에는 두 가지 옵션이 있다.

그 첫번째가 with admin option인데

이 명령어와 함께 시스템 권한을 부여하면, 받은 시스템 권한을 제 3의 계정에게 부여할 수 있게 된다.

단, 이때 부여받은 시스템 권한 외 다른 권한을 제 3의 계정에게 부여할 수 없다.

 

grant 부여할권한 to 또다른계정 with admin option;

grant create session to user02 with admin option;

 

 

 

 

 

 

 

2. 객체 권한

특정 객체를 조작할 수 있는 권한.

테이블, 뷰, 시퀀스, 함수 같은 특정 객체를 DML문을 사용할 수 있는 권한을 설정한다.

객체에 대한 권한은 그 객체를 만든 사용자에게 주어진다.

 

객체 권한	TABLE	VIEW	SEQUENCE	PROCEDURE
ALTER	v		v
DELETE	v	v
EXECUTE				v
INDEX	v
INSERT	v	v
REFERENCES	v
SELECT	v	v	v
UPDATE	v	v

 

 

 

 

 

1) 새로 생성된 user01 계정에게 scott 계정 소유의 emp 테이블에 대한 select 권한 부여하기

 

아직 SQL Developer 프로그램에 user01을 등록하지 않았기 때문에 cmd를 열어 작업한다.

 

grant select on emp to user01;

 

 

 

2) user01로 접속하여 emp01을 검색하려 하면 오류가 난다.

user01 계정으로 scott 계정 소유의 테이블을 검색하려 했는데, 소유자를 표시해주지 않았기 때문이다.

 

 

3) select*from emp; 대신 select*from scott.emp; 로 검색하면 결과가 잘 나온다.

 

 

 

4) user01이 emp테이블에 접근 할 수 있게 된 것을 확인할 수 있다.

 

SQL Developer > scott > emp > 권한 부여

 

 

 

 

5) 이제 부여받은 객체 권한을 제거해보자

 

Revoke select on emp from user01;

 

사라졌다.

 

 

 

 

 

 

WITH GRANT OPTION

 

grant 명령이 가진 두 번째 옵션으로,

WITH ADMIN OPTION 의 기능과 같다. 다만, 대상이 시스템 권한이 아닌 객체에 대한 권한이다.

이 명령어와 함께 객체 권한을 부여하면, 받은 객체 권한을 제 3의 계정에게 부여할 수 있게 된다.

단, 이때 부여받은 객체 권한 외 다른 권한을 제 3의 계정에게 부여할 수 없다.

 

grant 부여할권한 on 테이블 to 다른계정 with grant option;
grant 부여할권한 on 소유자.테이블 to 또다른계정;

--scott에서 user02에게 emp 접근 권한을 부여할 때
grant select on emp to user02 with grant option;

--user02가 user01에게 emp 접근 권한을 부여할 때
grant select on scott.emp to user01;