[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #7

<목차>

• 유저와 권한

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

  • OBJECT에 대한 권한 부여

• ROLE을 이용한 권한 부여

---

<< DCL (Data Control Language) >>

* 유저를 생성하고 권한을 제어할 수 있는 명령어.

---

 

1. 유저와 권한

[문제]

 다른 부서 간에 또는 다른 회사 간 데이터를 공유하기 위해 DB를 오픈해야 하는 경우가 있는데 데이터 손실 우려가 커지기 때문에 문제가 된다.

 

[해결]

 새로운 유저를 생성하고, 생성한 유저에 공유할 테이블이나 기타 오브젝트에 대한 접근 권한만 부여한다면 데이터가 수정되거나 손실될 문제를 방지할 수 있다.

 

* Oracle 설치시 기본적으로 제공되는 유저인 SYS, SYSTEM, SCOTT 유저에 대해 알아본다.

Oracle에서 제공하는 유저

 

[Oracle VS SQL Server]

1. Oracle

a. 접속 방식

• 유저를 통해 DB에 접속하는 형태
• ID 및 PW 방식으로 'INSTANCE' 에 접속하고, 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여 받는다.

 

2. SQL Server

a. 접속 방식

• 인스턴스에 접속하기 위해 'LOGIN' 을 생성하며, 인스턴스 내에 존재하는 다수의 DB에 연결하여 작업하기 위해 유저를 생성한 후 'LOGIN' 과 'USER' 를 매핑해야 한다.

• 유저는 특정 DB 내의 특정 스키마에 대한 권한을 부여 받는다.

• 두 가지 방식의 로그인
  ---> Windows 인증 방식
  ---> 혼합 모드 방식

 

2-1. Windows 인증 방식

• Windows에 로그인한 정보를 가지고 SQL Server에 접속하는 방식

• SQL Server 가 Windows에 제공하는 자격 증명을 신뢰하기 때문에 Windows 인증을 사용한 연결을 트러스트된 연결이라고 한다.

 

2-2. 혼합 모드 방식 (Windows인증 또는 SQL 인증)

• Windows 인증으로도 SQL Server에 접속 가능하며, Oracle 인증과 같은 방식으로 사용자 ID 및 PW로 SQL Server에 접속하는 방식.

 

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

* 유저를 생성하고 DB에 접속했다고 해서 테이블, 뷰, 인덱스 등과 같은 오브젝트를 생성할 수는 없다.

* 사용자가 실행하는 모든 DDL 문장(CREATE, ALTER, DROP, RENAME 등)은 그에 해당하는 권한이 있어야만 실행할 수 있다.

* 권한 = 시스템 권한

* 시스템 권한을 일일이 사용자에게 설정하기엔 너무 복잡하므로 ROLE을 이용하여 간편하고 쉽게 권한을 부여할 수 있다.

 

* 예시 : PJS유저 (패스워드 : KOREA7)를 생성해보자.

CREATE USER PJS IDENTIFIED BY KOREA7; 
/* 1행에 오류: ERROR: 권한이 불충분하다 */

---> 현재 유저(SCOTT)는 유저를 생성할 권한을 부여받지 못했기 때문에 권한 에러가 발생했다.

---> Oracle의 DBA 권한을 가진 SYSTEM 유저로 접속하면 유저 생성 권한을 다른 유저에게 부여할 수 있다.

 

* 예시 : 현재 유저(SCOTT)에게 유저 생성 권한을 부여해보자.

GRANT CREATE USER TO SCOTT;
CREATE USER PJS IDENTIFIED BY KOREA7;
/* 사용자가 생성되었다. */

---> 바로 로그인하면 CREATE SESSION 권한이 없어 로그인이 거절된다.

 

* 예시 : 생성된 PJS 유저가 로그인할 수 있도록 CREATE SESSION 권한을 부여한다.

GRANT CREATE SESSION TO PJS;
/* 권한이 부여되었다. */

---> 생성된 PJS 유저로 로그인하여 테이블을 생성한다.

---> 아직은 로그인 권한만 부여받았기 때문에 테이블 생성 권한이 없어 테이블 생성이 불가능하다.

 

* 예시 : 생성된 PJS 유저가 테이블을 생성할 수 있도록 CREATE TABLE 권한을 부여한다.

GRANT CREATE TABLE TO PJS;
/* 권한이 부여되었다. */
 
/* PJS유저로 연결 */
 
CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10) );
/* 테이블이 생성되었다. */

 

1-2. OBJECT에 대한 권한 부여

* 특정 유저가 소유한 객체 권한에 대해 알아보자. 오브젝트 권한은 특정 오브젝트인 테이블, 뷰 등에 대한 SELECT, INSERT, DELETE, UPDATE 작업 명령어를 의미한다.

오브젝트 권한과 오브젝트와의 관계 - Oracle

오브젝트 권한과 오브젝트와의 관계 - SQL Server

* 1-1의 예시에서 PJS 유저를 통해 MENU 테이블을 생성하였다. 이 MENU 테이블에 SCOTT 유저로 조회하면 어떻게 될까?

