[DB] 시스템 권한 (System Privileges), 롤 (Role)

박세진·2021년 3월 15일
0

DataBase

목록 보기
2/4
post-thumbnail

✅ 권한
Oracle 에서 권한은 특정 타입의 SQL 문을 실행하거나 데이터베이스 or 객체에 접근할 수 있는 권리이다.

시스템 권한

  • 사용자가 데이터베이스에서 특정 작업을 수행할 수 있도록 함.
  • 권한의 ANY 키워드 : 사용자가 모든 스키마에서 권한을 가짐을 의미
  • GRANT : 사용자 or ROLE 에 대해서 권한을 부여할 수 있음.
  • REVOKE : 권한을 회수

대표적인 시스템 권한

  • CREATE SESSION : 데이터베이스를 연결할 수 있는 권한. 이게 있어야 해당 계정으로 로그인 할 수 있음
  • CREATE ROLE : 오라클 데이터베이스 역할을 생성할 수 있는 권한
  • CREATE VIEW : 뷰 생성
  • ALTER USER : 생성한 사용자의 정의를 변경할 수 있는 권한
  • DROP USER : 생성한 사용자를 삭제시키는 권한

시스템 권한 부여하기

GRANT [system_priviege | role] TO [user | role | PUBLIC]
[WITH ADMIN OPTION]
  • system_privilege : 부여할 시스템권한의 이름
  • role : 부여할 데이터베이스 역할의 이름
  • user, role : 부여할 사용자 이름과 다른 데이터베이스 역할의 이름
  • PUBLIC : 시스템권한 or 데이터베이스 역할을 모든 사용자에게 부여할 수 있음.
  • WITH ADMIN OPTION : 권한을 부여받은 사용자도 부여 받은 권한을 다른 사용자 / 역할로 부여할 수 있게 된다.

예제

-- SYS 권한으로 접속.
SQL> CONN sys/manager AS SYSDBA

-- scott 유저에게도 사용자 생성/수정/삭제 할 수 있는 권한 부여하고
-- scott 사용자도 다른 사용자에게 그 권한을 부여 할 수 있도록 권한 부여.
SQL> GRANT CREATE USER, ALTER USER, DROP USER TO scott
WITH ADMIN OPTION;

시스템 권한 회수

GRANT [system_privilege | role]FROM [user | role | PUBLIC]

conn 명령어는 뭐야 ?

connect 또는 conn 으로 사용. 뒤에오는건 아이디/패스워드
as sysdba : 관리자 권한으로 접속하는 것.
즉, sys 계정에 관리자 권한으로 접속하기 위한 명령어 라고 할 수 있음.

참고 !
sys 계정은 신, system은 매니저 라고 표현할 수 있음.
system은 설정되어있는 하나의 관리자 일 뿐임 !
사용자 계정을 추가하거나 database 를 만들 때는 sys계정으로 접속해야 함.

예제

-- scott 사용자에게 부여한 생성/수정/삭제 권한 회수하기.
GRANT CREATE USER, ALTER USER, DROP USER
FROM scott;

WITH ADMIN OPTION을 사용하여 시스템 권한 취소하기

WITH ADMIN OPTION을 사용하여 시스템 권한을 부여했더라도, 시스템 권한을 취소할 때에는 연쇄적으로 취소 되지 않는다.


롤(Role)

  • 사용자에게 허가할 수 있는 권한들의 집합
  • 롤을 이용하면 권한 부여와 회수를 쉽게 할 수 있다.
  • 롤은 CREATE ROLE 권한을 가진 USER 에 의해서 생성된다.
  • 한 사용자가 여러개의 ROLE을 ACCESS 할 수 있고, 여러 사용자에게 같은 ROLE 을 부여할 수 있다.
  • 시스템 권한을 부여 & 취소할 때와 동일한 명령을 사용하여 사용자에게 부여하고 취소한다.
  • 사용자는 ROLE에 ROLE을 부여할 수 있다.
  • Oracle DB를 설치하면 기본적으로 CONNECT, RESOURCE, DBA ROLE이 제공된다.

cf) 아래의 그림처럼 DBA 가 유저들에게 권한을 부여할 때 일일이 권한 하나하나씩을 지정한다면 몹시 불편할 것임 !
=> DBA 가 USER 의 역할에 맞도록 ROLE 을 생성해서 해당 ROLE만 유저에게 지정해준다면 보다 효율적으로 유저들의 권한을 관리할 수 있다.

ROLE 생성

CREATE ROLE role_name

예제

  • ROLE의 부여 순서
-- 1. ROLE 생성하기
SQL> CREATE ROLE manager;

-- 2. ROLE에 권한 부여하기
SQL> GRANT CREATE SESSION, CREATE TABLE TO manager;

-- 3. 권한이 부여된 ROLE을 USER 나 ROLE 에 부여하기
SQL> GRANT manager TO scott, test; // 여기서는 scott, test 라는 유저에 부여함.

Oracle DB 를 설치하면 기본적으로 생성되는 ROLE

DBA_ROLES 데이터 사전을 통하여 미리 정의된 ROLE을 조회할 수 있다.

SQL> SELECT * FROM DBA_ROLES;
ROLE                      PASSWORD
----------------------    -----------
CONNECT                   NO
RESOURCE                  NO
DBA                       NO
SELECT_CATALOG_ROLE       NO
EXECUTE_CATALOG_ROLE      NO
DELETE_CATALOG_ROLE       NO
EXP_FULL_DATABASE         NO
IMP_FULL_DATABASE         NO
....

이 때 나오는 ROLE 들 중 가장 많이 사용하는 세가지에 대해 알아보자.

1. CONNECT

: 오라클에 접속할 수 있는 세션 생성 및 테이블 생성/조회 를 할 수 있는 가장 일반적인 권한들로 이루어짐.

  • CONNECT ROLE 이 없다면 유저를 생성하고 나서도 Oracle 에 접속할 수 없다.
  • 아래의 명령어로 CONNECT ROLE이 어떤 권한으로 이루어져 있는 지 확인할 수 있다.
SQL>SELECT grantee, privilege
    FROM DBA_SYS_PRIVS
    WHERE grantee = 'CONNECT';

GRANTEE     PRIVILEGE
----------- ---------------------
CONNECT     ALTER SESSION
CONNECT     CREATE CLUSTER
CONNECT     CREATE DATABASE LINK
CONNECT     CREATE SEQUENCE
CONNECT     CREATE SESSION
...

2. RESOURCE ROLE

: Store Procedure 나 Trigger를 사용할 수 있는 권한들로 이루어져 있음.

  • PL/SQL사용시 RESOURCE ROLE 부여해주어야 함 !
  • 유저를 생성하면 일반적으로 CONNECT, RESOURCE ROLE을 부여한다.

3. DBA ROLE

: 모든 시스템 권한이 부여된 ROLE.

  • DBA ROLE은 데이터베이스 관리자에게만 부여해야 한다.
profile
계속해서 기록하는 개발자. 조금씩 성장하기!

0개의 댓글