2020년 11월 06일

업데이트: November 08, 2020

DCL(Data Control Language)

데이터베이스, 데이터베이스 객체에 대한 접근 권한을 제어(부여, 회수)하는 언어이며 GRANT(권한 부여), REVOKE(권한 회수)가 있다.

/*  
1. 시스템 권한
- 사용자의 객체 생성/삭제, 데이터베이스 접속 등에 관련된 권한을 의미함

[작성법]
GRANT 권한1, 권한2, .....
TO 사용자명;

REVOKE 권한1, 권한2, ....
FROM 사용자명;

*/


-- 권한 종류 
/*
CRETAE SESSION   : 데이터베이스 접속 권한
CREATE TABLE     : 테이블 생성 권한
CREATE VIEW      : 뷰 생성 권한
CREATE SEQUENCE  : 시퀀스 생성 권한
CREATE PROCEDURE : 함수(프로시져) 생성 권한
CREATE USER      : 사용자(계정) 생성 권한
DROP USER        : 사용자(계정) 삭제 권한
DROP ANY TABLE   : 임의 테이블 삭제 권한
*/

/*  
계정의 종류

1) 관리자 계정 : 데이터베이스의 생성과 관리를 담당하는 계정이며 
                모든 권한과 책임을 가지는 계정 (SYS, SYSTEM)

2) 사용자 계정 : 데이터베이스에 대하여 질의, 갱신, 보고서 작성 등의 작업을 수행할 수 있는 
                계정으로 업무에 필요한 최소한의 권한만 가지는 것을 원칙으로 한다
*/


-- SQL DEVELOPER(SYS)
-- 테스트용 계정 생성

CREATE USER sample IDENTIFIED BY sample;
-- User SAMPLE이(가) 생성되었습니다.

-- SQLPLUS에서  sample 계정으로 로그인 시도
-- ORA-01045: user SAMPLE lacks CREATE SESSION privilege; logon denied
--> CREATE SESSION 권한이 없어서 로그인 실패 

-- SQL DEVELOPER(SYS)
-- sample 계정에 DB 접속 권한 부여
GRANT CREATE SESSION TO sample;
-- > Grant을(를) 성공했습니다.
--> SQLPLUS에서 다시 sample 계정 로그인 해보기

-- SQLPLUS(sample)
SQL> CREATE TABLE TEST(
  2  TID NUMBER PRIMARY KEY
  3  );
--ORA-01031: insufficient privileges
--->테이블 생성 권한이 없어서 에러 발생

-- SQL DEVELOPER(sys)
-- sample 계정에 테이블 생성 권한 부여
GRANT CREATE TABLE TO sample;
-- Grant을(를) 성공했습니다.


-- SQLPLUS(sample)
-- 테이블 생성 권한(CREATE TABLE)이 부여된 상태에서 다시 테이블 생성
-- ORA-01950: no privileges on tablespace 'SYSTEM' 오류 발생

-- SQL DEVELOPER(sys)
-- sample 계정에 테이블 스페이스 할당량 부여
ALTER USER sample QUOTA 2M ON SYSTEM;
-- User SAMPLE이(가) 변경되었습니다.

-- SQLPLUS(sample)
-- 다시 TEST 테이블 생성해보기  --> Table created(테이블 생성 성공)



/*************************
    ROLE : 사용자에게 허가할 수 있는 권한들의 집합
                    ROLE을 이용하면 권한 부여/회수가 용이함
        - CONNECT : 사용자가 DB에 접속 가능하도록 하는
                                CREATE SEESION의 권한이 작성되어 있는 ROLE
        
        - RESOURCE : CREATE 구문을 이용한 객체 생성 권한과
                                    INSERT, UPDATE, DELETE 구문을 사용할 수 있도록 하는 
                                    권한을 모아둔 ROLE
                                    
*/

-- ROLE 테스트를 위해서 sample 계정 삭제 후 다시 생성

