[SQLD : Ⅳ. SQL 활용] 7. DCL

* DCL 개요

 - DML, TCL 명령어들 이외에도 유저를 생성하고 권한을 제어할 수 있는 DCL(DATA CONTROL LANGUAGE) 명령어

 

* 유저와 권한

 - 유저 생성과 시스템 권한 부여

  [예제] SCOTT 유저로 접속한 다음 PJS 유저(패스워드: KOREA7)를 생성해 본다.

Oracle CONN SCOTT/TIGER 연결되었다. 
CREATE USER PJS IDENTIFIED BY KOREA7; 
CREATE USER PJS IDENTIFIED BY KOREA7; * 1행에 오류: ERROR: 권한이 불충분하다

  SCOTT 유저는 유저를 생성할 권한을 부여받지 못했기 때문에 권한이 불충분하다는 오류가 발생

  Oracle의 DBA 권한을 가지고 있는 SYSTEM 유저로 접속하면 유저 생성 권한을 다른 유저에게 부여할 수 있음

  [예제] SCOTT 유저에게 유저생성 권한(CREATE USER)을 부여한 후 다시 PJS 유저를 생성한다.

Oracle GRANT CREATE USER TO SCOTT; 권한이 부여되었다. CONN SCOTT/TIGER 연결되었다. 
       CREATE USER PJS IDENTIFIED BY KOREA7; 사용자가 생성되었다.

  SQL Server는 유저를 생성하기 전 먼저 로그인을 생성해야 함

  로그인을 생성할 수 있는 권한을 가진 로그인은 기본적으로 sa

  [예제] sa로 로그인을 한 후 SQL 인증을 사용하는 PJS라는 로그인(패스워드: KOREA7)을 생성해 본다.

          로그인 후 최초로 접속할 데이터베이스는 AdventureWorks 데이터베이스로 설정한다.

SQL Server CREATE LOGIN PJS WITH PASSWORD='KOREA7', DEFAULT_DATABASE=AdventureWorks

  유저를 생성하기 위해서는 생성하고자 하는 유저가 속할 데이터베이스로 이동을 한 후 처리

SQL Server USE ADVENTUREWORKS; 
GO CREATE USER PJS FOR LOGIN PJS WITH DEFAULT_SCHEMA = dbo;

  [예제] 생성된 PJS 유저로 로그인한다.

Oracle CONN PJS/KOREA7; 
오류: ERROR: 사용자 PJS는 CREATE SESSION 권한을 가지고 있지 않음; 로그온이 거절되었다.

  PJS 유저가 생성됐지만 아무런 권한도 부여받지 못했기 때문에

  로그인을 하면 CREATE SESSION 권한이 없다는 오류가 발생

  유저가 로그인을 하려면 CREATE SESSION 권한을 부여받아야 함

  [예제] PJS 유저가 로그인할 수 있도록 CREATE SESSION 권한을 부여한다.

Oracle CONN SCOTT/TIGER 연결되었다. 
GRANT CREATE SESSION TO PJS; 권한이 부여되었다. CONN PJS/KOREA7 연결되었다.

  [예제] PJS 유저로 테이블을 생성한다.

Oracle SELECT * FROM TAB; 선택된 레코드가 없다. 
       CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10) );
       CREATE TABLE MENU ( * 1행에 오류: ERROR: 권한이 불충분하다.
       
SQL Server CREATE TABLE MENU ( MENU_SEQ INT NOT NULL, TITLE VARCHAR(10) ); 
데이터베이스 ‘AdventureWorks'에서 CREATE TABLE 사용 권한이 거부되었다.

  PJS 유저는 로그인 권한만 부여되었기 때문에 테이블을 생성하려면 테이블 생성 권한이 불충분하다는 오류가 발생

  [예제] SYSTEM 유저를 통하여 PJS 유저에게 CREATE TABLE 권한을 부여한 후 다시 테이블을 생성

Oracle CONN SYSTEM/MANAGER 연결되었다. GRANT CREATE TABLE TO PJS; 권한이 부여되었다. 
       CONN PJS/KOREA7 연결되었다. CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, 
                                                       TITLE VARCHAR2(10) ); 테이블이 생성되었다.

 SQL Server GRANT CREATE TABLE TO PJS; 권한이 부여되었다. 스키마에 권한을 부여한다. 
 GRANT Control ON SCHEMA::dbo TO PJS 권한이 부여되었다. PJS로 로그인한다. 
 CREATE TABLE MENU ( MENU_SEQ INT NOT NULL, TITLE VARCHAR(10) ); 테이블이 생성되었다.

 

 - OBJECT 에 대한 권한 부여

  특정 유저가 소유한 객체(OBJECT) 권한

  오브젝트 권한은 특정 오브젝트인 테이블, 뷰 등에 대한 SELECT, INSERT, DELETE, UPDATE 작업 명령어를 의미

  다른 유저가 소유한 객체에 접근하기 위해서는 객체 앞에 객체를 소유한 유저의 이름을 붙여서 접근

  SQL Server는 객체 앞에 소유한 유저의 이름을 붙이는 것이 아니고 객체가 속한 스키마 이름을 붙임

Oracle CONN SCOTT/TIGER 연결되었다. 
SELECT * FROM PJS.MENU; 
SELECT * FROM PJS.MENU * 1행에 오류: ERROR: 테이블 또는 뷰가 존재하지 않는다.

