#13 13일차_DML, DCL, TCL

13일차에 배운 내용을 정리하였다.

목차는 다음과 같다. :

 

1. DML

 - 01 MERGE

2. DCL

 -01 GRANT

 -02 REVOKE

3. TCL

 

---

1. DML

 - 01 MERGE

데이터를 병합하는 명령어이다. 
참조테이블과 완전히 내용이 동일하게 수정하는 개념 -> INSERT, UPDATE, DELETE가 동시에 발생한다.
보통 현업에서는 잘 사용하지 않으나 SQLD에서는 종종 시험에 출제된다.

* 문법
MERGE INTO 수정테이블 T1
USING 참조테이블 T2
      ON (연결조건)
 WHEN MATCHED THEN      -- 연결조건을 만족하는 데이터에 한한 수정 명령어
             UPDATE
                    SET T1.컬럼 = T2.컬럼 
                    [DELETE 조건] -- 단독사용 불가, UPDATE 후 DELETE 처리
 WHEN NOT MATCHED THEN  -- 연결조건을 만족하지 않는 데이터에 한한 수정 명령어
             INSERT INTO 수정테이블(T1.컬럼1, T1.컬럼2)
                           VALUES (T2.컬럼1, T2.컬럼2, ... )

 ** DELETE 문 사용
 UPDATE 후 DELETE 처리 (UPDATE한 대상에'만' 대해서 DELETE 조건 성립 시 삭제됨)
연결조건은 어느 조건에 대해 값이 같을 것이냐를 지정해준다.
수정테이블이 OLD VER, 참조테이블이 NEW VER 정도라고 생각하면 된다.


 + 예제) 아래와 같은 테이블 생성 후 MERGE를 사용하여 테이블 수정
<수정테이블 - OLD_MENU>         <참조테이블 - NEW_MENU>
NO PRICE                        NO  PRICE
1   1000                        1   1500
2   1500                        2   1500
3   2000                        3   2500
4   3000                        4   3000
                                     5   4000

 

CREATE TABLE OLD_MENU(NO NUMBER, PRICE NUMBER);
INSERT INTO OLD_MENU VALUES(1,1000);
INSERT INTO OLD_MENU VALUES(2,1500);
INSERT INTO OLD_MENU VALUES(3,2000);
INSERT INTO OLD_MENU VALUES(4,3000);
SELECT * FROM OLD_MENU;
COMMIT;

CREATE TABLE NEW_MENU(NO NUMBER, PRICE NUMBER);
INSERT INTO NEW_MENU VALUES(1,1500);
INSERT INTO NEW_MENU VALUES(2,1500);
INSERT INTO NEW_MENU VALUES(3,2500);
INSERT INTO NEW_MENU VALUES(4,3000);
INSERT INTO NEW_MENU VALUES(5,4000);
SELECT * FROM NEW_MENU;
COMMIT;

MERGE INTO OLD_MENU T1
USING NEW_MENU T2 
   ON (T1.NO = T2.NO)
 WHEN MATCHED THEN
      UPDATE
         SET T1.PRICE = T2.PRICE
 WHEN NOT MATCHED THEN
      INSERT VALUES(T2.NO, T2.PRICE);

 

 

 5 ROW UPSERTED. 
 UPDATE + INSERT 인데, 몇 개가 UPDATE, 몇 개가 INSERT 되었는지 파악하기가 어렵고 데이터의 검증이 힘들어진다.

같은 상품번호에 대해 가격이 다르면 가격을 참조테이블 기준으로 UPDATE 한다.
새로운 테이블만 가지고 있는 열의 경우 INSERT 한다.

 

 ** 추가 아래 문장 결과 확인

MERGE INTO OLD_MENU T1
USING NEW_MENU T2 
   ON (T1.NO = T2.NO)
 WHEN MATCHED THEN
      UPDATE
         SET T1.PRICE = T2.PRICE
      DELETE WHERE T1.PRICE >=2500
 WHEN NOT MATCHED THEN
      INSERT VALUES(T2.NO, T2.PRICE);

 

 

5 ROWS UPSERETED.
UPDATE + INSERT + DELETE.

---

2. DCL

** 권한의 종류 "
1. 오브젝트 권한 : 특정 테이블 조회 및 수정 권한 
테이블 하나하나씩 지정하는 느낌 
관리자 권한을 갖는 계정(SYS, SYSTEM)으로 부여 및 회수 가능 
테이블 소유자 계정으로 부여 및 회수 가능 

