18_데이터베이스 롤 권한 제어

[ 데이터베이스 롤 권한 제어 목차 ]

1. 롤의 정의와 종류

   1) 롤의 정의

   2) 사전에 정의된 롤의 종류

      - CONNECT 롤

      - RESOURCE 롤

      - DBA 롤

   3) 롤 관련 데이터 딕셔너리

2. 사용자 롤 정의

3. 롤 회수하기

4. 롤 장점

 

 

 

 

[ 1 ] 롤의 정의와 종류

 

1. 롤의 정의

- 롤

  : 사용자에게 보다 효율적으로 권한을 부여할 수 있도록

    여러개의 권한을 묶어 놓은 것

 

- 사용자를 생성했으면 그 사용자에게 각종 권한을 부여해야만

  생성된 사용자가 데이터베이스를 사용할 수 있다.

 

- 데이터베이스의 접속 권한 (CREATE SESSION),

  테이블 생성 권한 (CREATE TABLE),

  데이블 수정 (UPDATE), 삭제 (DELETE), 조회 (SELECT) 등과 같은

  권한은 사용자에게 기본적으로 필요한 권한들을 사용자 생성할 때마다

  부여해줘야한다.

  ---> 매번 사용자 생성하고 기본적으로 필요한 권한들을 부여해줘야

         하는 것이 너무 번거롭다!! 

 

- 그래서 다수의 사용자에게 공통적으로 필요한 권한들을 롤에 하나의

  그룹으로 묶어두고 사용자에게는 특정 롤에 대한 권한을 부여함으로서

  간단하게 권한 부여를 할 수 있게 한다.

 

- 또한 여러 사용자에게 부여된 권한을 수정하고 싶을 때에도 일일이

  사용자마다 권한을 수정하지 않고 롤만 수정하면 그 롤에 대한

  권한이 부여된 사용자들의 권한이 자동으로 수정된다.

  이 밖에도 롤을 활성화 비활성화 함으로서 일시적으로 권한을 부여했다

  철회했다 할 수 있으므로 사용자 관리를 간편하고 효율적으로 사용 할 수 있다.

 

 

 

2. 사전에 정의된 롤의 종류

- 롤은 오라클 데이터베이스를 설치하면 기본적으로 제공되는 사전 정의된 롤과

  사용자가 정의한 롤로 구분된다.

  사전 정의된 시스템에서 제공해주는 롤은 다음과 같다.

 

1) CONNECT 롤

    - 사용자가 데이터베이스에 접속 가능하도록 하기 위해서 다음과 같이

      가장 기본적인 시스템 권한 8가지를 묶어 놓았다.

     --> ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK,

          CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM,

          CREATE TABLE, CREATE VIEW

* 자주 사용하는 롤 -> CONNECT 롤

 

2) RESOURCE 롤

    - 사용자가 객체 (테이블, 뷰, 인덱스)를 생성할 수 있도록 하기 위해서

      다음에 나오는 시스템 권한들을 묶어 놓았다.

     --> CREATE CLUSTER, CREATE PROCEDURE, CREATE SEQUENCE,

           CREATE TABLE, CREATE TRIGGER

 

3) DBA 롤

    - 사용자들이 소유한 데이터베이스객체를 관리하고 사용자들을 작성하고

      변경하고 제거할 수 있도록 하는 모든 권한을 가진다.

      즉, 시스템 자원을 무제한적으로 사용하며 시스템 관리에 필요한 모든 권한을

      부여할 수 있는 강력한 권한을 보유한 롤이다.

* DBA 롤 권한이 있는 계정 : SYSTEM 계정

 

 

 

[ 실습 : 롤 부여하기 ]

- 일반적으로 데이터베이스 관리자는 새로운 사용자를 생성할 때

  CONNECT 롤과 RESOURCE 롤을 부여한다.

  USER04 사용자를 생성하여 CONNECT 롤과 RESOURCE 롤을

  부여해보자.

 

CONN SYSTEM/MANAGER
DROP USER USER04 CASCADE;

CREATE USER USER04 IDENTIFIED BY TIGER;
CONN USER04/TIGER;

CONN SYSTEM/MANAGER
GRANT CONNECT, RESOURCE TO USER04;
CONN USER04/TIGER;

-- 아래 쉅 내용 코드 --
-- SYSTEM 계정에서,,
-- 사용자(USER04/PWD) 생성하기
CREATE USER USER04 IDENTIFIED BY PWD;

--USER04에게 ROLE 부여
GRANT CONNECT, RESOURCE TO USER04;

 

 

[ 롤 부여받기 전 ]

 

 

 

[ 롤 부여받은 후 ]

 

[ 위에서 CONNECT 롤을 부여받았는데, CONNECT 롤에는 CREATE TABLE 권한이 들어있기 때문에 위에서처럼 TABLE을 만들 수 있게 된것이다! ]

 

 

 

 

3. 롤 관련 데이터 딕셔너리

- 데이터 딕셔너리를 통해서 부여된 권한에 대한 정보를

  확인할 수 있다.

 

