[sql] 15. DCL (GRANT/REVOKE) | 롤(ROLL)

1. DCL  - 데이타 제어어

• DATA CONTROL LANGUAGE
• 계정에게 권한을 부여하거나 회수하는 명령
• 오라클 권한(PRIVILEGE)
• 시스템 권한(관리자)과 객체 권한(일반사용자)으로 구분

 

 

 

 

 

 

2. 시스템 권한(관리자) - GRANT

• 시스템을 구성하는 객체를 관리하기 위한 명령(DDL)에 대한 사용 권한
• DDL -SESSION/TABLE/..에 CREATE, DROP, ALTER ... 할 수 있나요?
• 시스템 권한을 계정에게 부여하는 명령

1) 형식

-- 롤(ROLL) - 시스템 권한을 그룹화하여 표현한 명칭
-- 계정명 대신 PUBLIC 키워드를 사용 - 모든 계정에게 일괄적으로 시스템 권한 부여 가능
-- WITH ADMIN OPTION - 부여 받은 시스템 권한을 다른 계정에게 부여하거나 회수하는 권한을 제공하는 옵션
-- 시스템 권한을 부여받을 계정이 없는 경우 자동으로 계정 생성
-- => 즉, CREATE USER 사용하지 않고, GRANT USER로도 계정 생성이 가능
-- GRANT 명령으로 계정이 생성될 경우 반드시 IDENTIFIED BY를 이용하여 반드시 암호를 설정
-- => 보안에 가장 위험한 계정은 암호가 없는 계정이므로 반드시 암호 설정하기

GRANT {PRIVILEGE|ROLL},{PRIVILEGE|ROLL},...
	TO {계정명|PUBLIC} [WITH ADMIN OPTION] [IDENTIFIED BY 암호]

2) 예제

--1)
--🔔 관리자 세션에서 작업 - KIM 계정 생성
--KIM 계정 생성 - 관리자 세션에서 작업
CREATE USER KIM IDENTIFIED BY 1234;
//User KIM이(가) 생성되었습니다.


--2)
--💕KIM 계정으로 오라클 서버에 접속 - SQLPLUS 사용 : 접속 불가능
--로그인을 시도하지 않고 SQLPLUS 프로그램 실행하는 방법
DOS> SQLPLUS /NOLOG
--오라클 서버에서 접속하기 위해 계정과 비밀번호를 입력 - 로그인처리 (인증을 통한 권한 획득)
SQL> CONN KIM/1234
//ORA-01045: 사용자 KIM는 CREATE SESSION 권한을 가지고있지 않음; 로그온이 거절되었습니다.

--3)
--🔔관리자 세션에서 - "CREATE SESSION" 시스템 권한 부여
--KIM 계정에게 CREATE SESSION 시스템 권한 부여 - 관리자 세션에서 작업
GRANT CREATE SESSION TO KIM; --SESSION에 관련된 모든 명령 사용할 수 있는 권한 부여!
//Grant을(를) 성공했습니다.


--4)
--💕KIM 계정으로 오라클 서버에 접속 - 가능
--CREATE SESSION 시스템 권한 부여받은 후 KIM 계정으로 오라클 서버에 접속 - SQLPLUS 사용 : 접속 가능
--오라클 서버에서 접속하기 위해 계정과 비밀번호를 입력 - 로그인처리(인증을 통한 권한 획득)
SQL> CONN KIM/1234
//연결되었습니다.

--5)
--💕KIM 계정에서 테이블 생성 - 불가능
--KIM 계정으로 SAWON 테이블 생성 : 사원번호(숫자형-PRIMARY KEY),사원이름(문자형),급여(숫자형) - SQLPLUS 사용 : 생성 불가능
SQL> CREATE TABLE SAWON(NO NUMBER(4) PRIMARY KEY, NAME VARCHAR2(20), PAY NUMBER);
//ORA-01031: 권한이 불충분합니다

--6)
--🔔관리자 세션에서 작업 - "CREATE TABLE" 시스템 권한 부여
--KIM 계정에게 CREATE TABLE 시스템 권한 부여 - 관리자 세션에서 작업
GRANT CREATE TABLE TO KIM; --TABLE에 관련된 모든 명령 사용할 수 있는 권한 부여!
//Grant을(를) 성공했습니다.