2. 시스템 권한 : 작업 명령어 권한(테이블 생성, 삭제 등) 
관리자 권한을 갖는 계정(SYS, SYSTEM)으로 부여 및 회수 가능 
CREATE TABLE / CREATE ANY TABLE / DROP TABLE / ... 
▲ TABLE 대신 INDEX, VIEW 등 지정 가능

 

 -01 GRANT

 권한을 부여한다.

 GRANT 권한 TO 사용자;

 

 [  GRANT  ]

예제) SCOTT 계정 소유의 EMP 테이블 조회 권한 HR 부여
SCOTT 계정으로 수행

GRANT SELECT ON EMP TO HR;

 본인 (SCOTT) 테이블이므로 본인 테이블은 타인에게 권한을 줄 수 있음
 따라서 HR 계정 기준
SELECT * FROM EMP;          -- 오류
SELECT * FROM SCOTT.EMP;    -- 가능

 +예제) SCOTT 계정 소유의 DEPT 테이블 조회, 수정 권한 HR 부여
 권한은 여러개를 동시에 부여 가능
 테이블은 하나만 지정 가능
GRANT SELECT, INSERT, UPDATE ON DEPT TO HR;

 

[ 사용자 관리 ]
1. 사용자 생성
CREATE USER 사용자명 
IDENTIFIED BY 패스워드; 
[QUOTA 할당량 ON 테이블스페이스]; (생략가능)
 패스워드는 대소문자를 구분한다

 +예제) ITWILL 유저 생성 후 EMP 테이블에 대한 조회 권한 부여
 system 계정으로 수행
CREATE USER ITWILL
IDENTIFIED BY oracle 
quota unlimited on users;

 해당 유저에 대해서는 할당량 제한을 두지 않는다.
 유저를 만든 직후 접속할 수는 없다. (create session 권한 없음)

GRANT CREATE SESSION TO ITWILL;
 ITWILL 계정으로 테이블 생성 시도 시 '권한이 불충분합니다' 에러 발생
CREATE TABLE ITWILL_TEST1(NO NUMBER);   -- 에러 발생

 SYSTEM 계정으로 ITWILL 계정에게 테이블 생성 권한을 아래와 같이 주어야 함
GRANT CREATE TABLE TO ITWILL;   -- 권한 부여
CREATE TABLE ITWILL_TEST1(NO NUMBER);   -- 성공

 EMP 테이블에 대해서 SELECT, INSERT, UPDATE, DELETE 권한을 ITWILL에 부여
GRANT SELECT, INSERT, UPDATE, DELETE ON SCOTT.EMP TO ITWILL;

 ITWILL 계정으로 수행
 ITWILL_TEST2 테이블을 만들되, HR의 소유로 만들기
CREATE TABLE ITWILL_TEST1(NO NUMBER);  -- 에러 발생 ('권한이 불충분합니다.')

 CREATE TABLE (개인의 테이블만 가능)
 CREATE ANY TABLE (다른 사용자의 테이블도 가능)/ DROP TABLE / DROP ANY TABLE ...
GRANT CREATE ANY TABLE TO ITWILL;           -- 권한부여
CREATE TABLE HR.ITWILL_TEST2(NO NUMBER);    -- 성공

---

 -02 REVOKE

 SYSTEM 계정으로 수행)
REVOKE CREATE ANY TABLE FROM ITWILL;
REVOKE SELECT, INSERT, UPDATE, DELETE ON SCOTT.EMP FROM ITWILL;

 ITWILL 계정으로 수행)
CREATE TABLE HR.ITWILL_TEST3(NO NUMBER); -- 에러 (권한이 불충분합니다)

 ** 권한 부여 및 회수는 즉시 반영(재접속하지 않아도 됨)

 

 [ ROLE ]
 권한의 묶음
 권한 관리를 보다 편리하게 하기 위해 생성
 즉시 부여 X

1. ROLE 생성 및 권한 부여
CREATE ROLE 롤이름;
GRANT 권한 TO 롤이름;
REVOKE 권한 FROM 롤이름;
GRANT 롤이름 TO 사용자;

 SYSTEM 계정으로 수행)
CREATE ROLE R1;
GRANT SELECT ON SCOTT.EMP TO R1;
GRANT SELECT ON SCOTT.DEPT TO R1;
GRANT SELECT ON SCOTT.BONUS TO R1;

GRANT R1 TO ITWILL;

 ITWILL 계정으로 수행)
SELECT * FROM SCOTT.EMP;    -- 오류

 ITWILL 계정으로 재접속)
