[SQL-Oracle]DCL(DATA CONTROL LANGUAGE) - 데이타 제어어

⚫DCL(DATA CONTROL LANGUAGE) - 데이타 제어어
--계정에게 필요한 권한을 부여하거나 회수하는 SQL 명령
--오라클 권한(ORACLE PRIVILEGE) : 시스템 권한(관리자)과 객체 권한(일반 사용자)으로 구분 

🔘시스템 권한 : 시스템을 구성하는 객체를 관리하기 위한 명령(DDL)에 대한 사용 권한
--시스템 권한을 계정에게 부여
형식) GRANT {PRIVILEGE|ROLE},{PRIVILEGE|ROLE},... TO {계정명|PUBLIC} [WITH ADMIN OPTION] [IDENTIFIED BY 비밀번호]
--롤(ROLE) : 시스템 권한을 묶어 그룹화하여 사용하는 기능
--계정명 대신 PUBLIC 키워드를 사용하면 모든 계정에게 일괄적으로 시스템 권한 부여
--WITH ADMIN OPTION : 부여받은 시스템 권한을 다른 계정에게 부여하거나 회수할 수 있는 권한을 제공하는 기능
--시스템 권한을 부여받을 계정이 없는 경우 자동으로 계정 생성
--GRANT 명령으로 계정이 생성될 경우 반드시 IDENTIFIED BY 키워드를 사용하여 계정의 비밀번호 설정

--KIM 계정 생성
CREATE USER KIM IDENTIFIED BY 1234;

--KIM 계정으로 오라클 서버에 접속 - SQLPLUS 프로그램 사용
--C:\Users\itwill>SQLPLUS KIM  >> KIM 계정으로 오라클 서버 접속 - 비밀번호 입력
--CREATE SESSION 권한이 없으므로 오라클 서버 접속 실패

--시스템 관리자(SYSDBA - SYS 계정)로 접속하여 KIM 계정에게 CREATE SESSION 시스템 권한 부여

GRANT CREATE SESSION TO KIM;

 

 

 

<SQLPLUS 프로그램 사용 (명령 프롬프트(CMD) 열어서 SQLPLUS 실행)>

--KIM 계정으로 오라클 서버에 접속 
C:\Users\itwill>SQLPLUS KIM  >> KIM 계정으로 오라클 서버 접속 - 비밀번호 입력
--CREATE SESSION 권한이 있으므로 오라클 서버 접속 성공

 

--KIM 계정으로 접속된 환경에서 SAWON 테이블 생성 
--SAWON 테이블 - 사원번호(숫자형-PRIMARY KEY),사원이름(문자형),급여(숫자형)
--SQL> CREATE TABLE SAWON(NO NUMBER(4) PRIMARY KEY,NAME VARCHAR2(20),PAY NUMBER);
--KIM 계정은 CREATE TABLE 시스템 권한이 없으므로 테이블을 생성하면 에러 발생

 

 

 

--시스템 관리자(SYSDBA - SYS 계정)로 접속하여 KIM 계정에게 CREATE TABLE 시스템 권한 부여

GRANT CREATE TABLE TO KIM;

 

<SQLPLUS 프로그램 사용>

--KIM 계정으로 접속된 환경에서 SAWON 테이블 생성 
--SQL> CREATE TABLE SAWON(NO NUMBER(4) PRIMARY KEY,NAME VARCHAR2(20),PAY NUMBER);
--KIM 계정은 CREATE TABLE 시스템 권한이 있으므로 테이블 생성 가능

🔘 객체 권한 : 사용자 스키마에 존재하는 객체 관련 명령(DQL 또는 DML) 사용 관련 권한
형식) GRANT {ALL|PRIVILEGE,PRIVILEGE,...} ON 객체명 TO 계정명 [WITH GRANT OPTION]
--객체 권한은 INSERT, UPDATE, DELETE, SELECT 키워드등을 사용하여 명령을 사용할 수 있는 권한을 표현
--ALL : 겍체에 관련된 모든 명령을 사용할 수 있는 권한을 표현하는 키워드
--WITH GRANT OPTION : 부여받은 객체 권한을 다른 계정에게 부여하거나 회수하는 권한을 제공하는 기능

 