-- SQLPLUS(sample)
-- 삭제 전 접속부터 해제
EXIT;
--Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit  Production


-- SQL DEVELOPER(sys)
-- sample 계정 삭제
DROP USER  sample CASCADE;
-- User SAMPLE이(가) 삭제되었습니다.

-- 다시 sample 계정 생성
CREATE USER sample IDENTIFIED BY sample;
-- User SAMPLE이(가) 생성되었습니다.

-- ROLE을 이용하여 sample 계정에 DB 접속 권한 + 기본 자원 사용 권한 부여
GRANT CONNECT, RESOURCE  TO   sample;
-- Grant을(를) 성공했습니다.

-- SQLPLUS를 다시 키고 sample로 로그인 후 TEST 테이블 다시 생성
--- > 문제없이 접속, 테이블 생성이 진행됨.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/* 2. 객체 권한
    - 특정 객체를 조작할 수 있는 권한
    
    [작성법]
    GRANT 권한 종류[(컬럼명)] | ALL
    ON 객체명 |  ROLE 이름 | PUBLIC
    TO 사용자명;
    
*/

-- SQLPLUS(sample)
-- sample 계정으로 kh 계정에 있는 EMPLOYEE 테이블 조회
SELECT * FROM kh.EMPLOYEE;
-- ORA-00942: table or view does not exist
----> kh 계정에 EMPLOYEE 테이블에 대한 접근 권한이 없어서 오류 발생

-- SQL DEVELOPER(kh)
-- sample 계정에게 kh 계정을의 EMPLOYEE 테이블을 조회할 수 있도록
-- 객체 권한 부여 진행.

-- 객체 권한 종류
/*
   권한 종류         설정 객체
   SELECT              TABLE, VIEW, SEQUENCE
    INSERT              TABLE, VIEW
    UPDATE              TABLE, VIEW
    DELETE              TABLE, VIEW
    ALTER               TABLE, SEQUENCE
    REFERENCES          TABLE
    INDEX               TABLE
    EXECUTE             PROCEDURE
*/

GRANT SELECT ON kh.EMPLOYEE  TO sample;
-- Grant을(를) 성공했습니다.

-- SQL DEVELOPER(kh)
-- sample 계정에 부여했던 EMPLOYEE 테이블 SELECT 권한회수
REVOKE SELECT ON kh.EMPLOYEE FROM sample;
-- Revoke을(를) 성공했습니다.

-- 권한 회수 후 SQLPLUS(sample)에서 다시 kh.EMPLOYEE 조회
-- REVOKE SELECT ON kh.EMPLOYEE FROM sample;

-- ROLE 권한 확인
SELECT grantee, privilege
    FROM DBA_SYS_PRIVS
    WHERE grantee IN('CONNECT' ,'RESOURCE');
    
-- 사용자에게 부여된 권한 확인
SELECT *
 FROM USER_ROLE_PRIVS;

VIEW

SELECT 쿼리의 실행 결과를 화면에 저장한 논리적 가상 테이블이다. 실제 테이블과는 다르게 실질적 데이터를 저장하고 있진 않지만 사용자는 테이블을 사용하는 것과 동일하게 사용 가능하다.

VIEW 사용 목적

복잡한 SELECT 쿼리문을 단순화하여 쉽게 사용한다.
테이블의 진짜 모습을 감출 수 있어 보안상 유리하다.

VIEW의 단점

ALTER 구문 사용 불가(VIEW는 가상 테이블이므로 수정이 불가능하다.)
VIEW를 이용한 DML을 사용할 수는 있으나 제약이 따른다.
- SINGLE TABLE VIEW에서만 부분적으로 DML 사용이 가능하다.

DML로 조작이 불가능한 경우