SELECT * FROM DICT WHERE TABLE_NAME LIKE '%ROLE%';

CONN USER04/TIGER
SELECT * FROM USER_ROLE_PRIVS;

 

딕셔너리 명	설명
ORLE_SYS_PRIVS	롤에 부여된 시스템 권한 정보
ROLE_TAB_PRIVS	롤에 부여된 테이블 관련 권한 정보
USER_ROLE_PRIVS	접근 가능한 롤 정보
USER_TAB_PRIVS_MADE	해당 사용자 소유의 오브젝트 권한 정보
USER_TAB_PRIVS_RECD	사용자에게 부여된 오브젝트 권한 정보
USER_COL_PRIVS_MADE	사용자 소유의 오브젝트 중 칼럼에 부여된 오브젝트 권한 정보
USER_COL_PRIVS_REDC	사용자에게 부여된 특정 칼럼에 대한 오브젝트 권한 정보

 

 

[ 위에서 롤 부여받은 USER04 사용자가 어떤 롤을 부여 받았는지 확인한 결과 ]

* USER_ROLE_PRIVS 로 USER04 사용자가 접근 가능한 롤 정보를 확인해보았다.

 

---

 

[ 2 ] 사용자 롤 정의

- 사용자는 CREATE ROLE 명령어로 다음 형식에 따라

  롤을 생성해야 한다.

 

-- 형식
CREATE ROLE ROLE_NAME;
GRANT PRIVILEGE_NAME TO ROLE_NAME;

 

 

 

[ 실습 : 롤 생성하여 시스템 권한 할당하기 ]

 

CONN SYSTEM/MANAGER
CREATE ROLE MROLE;

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO MROLE; 

DROP USER USER05 CASCADE;
CREATE USER USER05 IDENTIFIED BY TIGER;
GRANT MROLE TO USER05;

CONN USER05/TIGER
SELECT * FROM USER_ROLE_PRIVS;

-- 아래 쉅 내용 코드 --
-- 사용자 롤 정의하기
CREATE ROLE MROLE; -- 롤 생성
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO MROLE;
-- MROLE은 GRANT 받음으로서 3개의 시스템 권한을 가지게 된다.

CREATE USER USER05 IDENTIFIED BY PWD; -- USER05 사용자 생성
GRANT MROLE TO USER05; -- 3개의 시스템권한이 들어있는 MROLE 롤을 한번만 부여함.
REVOKE MROLE FROM USER05;

 

[ 3개의 시스템권한 CREATE SESSION, CREATE TABLE, CREATE VIEW 를 부여받은 MROLE 롤을 사용자 USER05에게 부여한 결과 ]

 

 

 

---

 

[ 3 ] 롤 회수하기

- 롤을 회수하기 위해 DROP ROLE 명령어를 사용한다.

 

-- 형식
REVOKE ROLE_NAME FROM USER_NAME;

 

 

[ 사용자 USER05에게 롤 회수한 후 다시 접속하려고 하니 안된다 ]

 

 

---

 

[ 4 ] 롤의 장점 (그냥 한번 읽고,,, 끝남,,,)

- 시스템 권한이나 객체 권한을 아래의 사진과 같이

  사용자마다 일일이 부여하게 되면 번거롭다.

 

[ 사용자 생성되고 나면 매번 권한 부여해줘야 하는 번거로움 발생 ]

 

 

- 이러한 단점을 롤로 보완할 수 있다. 다음 사진과 같이

  우선 롤에 시스템 권한과 객체 권한을 부여한다.

 

[ 롤에 시스템 권한과 객체 권한 부여하기 ]

 

 

- 그 후에 다음 사진과 같이

  롤을 사용자에 대해 권한 부여함으로서

  작업을 간소화 할 수 있다.

 

[ 사용자에게 롤 권한 부여하기 ]

 

 

 

 

 

[ 실습 : 디폴트 롤을 생성하여 여러 사용자에게 부여하기 ] (SKIP!!)

- 롤에 시스템 권한과 객체 권한을 부여한 후에

  사용자에게 롤에 대한 권한을 부여하여 작업을 간소화 해보자.

 

CONN SYSTEM/MANAGER
CREATE ROLE DEF_ROLE;

GRANT CREATE SESSION TO DEF_ROLE;
GRANT CREATE TABLE TO DEF_ROLE;

CONN SCOTT/TIGER
GRANT UPDATE ON EMP TO DEF_ROLE;
GRANT DELETE ON EMP TO DEF_ROLE;
GRANT SELECT ON EMP TO DEF_ROLE;

CONN SYSTEM/MANAGER
CREATE USER USERA1 IDENTIFIED BY A1234;
CREATE USER USERA2 IDENTIFIED BY A1234;
CREATE USER USERA3 IDENTIFIED BY A1234;

SHOW USER
GRANT DEF_ROLE TO USERA1;
GRANT DEF_ROLE TO USERA2;
GRANT DEF_ROLE TO USERA3;

SHOW USER
SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='DEF_ROLE';
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE='DEF_ROLE';