* [Oracle] 다른 유저가 소유한 객체(MENU)에 접근하기 위해서는 객체 앞에 객체를 가진 유저의 이름을 붙여서 접근해야 한다. (PJS.MENU)

* [SQL Server] 다른 유저가 소유한 객체에 접근하기 위해서는 객체 앞에 객체가 속한 스키마의 이름을 붙여서 접근해야 한다. (dbo.MENU)

 

* 예시 : PJS의 MENU 테이블 조회

SELECT * FROM PJS.MENU; 
/* ERROR: 테이블 또는 뷰가 존재하지 않는다. */

---> SCOTT 유저는 PJS 유저에게 MENU 테이블을 SELECT 할 수 있는 권한을 받지 않았기 때문에 MENU 테이블을 조회할 수 없다. PJS 유저에게 적절한 권한을 부여받아야 한다.

 

* 예시 : PJS로 접속해서 SCOTT 유저에게 MENU 테이블 조회 권한을 부여한다.

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

---> PJS 유저가 MENU 테이블에 데이터 하나를 추가한 후 DB에 반영하고 SCOTT에게 MENU 테이블에 대한 SELECT 권한을 부여했다.

---> SCOTT은 PJS.MENU 테이블을 조회할 수 있다. 하지만 UPDATE, INSERT, DELETE의 권한은 받지 않았기 때문에 불가능하다.

 

 

2. ROLE을 이용한 권한 부여

* 위의 예시로 확인해 보았듯이 유저를 생성하면 기본적으로 CREATE SESSION, CREATE TABLE, CREATE PROCEDURE 등 많은 권한을 직접 부여해야만 한다.

 

* DB 관리자는 각 유저별로 어떤 권한이 부여되었는지를 관리해야 하지만 유저가 점점 늘어나고 자주 변경되는 상황에서는 매우 번거로운 작업이 된다.

---> 이 문제를 해결하기 위해 유저와 권한 사이에서 중개 역할을 하는 ROLE을 사용한다.

 

* DB 관리자는 ROLE을 생성하고, ROLE에 각종 권한들을 부여한 후 ROLE을 다른 ROLE이나 유저에게 부여할 수 있다.

 

* ROLE에는 시스템 권한/오브젝트 권한을 모두 부여할 수 있고, 유저에게 직접 부여될 수도 있고, 다른 ROLE에 포함되어 유저에게 부여될 수도 있다.

 

* ROLE을 통한 권한 부여와 권한을 직접 부여하는 경우의 차이를 그림으로 확인해보자.

ROLE의 개념

 

* 예시 : JISUNG 유저에게 CREATE SESSION과 CREATE TABLE 권한을 가진 ROLE을 생성한 후 ROLE을 이용하여 권한을 JISUNG 유저에게 다시 할당한다. (권한을 취소할 때는 REVOKE를 사용한다.)

CONN SYSTEM/MANAGER 
/* SYSTEM 유저에 연결. */
 
REVOKE CREATE SESSION, CREATE TABLE FROM JISUNG; 
/* 
    JISUNG 유저가 가진 CREATE SESSION, CREATE TABLE 권한 취소.
    REVOKE [권한] FROM [USER_NAME];
*/
 
CONN JISUNG/KOREA7 
/* ERROR: 사용자 JISUNG은 CREATE SESSION 권한을 가지고 있지 않음. 로그온이 거절되었다. */
/* CREATE SESSION 권한도 취소했으므로 로그인 거절됨. */
 
CONN SYSTEM/MANAGER 
/* SYSTEM 유저에 연결. */
 
CREATE ROLE LOGIN_TABLE; 
/* 롤이 생성되었다. */
/* 
    CREATE ROLE [ROLE_NAME];
*/
 
GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE; 
/* 권한이 부여되었다. */ 
/*
    GRANT [권한1], [권한2], TO [ROLE_NAME];
*/
 
GRANT LOGIN_TABLE TO JISUNG; 
/* 권한이 부여되었다. */
/*
    GRANT [ROLE_NAME] TO [USER_NAME];
*/
 
CONN JISUNG/KOREA7 
/* JISUNG 유저에 연결. */ 
 
CREATE TABLE MENU2( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10)); 
/* 테이블이 생성되었다. */

---> 예시와 같이 ROLE을 만들어 사용하는 것이 권한을 직접 부여하는 것보다 빠르고 안전하게 유저를 관리하는 방법.

---> Oracle에서는 기본적으로 몇 가지 ROLE을 제공한다. 그 중 가장 많이 사용하는 ROLE은 CONNECT와 RESOURCE.

CONNECT/RESOURCE ROLE에 포함된 권한 목록 - Oracle 사례

* 유저를 삭제하는 명령어는 DROP USER.

* 삭제 명령에 CASCADE 옵션을 추가하면 해당 유저가 생성한 오브젝트를 먼저 삭제한 뒤에 유저를 삭제한다.

ex. DROP USER [USER_NAME] CASCADE;

 

 

 

 

출처

www.dbguide.net/db.db?cmd=view&boardUid=148206&boardConfigUid=9&categoryUid=216&boardIdx=135&boardStep=1

데이터 전문가 지식포털 DBGuide.net