VIEW에 포함되지 않은 컬럼을 조작하는 경우
VIEW에 포함되지 않은 컬럼 중에 베이스 테이블 컬럼이 NOT NULL 제약 조건이 있는 경우
VIEW의 컬럼이 산술 표현식으로 정의된 경우
VIEW 생성 시 서브쿼리에 GROUP BY절이 포함되어 있는 경우
VIEW의 컬럼에 DISTINCT가 포함된 경우
VIEW 생성에 사용된 서브쿼리에 JOIN이 작성되어 있는 경우

/*
[작성법]
CREATE [OR REPLACE] VIEW 뷰명
AS 서브쿼리;
*/

-- 1. VIEW 사용 예시
-- 모든 사번, 이름, 부서명, 근무지역을 조회하는 SELECT문 작성 후
-- 해당 결과를 VIEW로 생성해서 저장
CREATE VIEW V_EMPLOYEE
AS
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_ID = DEPT_CODE)
LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
LEFT JOIN NATIONAL USING(NATIONAL_CODE);
-- ORA-01031: insufficient privileges : 뷰 생성 권한이 없음
---> sys as sysdba로 접속 전환 후 kh 계정에 뷰 생성 권한을 부여

GRANT CREATE VIEW TO kh;
-- Grant을(를) 성공했습니다.
--> 다시 kh 계정으로 접속 변경 후 위에 있는 VIEW 생성 구문 다시 실행
---> View V_EMPLOYEE이(가) 생성되었습니다.


-- 모든 사번, 이름, 부서명, 근무지역을 조회
SELECT * FROM V_EMPLOYEE;


--*** VIEW는 가상의 테이블이다.
----------> 실제 존재하는 테이블이 아니라 기반이 되는 테이블들의 데이터를 이용해서 결과를 보여주는 것

COMMIT; --> 현재 트랜잭션의 상태를 DB에 반영


-- 사번이 205인 직원의 이름을 '정중앙'으로 변경
SELECT * FROM EMPLOYEE
WHERE EMP_ID = 205;

UPDATE EMPLOYEE 
SET EMP_NAME = '정중앙'
WHERE EMP_ID = 205;
-- 1 행 이(가) 업데이트되었습니다.

-- 베이스테이블 확인
SELECT EMP_NAME FROM EMPLOYEE
WHERE EMP_ID = 205;  -- 정중앙

--V_EMPLOYEE에서도 확인
SELECT EMP_NAME FROM V_EMPLOYEE
WHERE EMP_ID = 205;  -- 정중앙

ROLLBACK;

----------------------------------------------------------------------------------------------------------------------------------------------------
 COMMIT;

UPDATE V_EMPLOYEE 
SET EMP_NAME = '정중앙'
WHERE EMP_ID = 205;

SELECT * FROM EMPLOYEE
WHERE EMP_ID = 205;

ROLLBACK;


--   2) VIEW에 포함되지 않은 컬럼 중에 베이스 테이블 컬럼이 NOT NULL 제약조건이 있는 경우
CREATE VIEW V_JOB3
AS SELECT JOB_NAME FROM JOB;

INSERT INTO V_JOB3 VALUES('인턴');
-- ORA-01400: cannot insert NULL into ("KH"."JOB"."JOB_CODE")

-- VIEW를 이용해 DML을 수행하면
-- VIEW 자체의 데이터가변하는 것이 아니라
-- 베이스 테이블의 값이 변하게 되고
-- 그 결과를 다시 VIEW가 투영하는 것.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



--  1) VIEW에 포함되지 않은 컬럼을 조작하는 경우
CREATE OR REPLACE VIEW V_JOB3
AS SELECT JOB_CODE
    FROM JOB;

SELECT * FROM V_JOB3;

-- 1) VIEW에 포함되지 않은 컬럼을 조작하는 경우
INSERT INTO V_JOB3 VALUES('J8', '인턴');

UPDATE V_JOB3
SET JOB_NAME = '인턴'
WHERE JOB_CODE = 'J7';