--7)
--💕KIM 계정에서 테이블 생성 - 가능
--CREATE TABLE 시스템 권한을 부여받은 후 KIM 계정으로 SAWON 테이블 생성 - SQLPLUS 사용 : 생성 가능
SQL> CREATE TABLE SAWON(NO NUMBER(4) PRIMARY KEY,NAME VARCHAR2(20),PAY NUMBER);
//테이블이 생성되었습니다

 

 

 

 

 

 

 

 

3. 객체 권한(일반사용자) - GRANT

• 사용자 스키마의 객체(테이블/시퀀스/뷰) 관련 명령(DQL 및 DML) 사용에 사용 권한
• DQL 및 DML - 저 테이블/시퀀스/뷰..에 SELECT,INSERT,DELETE 할 수 있나요?

1) 형식

--객체 권한(PRIVILEGE)은 INSERT, UPDATE, DELETE, SELECT 등 키워드로 표현
--ALL : 객체에 관련되어있는 모든 명령 사용 권한 표현 (모든 명령 다 사용가능해~)
--WITH GRANT OPTION : 부여받은 객체 권한을 다른 계정에게 부여하거나 회수하는 기능을 제공하는 옵션
--오라클을 제외한 권한부여는 객체권한만 존재
--오라클만 특별히 시스템권한이 있음
--오라클을 제외한 일반적인 객체권한은 GRANT ON 계정명 TO 객체명만 사용함..

GRANT {ALL|PRIVILEGE,PRIVILEGE,...} ON 객체명
	TO 계정명 [WITH GRANT OPTION]

2) 예제

--1)
--🥝SCOTT 계정에서 - 행 검색 가능
--SCOTT 계정의 DEPT 테이블에 저장된 모든 행 검색 - 계정 소유의 객체는 [계정명.객체명]으로 표현
SELECT * FROM SCOTT.DEPT;
--현재 접속 계정의 객체인 경우 계정명 생략 가능 - 현재 접속 계정의 객체가 아닌 경우에만 계정명을 표현
SELECT * FROM DEPT;

--2)
--🍓KIM 계정에서 - 행 검색 불가능 (SQLPLUS 사용)
--KIM계정으로 SCOTT계정의 DEPT 테이블에 저장된 모든 행 검색 - SQLPLUS 사용 : 검색불가능
--현재 접속된 계정의 객체가 아닌 경우 반드시 [계정명.객체명]으로 표현
--KIM계정은 SCOTT 계정에 존재하는 DEPT 테이블에 접근할 수 있는 객체 권한이 없으므로 에러 발생
SQL> SELECT * FROM DEPT; --ERROR
SQL> SELECT * FROM SCOTT.DEPT; --ERROR
//ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
--KIM 계정이 SCOTT 계정에게 DEPT 테이블에 접근할 수 있는 객체 권한 요청 필요

--3)
--🥝SCOTT 계정에서 - KIM 계정에게 행 검색 권한 부여
--SCOTT 계정이 KIM 계정에게 "DEPT 테이블"에 저장된 행을 검색할 수 있는 권한 부여
GRANT SELECT ON DEPT TO KIM;
//Grant을(를) 성공했습니다.


--4)
--🍓KIM 계정에서 - 행 검색 가능
--객체 권한을 부여 받은 후 KIM 계정으로 SCOTT 계정의 DEPT 테이블에 저장된 모든 행 검색 - SQLPLUS 사용 : 검색 가능
SQL> SELECT * FROM SCOTT.DEPT;

 

--5)
--🥝SCOTT 계정에서 - 다른 계정에게 "부여한" 객체 권한 확인
--📕USER_TAB_PRIVS_MADE : 부여한 객체 권한 정보를 제공하는 딕셔너리
SELECT * FROM USER_TAB_PRIVS_MADE;


--6)
--🥝SCOTT 계정에서 - 다른 계정에게 "부여받은" 객체 권한 확인
--📕USER_TAB_PRIVS_RECD : 부여받은 객체 권한 정보를 제공하는 딕셔너리
SELECT * FROM USER_TAB_PRIVS_RECD;

--7)
--🍓KIM 계정에서 - 다른 계정에게 "부여받은" 객체 권한 확인
SELECT * FROM USER_TAB_PRIVS_RECD;

 

 

 

 

 

 

 

 

 