SQL Server SCOTT로 로그인한다. 
SELECT * FROM dbo.MENU; 개체이름 ‘dbo.MENU'이(가) 잘못되었다.

  SCOTT 유저는 PJS 유저로부터 MENU 테이블을 SELECT할 수 있는 권한을 부여받지 못했기 때문에

  MENU 테이블을 조회 불가

  [예제] PJS 유저로 접속하여 SCOTT 유저에게 MENU 테이블을 SELECT 할 수 있는 권한을 부여한다.

Oracle CONN PJS/KOREA7 연결되었다. 
INSERT INTO MENU VALUES (1, '화이팅'); 1개의 행이 만들어졌다. 
COMMIT; 커밋이 완료되었다. GRANT SELECT ON MENU TO SCOTT; 권한이 부여되었다.

SQL Server PJS로 로그인한다. 
INSERT INTO MENU VALUES (1, '화이팅'); 1개의 행이 만들어졌다. 
GRANT SELECT ON MENU TO SCOTT; 권한이 부여되었다.

  이제 PJS.MENU 테이블을 SELECT하면 테이블 자료를 볼 수 있음

  오브젝트 권한은 SELECT, INSERT, DELETE, UPDATE 등의 권한을 따로따로 관리

Oracle CONN SCOTT/TIGER 연결되었다. 
SELECT * FROM PJS.MENU;
MENU_SEQ TITLE 
-------- -------- 
1 화이팅 
UPDATE PJS.MENU SET TITLE = '코리아' WHERE MENU_SEQ = 1; 
UPDATE PJS.MENU * 1행에 오=text>

SQL Server SCOTT으로 로그인한다. SELECT * FROM PJS.MENU;
MENU_SEQ TITLE 
-------- ---------- 
1 화이팅 
UPDATE PJS.MENU SET TITLE = '코리아' WHERE MENU_SEQ = 1; 
개체 ‘MENU', 데이터베이스 ’AdventureWorks', 스키마 ‘dbo'에 대한 UPDATE 권한이 거부되었다.

  권한이 부족하여 UPDATE를 할 수 없다는 오류,  PJS 유저에게 UPDATE 권한을 부여한 후 다시 시도

 

* ROLE을 이용한 권한 부여

 - 유저를 생성하면 기본적으로 CREATE SESSION, CREATE TABLE, CREATE PROCEDURE 등 많은 권한을 부여

 - 데이터베이스에서 유저들과 권한들 사이에서 중개 역할을 하는 ROLE을 제공

 - 데이터베이스 관리자는 ROLE을 생성하고, ROLE에 각종 권한들을 부여한 후 ROLE을 다른 ROLE이나 유저에게 부여

 - ROLE에 포함되어 있는 권한들이 필요한 유저에게 해당 ROLE만을 부여함으로써 빠르고 정확하게 필요한 권한을 부여

 왼쪽 그림은 권한을 직접 유저에게 할당할 때

오른쪽 그림은 ROLE에 권한을 부여한 후 ROLE을 유저들에게 부여하는 것

  [예제] JISUNG 유저에게 CREATE SESSION과 CREATE TABLE 권한을 가진 ROLE을 생성한 후

          ROLE을 이용하여 다시 권한을 할당한다. 권한을 취소할 때는 REVOKE를 사용한다.

Oracle CONN SYSTEM/MANAGER 연결되었다. 
REVOKE CREATE SESSION, 
       CREATE TABLE FROM JISUNG; 권한이 취소되었다. 
CONN JISUNG/KOREA7 ERROR: 사용자 JISUNG은 CREATE SESSION 권한을 가지고 있지 않음. 
로그온이 거절되었다.

SQL Server sa로 로그인한다. 
REVOKE CREATE TABLE FROM PJS; 권한이 취소되었다. PJS로 로그인한다. 
CREATE TABLE MENU ( MENU_SEQ INT NOT NULL, TITLE VARCHAR(10) ); 
데이터베이스 ‘AdventureWorks'에서 CREATE TABLE사용 권한이 거부되었다.

  [예제] 이제 LOGIN_TABLE이라는 ROLE을 만들고, 이 ROLE을 이용하여 JISUNG 유저에게 권한을 부여한다.

Oracle CONN SYSTEM/MANAGER 연결되었다. 
CREATE ROLE LOGIN_TABLE; 롤이 생성되었다. 
GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE; 권한이 부여되었다. 
GRANT LOGIN_TABLE TO JISUNG; 권한이 부여되었다. CONN JISUNG/KOREA7 연결되었다. 
CREATE TABLE MENU2( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10)); 테이블이 생성되었다.

 - 유저를 삭제하는 명령어는 DROP USER이고,

   CASCADE 옵션을 주면 해당 유저가 생성한 오브젝트를 먼저 삭제한 후 유저를 삭제한다.

    [예제] 앞에서 MENU라는 테이블을 생성했기 때문에 CASCADE 옵션을 사용하여

             JISUNG 유저를 삭제한 후, 유저 재생성 및 기본적인 ROLE을 부여한다.

Oracle CONN SYSTEM/MANAGER 연결되었다. 
DROP USER JISUNG CASCADE; 사용자가 삭제되었다. ☞ JISUNG 유저가 만든 MENU 테이블도 같이 삭제되었다.
CREATE USER JISUNG IDENTIFIED BY KOREA7; 사용자가 생성되었다. 
GRANT CONNECT, RESOURCE TO JISUNG; 권한이 부여되었다. 
CONN JISUNG/KOREA7 연결되었다. 
CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10)); 테이블이 생성되었다.

 - 인스턴스 수준을 요구하는 로그인에는 서버 수준 역할을,

   데이터베이스 수준을 요구하는 사용자에게는 데이터베이스 수준 역할을 부여