DELETE FROM V_JOB3
WHERE JOB_NAME = '사원';


--INSERT INTO V_JOB3 VALUES('J8');

SELECT * FROM JOB;


--------------------------------------------------------------------------------------------------------------------

--   2) VIEW에 포함되지 않은 컬럼 중에
--       베이스 테이블 컬럼이 NOT NULL 제약조건이 있는 경우
-- INSERT시에 오류
CREATE OR REPLACE VIEW V_JOB3
AS SELECT JOB_NAME
    FROM JOB;
    
SELECT * FROM V_JOB3;

INSERT INTO V_JOB3 VALUES('인턴');
-- 베이스 테이블인 JOB에 JOB_CODE는 NOT NULL 제약조건이 지정되어있음. --> 에러

INSERT INTO V_JOB3 VALUES ('J8', '인턴');
--  뷰에 정의되지 않은 컬럼 조작 --> 에러

-- UPDATE/DELETE는 문제없이 가능
INSERT INTO JOB VALUES('J8','인턴');

SELECT * FROM V_JOB3;

UPDATE V_JOB3 SET JOB_NAME = '알바'
WHERE JOB_NAME = '인턴';

SELECT * FROM V_JOB3;
SELECT * FROM JOB;

DELETE FROM V_JOB3
WHERE JOB_NAME = '알바';

SELECT * FROM V_JOB3;
SELECT * FROM JOB;


--------------------------------------------------------------------------------------------------------------------

--  3) VIEW의 컬럼이 산술 표현식으로 정의된 경우
CREATE OR REPLACE VIEW EMP_SAL
AS SELECT EMP_ID, EMP_NAME, SALARY,
	    (SALARY + (SALARY*NVL(BONUS, 0)))*12 연봉
     FROM EMPLOYEE;

SELECT * FROM EMP_SAL;

-- 뷰에 산술 계산식이 포함된 경우 INSERT/UPDATE 시 에러 발생
INSERT INTO EMP_SAL VALUES(800, '정진훈', 3000000, 36000000);

UPDATE EMP_SAL
SET 연봉 = 8000000
WHERE EMP_ID = 200;


-- DELETE할 때는 사용 가능
COMMIT;

DELETE FROM EMP_SAL
WHERE 연봉 = 124800000;

SELECT * FROM EMP_SAL;
SELECT * FROM EMPLOYEE;

ROLLBACK;


--------------------------------------------------------------------------------------------------------------------

-- 4) VIEW 생성 시 서브쿼리에 GROUP BY절이 포함되어 있는 경우
CREATE OR REPLACE VIEW V_GROUPDEPT
AS SELECT DEPT_CODE, SUM(SALARY) 합계, AVG(SALARY) 평균
     FROM EMPLOYEE
     GROUP BY DEPT_CODE;
     
SELECT * FROM V_GROUPDEPT;

-- 그룹함수 또는 GROUP BY를 사용한 경우 INSERT/UPDATE/DELETE 시 에러 발생
INSERT INTO V_GROUPDEPT
VALUES ('D10', 6000000, 4000000);  -- 에러남

UPDATE V_GROUPDEPT
SET DEPT_CODE = 'D10'
WHERE DEPT_CODE = 'D1';

DELETE FROM V_GROUPDEPT
WHERE DEPT_CODE = 'D1';


--------------------------------------------------------------------------------------------------------------------

--   5) VIEW의 컬럼에 DISTINCT가 포함된 경우

CREATE OR REPLACE VIEW V_DT_EMP
AS SELECT DISTINCT JOB_CODE
     FROM EMPLOYEE;
     
SELECT * FROM V_DT_EMP;     
    
-- DISTINCT를 사용한 경우 INSERT/UPDATE/DELETE 시 에러 발생    
INSERT INTO V_DT_EMP VALUES(‘J9’);

UPDATE V_DT_EMP
SET JOB_CODE = 'J9'
WHERE JOB_CODE = 'J7';

