[SQL] DCL(Data Control Language) – GRANT, REVOKE, TRANSACTION

DCL(Data Control Language)

o  DATA에 대한 접근권한 설정, 권한을 회수하는 기능/트랜젝션 처리를 담당한다.

o  권한부여/회수 : GRANT, REVOKE

 

GRANT

o  계정에 권한을 부여하는 명령어이다.

o  세부적인 내용(특정 사용자의 특정테이블)에 대해 권한 부여할 때 사용한다.

o  사용 형식 : GRANT 권한명||ROLE명령,권한명,권한명...TO 사용자명

: GRANT ON 테이블 / 다른 소유의 테이블도 접근이 가능하게 만들 수 있다.

CREATE USER QWER IDENTIFIED BY QWER; -- 유저 계정 생싱 GRANT CREATE SESSION TO QWER;   -- 접속 권한 부여 GRANT CREATE TABLE TO QWER; -- 테이블 생성 권한 부여   ALTER USER QWER DEFAULT TABLESPACE USERS; --특정 영역할당 ALTER USER QWER QUOTA UNLIMITED ON USERS; -- 위 영역 안에서 오라클이 사용할 수 있는 무제한 공간 확보(예 바이트단위 풀)

 

o  사용자에게 부여된 권한 확인

SELECT * FROM DBA_SYS_PRIVS -- 개별 권한 확인 WHERE GRANTEE='QWER';   SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='H';   --USER계정(H)에서 조회 SELECT * FROM USER_ROLE_PRIVS WHERE USERNAME='H';

 

o  여러 권한을 하나의 ROLE로 묶어 사용할 수 있다.

-- SYSTEM계정 전환 CREATE ROLE MYROLE; GRANT CREATE TABLE, CREATE SESSION TO MYROLE;   GRANT MYROLE TO QWER;  -- 여러 권한을 한번에 부여

 

o  특정 계정에 권한을 부여하여 다른 계정의 테이블에 접근하게 할 수 있다.

-- SYSTEM계정 전환 GRANT SELECT ON H.EMPLOYEE TO QWER; -- 권한부여 (H계정 EMPLOYEE테이블 조회권한을 QWER계정에게 부여) GRANT INSERT ON H.EMPLOYEE TO QWER; -- 권한부여 (H계정 EMPLOYEE테이블 수정권한을 QWER계정에게 부여) GRANT SELECT, INSERT, UPDATE ON KH.DEPARTMENT TO QWER; -- 여러 권한을 한번에 부여   -- QWER계정 전환 SELECT * FROM KH.EMPLOYEE; INSERT INTO KH.EMPLOYEE(EMP_ID,EMP_NAME,EMP_NO,JOB_CODE,SAL_LEVEL) VALUES('988','CHAN','921122-1234567','J4','S3'); --권한을 부여 받음으로써 다른 계정 테이블 조회 및 삽입 가능

  

REVOKE

o  계정에 부여한 권한을 회수하는 명령어이다.

-- 부여된 권한 회수하기 REVOKE SELECT ON KH.EMPLOYEE FROM QWER;

  

TRANSACTION

o  CRUD(Create, Read, Update, Delete)는 DBMS가 제공하는 가장 기본적이면서 핵심적인 기능으로 데이터베이스에서 최소의 작업 단위라고 할 수 있다.

o  트렌젝션은 하나 이상의 CRUD 작업들을 마치 하나의 실행 단위인 것처럼 묶어주는 기능으로, ATM 기기나 데이터베이스 등의 시스템에서 강조되는 개념이다.

o  트렌젝션은 연관된 여러 개의 CRUD 작업을 묶어 단일 작업처럼 수행할 수 있도록 해 주는 기능이다.

o  하나의 작업 단위(INSERT, UPDATE, DELETE 해당 -> 테이블의 ROW를 수정했을 때)로 실행을 하는 명령어이다.

종류	역할
COMMIT	트렌젝션의 최종 결과가 성공이면 그 결과를 데이터베이스에 반영한다.
ROLLBACK	일부 작업이 수행되지 않아 트렌젝션 최종 결과가 실패인 경우 아무것도 실행되지 않았던 처음 상태로 되돌린다.

o  트렌젝션 4대원칙

원자성(Atomicity)	하나의 트렌젝션 내에 정의된 작업들은 모두 수행되거나 아무것도 수행되지 않아야 한다.
일관성(Consistency)	정당한 데이터만을 데이터베이스에 반영해서 무결성을 유지해야한다.
격리성(Isolation)	하나의 트렌젝션이 실행되는 동안 트렌젝션 내부에 다른 트렌젝션이 접근할 수 없어야 한다.
지속성(Durability)	하드웨어나 소프트웨어에 오류가 발생했더라도 트렌젝션의 결과가 데이터베이스에 계속 보존되어야 한다.

 

o  커밋에 따른 데이터 출력 상황

-- SYSTEM계정 전환 GRANT SELECT ON KH.TBL_USER TO QWER; -- QWER계정에게 권한을 부여한다. GRANT INSERT ON KH.EMPLOYEE TO QWER;   -- QWER계정 전환 INSERT INTO KH.TBL_USER VALUES(2,'CO','123','CHAN','K@K.COM'); INSERT INTO KH.TBL_USER VALUES(3,'C','123','C','K@K.COM'); -- QWER계정으로 다른 계정 테이블 데이터 삽입   SELECT * FROM KH.TBL_USER; -- QWER계정 커밋 전  SELECT * FROM KH.TBL_USER; -- KH계정 커밋 전  COMMIT; -- 커밋은 데이터를 변경한 계정에서 진행한다.   SELECT * FROM KH.TBL_USER; -- KH계정 커밋 후  ROLLBACK; -- COMMIT 전의 내용으로 되돌릴 경우 사용

-  한 테이블에 접속하는 세션(계정)이 다르면 해당 세션에서 COMMIT하기 전까지는 다른 계정에서 추가된 사항을 알 수 없다.

-  ROLLBACK은 COMMIT하기 전까지 내용을 되돌리며 다른 계정에서는 데이터 추가 여부를 알 수 없다.