4. 객체 권한 회수 - REVOKE

1) 형식

REVOKE {ALL|PRIVILEGE,PRIVILEGE,...} ON 객체명 FROM 계정명 [WITH GRANT OPTION]

2) 예제

--1)
--🥝SCOTT계정에서 KIM계정 행 검색 권한 회수
--SCOTT계정이 KIM계정에게 DEPT테이블에 저장된 행을 검색할 수 있는 권한 회수
REVOKE SELECT ON DEPT FROM KIM;
//Revoke을(를) 성공했습니다.


--2)
--🥝SCOTT계정에서 - 다른 계정에게 "부여한" 객체 권한 확인
SELECT * FROM USER_TAB_PRIVS_MADE;


--3)
--🍓KIM계정에서 - 다른 계정에게 "부여받은" 객체 권한 확인

 

 

 

 

 

 

 

5. 시스템 권한 회수 - REVOKE

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

1) 형식

REVOKE {PRIVILEGE|ROLL},{PRIVILEGE|ROLL},... FROM {계정명|PUBLIC} [WITH ADMIN OPTION]

2) 예제

--1)
--🔔관리자 세션에서 작업 - "CREATE SESSION" 시스템 권한 회수
--관리자가 KIM 계정에게 부여한 CREATE SESSION 시스템 권한 회수 - 관리자 세션에서 작업
REVOKE CREATE SESSION FROM KIM;
//Revoke을(를) 성공했습니다.


--2)
--💕KIM 계정으로 오라클 서버에 접속 - 불가능
SQL> CONN KIM/1234

 

 

 

 

 

 

 

 

 

6. 롤(ROLL)

• 관리자가 일반 계정의 시스템 권한을 효율적으로 부여하거나 회수하기 위해 사용하는 시스템 권한 그룹
• 오라클에는 기본적으로 제공되는 롤 존재

-- CONNECT
-- 기본적인 시스템 권한 그룹
-- CREATE SESSION , CREATE TABLE , ALTER SESSION , CREATE SYNONYM 등

-- RESOURCE
-- 객체 관련 시스템 권한 그룹
-- CREATE TABLE , CREATE SEQUENCE , CREATE TRIGGER 등

-- DBA
-- 시스템 관리에 필요한 모든 시스템 권한 그룹
-- 관리자 관련 롤




--1)
--🐹관리자 세션에서 작업 - "CONNECT와 RESOURCE 롤" 부여
--관리자가 LEE 계정에게 CONNECT와 RESOURCE 롤 부여 - 관리자 세션 작업
GRANT CONNECT,RESOURCE TO LEE IDENTIFIED BY 1234;
//Grant을(를) 성공했습니다.




--2)
--🐹LEE 계정으로 오라클 서버에 접속 및 테이블 생성 가능
--LEE 계정으로 오라클 서버에 접속하여 SAWON 테이블 생성 - SQLPLUS 사용 : 서버 접속 및 테이블 생성 가능
SQL> CONN LEE/1234
//연결되었습니다.
SQL> CREATE TABLE SAWON(NO NUMBER(4) PRIMARY KEY,NAME VARCHAR2(20),PAY NUMBER);
//테이블이 생성되었습니다

--3)
--🐹관리자 세션에서 작업 - "CONNECT와 RESOURCE 롤" 회수
--관리자가 LEE 계정에게 CONNECT와 RESOURCE 롤 회수 - 관리자 세션 작업
REVOKE CONNECT,RESOURCE FROM LEE;

 

 

 

 

 

PL/SQL 참고

 

https://github.com/jaeheela/java-edu/blob/main/%EA%B5%90%EC%9C%A1%EB%82%B4%EC%9A%A9/02.Oracle/7%EC%9D%BC%EC%B0%A8.sql

java-edu/교육내용/02.Oracle/7일차.sql at main · jaeheela/java-edu

https://github.com/jaeheela/java-edu/blob/main/%EA%B5%90%EC%9C%A1%EB%82%B4%EC%9A%A9/02.Oracle/8%EC%9D%BC%EC%B0%A8.sql

java-edu/교육내용/02.Oracle/8일차.sql at main · jaeheela/java-edu