SELECT * FROM SCOTT.EMP;    -- 정상

 ** ROLE을 통해서 권한 부여 시 재접속 이후에 효력이 발생하며,
 회수 시 즉시 효력이 발생한다.

 SYSTEM 계정으로 수행)
 EMP 테이블에 대한 조회권한을 사용자로부터 직접 회수)

REVOKE SELECT ON SCOTT.EMP FROM ITWILL; -- 에러 : 허가하지 않은 권한을 REVOKE 할 수 없습니다.
REVOKE SELECT ON SCOTT.DEPT FROM R1;    -- 가능

 SCOTT.DEPT 조회권한 ROLE에서 회수)
REVOKE SELECT ON SCOTT.DEPT FROM R1;    -- 즉시 효력 발생


 [ 권한 부여 옵션 ]
1. WITH ADMIN
 오브젝트 권한에 대해 중간관리자에게 권한 부여(일임받은 권한을 중간관리자 부여/회수 가능)
 중간관리자를 통해 부여한 권한을 관리자가 직접 회수 불가능
 중간관리자에게 부여한 권한 회수시 제3자에게 부여된 권한도 함께 회수 가능

2. WITH GRANT
 시스템, 롤 권한에 대해 중간관리자에게 권한 부여(일임받은 권한을 중간관리자 부여/회수 가능)
 중간관리자를 통해 부여한 권한을 관리자가 직접 회수 가능
 중간관리자에게 부여한 권한 회수시 제3자에게 부여된 권한도 함께 회수 불가능

 권한 부여 옵션 테스트)
1. SYSTEM 계정에서 HR 계정에게 권한 일임
1-1) SCOTT.MOVIE 조회 권한을 HR에게 부여(WITH GRANT OPTION)

GRANT SELECT ON SCOTT.MOVIE TO HR WITH GRANT OPTION;    -- 정상

1-2) CREATE ANY TABLE, CREATE VIEW 권한 HR에게 부여(WITH ADMIN OPTION)

GRANT CREATE ANY TABLE, CREATE VIEW TO HR WITH ADMIN OPTION;    -- 정상



2. HR 계정에서 ITWILL 계정에게 권한 부여
2-1) SCOTT.MOVIE 조회 권한을 ITWILL에게 부여

GRANT SELECT ON SCOTT.MOVIE TO ITWILL;     -- 정상

2-2) CREATE ANY TABLE, CREATE VIEW 권한 ITWILL에게 부여

GRANT CREATE ANY TABLE, CREATE VIEW TO ITWILL;



3. SYSTEM 계정에서 직접 회수 시도
3-1) SCOTT.MOVIE 조회 권한 직접 회수 시도 (ITWILL로부터)

REVOKE SELECT ON SCOTT.MOVIE FROM ITWILL;   -- -- 허가하지 않은 권한을 REVOKE 할 수 없습니다

3-2) CREATE VIEW 권한 직접 회수 시도(ITWILL로부터)

REVOKE CREATE VIEW FROM ITWILL;  -- 가능



4. SYSTEM 계정에서 중간관리자에게 권한 회수 시 제3자 권한 함께 회수 여부 확인
4-1) SCOTT.MOVIE 조회 권한 HR로부터 회수 / ITWILL에서 해당 권한 확인

REVOKE SELECT ON SCOTT.MOVIE FROM HR;   -- 가능

4-2) CREATE ANY TABLE 권한 HR로부터 회수 / ITWILL에서 해당 권한 확인

REVOKE SELECT, INSERT, UPDATE, DELETE ON SCOTT.MOVIE FROM HR;  -- 허가하지 않은 권한을 REVOKE 할 수 없습니다.


 * 권한 현황 확인
SELECT *
  FROM DBA_TAB_PRIVS
 WHERE GRANTEE = 'HR';
 
SELECT *
  FROM DBA_SYS_PRIVS
 WHERE GRANTEE = 'HR';

 

---

3. TCL

 트랙젝션 종료어
 1) COMMIT : 변경 영구 저장
 2) ROLLBACK : 변경 취소 (COMMIT 이전으로는 불가)
 3) SAVEPOINT : 변경 지점 저장

 +예제) 아래와 같이 명령어 순서대로 실행했을 때 최종 출력 결과 확인

 

SELECT *
  FROM OLD_MENU;
  
INSERT INTO OLD_MENU VALUES(6, 5000);
INSERT INTO OLD_MENU VALUES(7, 6000);

COMMIT;

DELETE OLD_MENU WHERE NO = 1;
SAVEPOINT SP1;

UPDATE OLD_MENU SET PRICE = 3000;
ROLLBACK TO SP1;

COMMIT;

SELECT *
  FROM OLD_MENU;