국비 36일차 - [TCL, DCL, VIEW] COMMIT, ROLLBACK, GREANT, REVOKE, VIEW

■ TCL

- 트랜잭션을 제어하는 언어

- 트랜잭션 : 하나의 논리적인 작업 단위

 

1. COMMIT
- 모든 작업들을 정상적으로 처리하겠다고 확정하는 구문

2. ROLLBACK
- 모든 작업들을 취소처리 하겠다고 확정하는 구문 (마지막 COMMIT 시점으로 되돌아감)

3. SAVEPOINT

- 저장점을 지정하고 ROLLBACK 진행 시 SAVEPOINT 까지의 일부만 롤백

 

 

■ DCL

- 데이터 제어어

- 계정에게 시스템 권한, 객체에 대한 접근 권한 부여 및 회수하는 구문

 

1. 시스템 권한
- 데이터베이스에 접근하는 권한, 오라클에서 제공하는 객체를 생성할 수 있는 권한

- 종류

CREATE SESSION : 데이터베이스에 접속할 수 잇는 권한
CREATE TABLE : 테이블을 생성할 수 있는 권한
CREATE VIEW : 뷰를 생성할 수 있는 권한
CREATE USER : 계정을 생성할 수 있는 권한
DROP USER : 계정을 삭제할 수 있는 권한

- 표현법

GRANT 권한 1, 권한 2, ... TO 사용자계정;
REVOKE 권한 1, 권한 2, ... FROM 사용자계정;

-- 1. SAMPLE 계정 생성
CREATE USER SAMPLE IDENTIFIED BY SAMPLE;

-- 2. 계정에 접속하기 위해서 CREATE SESSION 권한을 부여
GRANT CREATE SESSION TO SAMPLE;

-- 3. 계정에서 테이블을 생성할 수 있는 CREATE TABLE 권한을 부여
GRANT CREATE TABLE TO SAMPLE;

-- 4. 테이블 스페이스 할당
ALTER USER SAMPLE QUOTA 2M ON SYSTEM;

 

2. 객체 권한

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

- SELECT, INSERT, UPDATE, DELETE, ALTER 등

- 표현법

GRANT 권한 ON 객체 TO 사용자계정;
REVOKE 권한 ON 객체 FROM 사용자계정;

-- 5. STUDY.EMPLOYEE 테이블을 SAMPLE 계정에서 조회할 수 있는 권한 부여
GRANT SELECT ON STUDY.EMPLOYEE TO SAMPLE;

-- 6. STUDY.EMPLOYEE 테이블을 SAMPLE 계정에서 삽입할 수 있는 권한 부여
GRANT INSERT ON STUDY.EMPLOYEE TO SAMPLE;

-- 7. 모든 테이블에 대한 조회 권한을 설정
GRANT SELECT ANY TABLE TO SAMPLE;

-- 8. 모든 테이블에 대한 조회 권한을 회수
REVOKE SELECT ANY TABLE FROM SAMPLE;

 

3. ROLE

- 특정 권한들을 하나로 묶어 놓은 것

SELECT *
FROM ROLE_SYS_PRIVS
WHERE ROLE = 'CONNECT';
--WHERE ROLE = 'RESOURCE';

 

■ VIEW

- SELECT문을 저장할 수 있는 객체 (논리적인 가상 테이블)

- 테이블에 대한 SQL만 저장되어 있음 (데이터 저장 X)

- VIEW 이용해 DML(SELECT, INSERT, UPDATE, DELETE 등) 사용 시 실제 데이터 저장되어있는 테이블에도 적용됨

-- VIEW 생성
CREATE VIEW  V_EMPLOYEE
AS SELECT E.EMP_ID, E.EMP_NAME, D.DEPT_TITLE, E.SALARY, N.NATIONAL_NAME
   FROM EMPLOYEE E
   INNER JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
   INNER JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
   INNER JOIN NATIONAL N ON(L.NATIONAL_CODE = N.NATIONAL_CODE);
   
-- VIEW 정보 조회하는 데이터 딕셔너리
SELECT * FROM USER_VIEWS; -- 데이터 저장되어 있지 않음

- 서브 쿼리의 SELECT 절에 함수나 산술연산이 기술되어 있을 경우

  반드시 컬럼에 별칭 지정해야 함

-- 1) 서브 쿼리에서 별칭 부여
CREATE VIEW V_EMP_01
AS SELECT EMP_ID,
       EMP_NAME,
       DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여') AS "성별",
       EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) AS "근무년수"
    FROM EMPLOYEE;

-- 2) VIEW 생성 시 모든 컬럼에 별칭을 부여
CREATE OR REPLACE VIEW V_EMP_01("사번", "직원명", "성별", "근무년수")
AS SELECT EMP_ID,
       EMP_NAME,
       DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여'),
       EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
    FROM EMPLOYEE;