[SQL/Oracle]오라클 제어어(Data Control Language)이란??

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

데이터에 대한 접근 권한 부여

데이터베이스 시스템의 트랜잭션을 관리

트랜잭션을 처리하는 SQL을 따로 TCL이라고 부른다.

TCL - 트랜잭션 처리어

COMMIT : 트랜잭션의 정상적인 종료 처리

ROLLBACK : 트랜잭션의 취소 

SAVEPOINT : 트랜잭션 내에 임시 저장점 설정

DCL - 사용자 권한 부여 회수

GRANT : 데이터베이스에 대한 일련의 권한 부여

REVOKE : 데이터베이스에 대한 일련의 권한 회수

권한

사용자가 테이블에 접근할 수 있도록 하거나, 해당 테이블에 사용할 수 있는 SQL문에 대하여 제한을 두는 것

다수의 사용자가 데이터베이스에 접근하여 저장된 정보를 사용하므로 정보에 대한 보안이 이루어져야 한다. 사용자마다 다른 권한을 부여함

으로써 보안을 설정한다.

- 시스템 권한 : 사용자의 생성과 제거, DB접근 및 객체를 생성할 수 있는 권한 등으로 주로 DBA에 부여한다.

- 객체 권한 : 객체를 조작할 수 있는 권한

시스템 권한

사용자가 데이터베이스에서 특정 작업을 수행할 수 있도록 한다.

권한의 ANY 키워드는 사용자가 모든 스키마에서 권한을 가지게 한다.

- 대표적인 시스템 권한

시스템 권한	기능
CREATE SESSION	데이터베이스에 연결할 수 있는 권한
CREATE ROLE	데이터베이스 롤을 생성할 수 있는 권한
CREATE VIEW	뷰의 생성 권한
ALTER USER	생성한 사용자의 정의를 변경할 수 있는
DROP USER	생성한 사용자를 삭제시키는 권한

시스템 권한 부여

GRANT [ system_privilege | role ] TO [ user | role | PUBLIC ]
[ WITH ADMIN OPTION ]

system_privilege : 부여할 시스템 권한의 이름

role : 부여할 롤 이름

user, role : 부여할 사용자 이름과 다른 데이터 베이스 롤 이름

PUBLIC : 시스템권한 또는 데이터베이스 역할을 모든 사용자에게 부여할 수 있게 한다.

WITH ADMIN OPTION : 권한을 부여 받은 사용자도 부여받은 권한을 다른 사용자에게 부여할 수 있게 한다.

- scott 계정의 시스템 권한 부여

SQL> CONN sys/6789 AS SYSDBA
SQL> GRANT CREATE USER, ALTER USER, DROP USER TO scott WITH ADMIN OPTION

scott 계정의 사용자를 생성, 수정, 삭제 할 수 있는 권한과 이를 다른 사용자와 롤에 부여할 수 있도록 권한 설정함

시스템 권한 회수

REVOKE [ system_privilege | role ] FROM [ user | role | PUBLIC ]

- scott 계정의 시스템 권한 회수

SQL> REVOKE CREATE USER, ALTER USER, DROP USER TO scott;

scott 계정의 부여했던 사용자를 생성, 수정, 삭제 할 수 있는 권한을 회수함

객체 권한

테이블이나 뷰, 시퀀스, 프로시저, 함수 또는 패키지 중 지정된 객체에 특별한 작업을 할 수 있는 권한을 말한다.

기본적으로 객체를 소유한 사용자는 해당 객체의 모든 권한이 자동으로 획득된다.

객체 소유자는 다른 사용자에게 특정 객체 권한을 부여할 수 있다.

PUBLIC으로 권한을 부여하면 회수할 때도 PUBLIC으로 해야 한다.

객체 권한 부여

GRANT object_privilege [ column ]
ON object
TO { user [, user] | role | PUBLIC }
[ WITH GRANT OPTION ]

object_privilege : 부여할 시스템 권한의 이름

object : 객체명

user, role : 부여할 사용자 이름과 다른 데이터베이스 롤

PUBLIC : 객체 권한 또는 데이터베이스 롤을 모든 사용자에게 부여할 수 있게 한다.

WITH GRANT OPTION : 권한을 부여 받은 사용자도 부여받은 권한을 다른 사용자에게 부여할 수 있게 한다.

- scott 계정의 객체 권한 부여

SQL> GRANT SELECT, INSERT
ON emp
TO scott
WITH GRANT OPTION

scott 계정의 emp 테이블에 대한 SELECT, INSERT 객체 권한을 부여하고 다른 사용자에게도 해당 권한을 부여할 수 있게 한다.

객체 권한 회수

REVOKE { privilege[, privilege, ...] | ALL }
ON object
FROM { user[, user] | role | PUBLIC }
[ CASCADE CONSTRAINTS ]

객체 권한을 부여한 부여자만 권한을 회수할 수 있다.

CASCADE CONSTRAINTS : 객체 권한에서 사용된 참조 무결성 제한을 같이 삭제할 수 있다.

- scott 계정의 권한 회수

SQL> REVOKE SELECT, INSERT ON emp FROM scott;

scott 계정에 부여했던 emp 테이블에 대한 SELECT, INSERT 권한 회수

scott 계정의 권한을 회수했으므로 scott 계정의 emp 테이블에 대한 select, insert의 권한을 사용할 수 없으므로 다시 사용하려면 SELECT, 

INSERT 객체 권한 부여를 다시 해줄 시 가능하다.

데이터베이스 ROLE

권한의 부여와 회수를 쉽게할 수 있게 해준다.

권한의 묶어줌. 부여하고자 하는 권한들을 ROLE로 묶어 한번에 여러 권한을 부여하고 회수할 수 있다.

CREATE ROLE 권한을 가진 사용자에 의해 생성된다.

한 사용자가 여러 ROLE에 접근할 수 있으며, 여러 사용자에게 같은 ROLE을 부여할 수 있다.

권한 부여, 회수와 같은 명령어를 사용한다.

사용자 역할에 맞도록 ROLE을 설정하고 유저에게 지정만 하면 되므로 여러 유저가 있더라도 권하을 부여하는 작업을 효율적으로 처리할 수

있다.

- ROLE 생성 문법

CREATE ROLE role_name

- ROLE을 이용한 사용자의 권한 처리 예

-- ROLE 생성
SQL> CREATE ROLE role_manager;

-- ROLE에 권한 부여
SQL> GRANT create session, create table TO role_manager;

-- scott, test123, ttt222 사용자에게 ROLE 부여
SQL> GRANT role_manager TO scott, test123, ttt222;

- ROLE을 부여한 사용자의 권한 회수

SQL> REVOKE role_manager FROM test123, test222;

보통 오라클 DCL의 트랜잭션을 사용할 때는 게시판에서 insert할 때, 오라클에서도 insert될 것을 확인할 때 사용하는 데 변경된 sql문을 

commit, rollback, savepoint를 통해서 데이터 집어넣거나 취소 혹은 임시 저장도 되도록 설정할 수 있다.

사용자 권한 부여, 회수도 가끔 오라클에서 사용하는데 말그래도 시스템이나 객체에 권한을 부여하거나 회수할 때 사용한다.