<SQLPLUS 프로그램 사용>

--KIM 계정으로 접속된 환경에서 SCOTT 계정의 DEPT 테이블 검색 
--C:\Users\itwill>SQLPLUS KIM  >> KIM 계정으로 오라클 서버 접속 - 비밀번호 입력
--SQL> SELECT * FROM SCOTT.DEPT; 

--SCOTT 계정의 DEPT 테이블을 검색할 수 있는 객체 권한이 KIM 계정에게 없으므로 에러 발생

 

 

--SCOTT 계정이 KIM 계정에게 DEPT 테이블에 저장된 행을 검색할 수 있는 객체 권한 부여 가능

GRANT SELECT ON DEPT TO KIM;

 

 

<SQLPLUS 프로그램 사용>
--KIM 계정으로 접속된 환경에서 SCOTT 계정의 DEPT 테이블 검색 
--SQL> SELECT * FROM SCOTT.DEPT;
--SCOTT 계정의 DEPT 테이블을 검색할 수 있는 객체 권한이 KIM 계정에게 있으므로 검색 가능

--다른 계정에게 부여한 객체 권한 확인 - USER_TAB_PRIVS_MADE : 부여한 객체 권한 정보를 제공하는 딕셔너리

SELECT * FROM USER_TAB_PRIVS_MADE;

--다른 계정에게 부여 받은 객체 권한 확인 - USER_TAB_PRIVS_RECD : 부여받은 객체 권한 정보를 제공하는 딕셔너리

SELECT * FROM USER_TAB_PRIVS_RECD;

🔘 객체 권한 회수
형식) REVOKE {ALL|PRIVILEGE,PRIVILEGE,...} ON 객체명 FROM 계정명 [WITH GRANT OPTION]

--SCOTT 계정이 KIM 계정에게 부여한 DEPT 테이블을 검색할 수 있는 객체 권한 회수

REVOKE SELECT ON DEPT FROM KIM;
SELECT * FROM USER_TAB_PRIVS_MADE;

 

<SQLPLUS 프로그램 사용>
--KIM 계정으로 접속된 환경에서 SCOTT 계정의 DEPT 테이블 검색
--SQL> SELECT * FROM SCOTT.DEPT;
--SCOTT 계정의 DEPT 테이블을 검색할 수 있는 객체 권한이 KIM 계정에게 없으므로 에러 발생

 

🔘 시스템 권한 회수 - 계정의 모든 시스템 권한을 회수해도 계정 미삭제

형식) REVOKE {PRIVILEGE|ROLE},{PRIVILEGE|ROLE},... FROM {계정명|PUBLIC} [WITH ADMIN OPTION]

--시스템 관리자(SYSDBA - SYS 계정)로 접속하여 KIM 계정에게 부여한 CREATE SESSION 시스템 권한 회수

REVOKE CREATE SESSION FROM KIM;

 

<SQLPLUS 프로그램 사용>
--KIM 계정으로 오라클 서버에 접속
C:\Users\itwill>SQLPLUS KIM  >> KIM 계정으로 오라클 서버 접속 - 비밀번호 입력
--CREATE SESSION 권한이 없으므로 오라클 서버 접속 실패

 

 

🔶롤(ROLE) : 관리자가 계정의 시스템 권한을 효율적으로 관리하기 위해 사용하는 시스템 권한 그룹
--CONNECT ROLE : 기본적인 시스템 권한 그룹 - CREATE SESSION, CREATE TABLE, ALTER SESSION 등
--RESOURCE ROLE : 객체 관련 시스템 권한 그룹 - CREATE TABLE, CREATE SEQUENCE, CREATE TRIGGER 등

 

--관리자가 LEE 계정에게 CONNECT ROLE과 RESOURCE ROLE 부여
--시스템 권한을 부여받아 계정이 없는 경우 계정을 자동 생성 - 반드시 비밀번호 설정

GRANT CONNECT,RESOURCE TO LEE IDENTIFIED BY 5678;