DELETE FROM V_DT_EMP WHERE JOB_CODE = ‘J1’;


--------------------------------------------------------------------------------------------------------------------

-- 6) VIEW 생성에 사용된 서브쿼리에 JOIN이 작성되어 있는 경우
CREATE OR REPLACE VIEW V_JOINEMP
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE
     FROM EMPLOYEE
     JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);
     
SELECT * FROM V_JOINEMP;

-- 뷰 정의 시 JOIN을 사용한 경우 INSERT/UPDATE 시 에러 발생
INSERT INTO V_JOINEMP VALUES(888, '조세오', '인사관리부');

UPDATE V_JOINEMP
SET DEPT_TITLE = '인사관리부'
WHERE EMP_ID = 219; 

-- 단 DELETE는 가능
COMMIT;

DELETE FROM V_JOINEMP
WHERE EMP_ID = 219;

SELECT * FROM V_JOINEMP;
SELECT * FROM EMPLOYEE;
SELECT * FROM DEPARTMENT;

ROLLBACK;

VIEW의 구조

VIEW 정의 시 사용한 SELECT문이 TEXT라는 컬럼에 저장되어있으며 VIEW가 호출되는 경우 TEXT 컬럼에 기록된 SELECT문을 다시 실행하여 그 결과를 보여주는 구조이다.

-- UESR_VIEWS : 사용자 정의 뷰를 확인하는 딕셔너리 뷰
SELECT * FROM USER_VIEWS;

SELECT * FROM V_EMPLOYEE;

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_ID = DEPT_CODE)
LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
LEFT JOIN NATIONAL USING(NATIONAL_CODE);

VIEW 옵션

/* 
4. VIEW 옵션

[작성법]
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름 [(별칭 [, 별칭...])]
AS 서브쿼리
[WITH CHECK OPTION]
[WITH READ ONLY]

WITH CHECK OPTION : 옵션을 설정한 컬럼 값을 수정 불가능하게 만드는 옵션
WITH READ ONLY : 뷰를 읽기 전용으로 사용하는 옵션 (SELECT만 가능)
*/

CREATE VIEW V_EMP_JOB
AS
SELECT EMP_ID 사번, EMP_NAME 이름, JOB_NAME 직급명, 
DECODE( SUBSTR(EMP_NO, 8, 1), '1','남','2','여' ) 성별
FROM EMPLOYEE
NATURAL JOIN JOB;

SELECT * FROM V_EMP_JOB;


CREATE OR REPLACE VIEW V_EMP_JOB(사번, 이름, 직급명, 성별)
AS
SELECT EMP_ID , EMP_NAME , JOB_NAME , 
DECODE( SUBSTR(EMP_NO, 8, 1), '1','남','2','여' ) 
FROM EMPLOYEE
NATURAL JOIN JOB;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


-- FORCE / NOFORCE
-- VIEW 생성 시 사용되는 서브쿼리의 베이스 테이블이 존재하지 않아도 VIEW를 생성할 수 있다 / 없다를 가르는 옵션
-- NOFORCE 기본값

CREATE OR REPLACE FORCE VIEW V_EMP
AS SELECT TCODE, TNAME, TCONTENT
FROM TT;
-- 경고: 컴파일 오류와 함께 뷰가 생성되었습니다.

SELECT * FROM V_EMP;

Twitter Google+ LinkedIn

techhan

2020년 11월 06일

DCL(Data Control Language)

VIEW

VIEW 사용 목적

VIEW의 단점

DML로 조작이 불가능한 경우

VIEW의 구조

VIEW 옵션

공유하기

댓글남기기

관련 글 더보기

[정올] 1658_최대공약수와 최소공배수 (JAVA)

[정올] 2809_약수 (JAVA)

[정올] 1402_약수 구하기 (JAVA)

[정올] 1071_약수와 배수 (JAVA)