SQL - 권한과 ROLE

 

 

 

 

 

사용자 관리

 

• 사용자의 계정과 암호설정, 권한 부여
• 객체(테이블, 뷰 등)에 대한 권한 부여
• 다수의 사용자가 공유하는 데이터베이스 정보에 대한 보안 설정
• 데이터베이스에 접근하는 사용자마다 서로 다른 권한과 ROLE을 부여함

 

 

 

 

시스템 권한

 

• 데이터베이스 관리자가 가지고 있는 권한
• 오라클 접속, 테이블, 뷰, 인덱스 등의 생성 권한
  - CREATE USER(사용자 계정 만들기)
  - DROP USER(사용자 계정 삭제)
  - DROP ANY TABLE(임의의 테이블 삭제)
  - QUERY REWRITE(함수 기반 인덱스 생성 권한)
  - BACKUP ANY TABLE(테이블 백업)
• 시스템 관리자가 사용자에게 부여하는 권한
  - CREATE SESSION(데이터베이스에 접속)
  - CREATE TABLE(테이블 생성)
  - CREATE VIEW(뷰 생성)
  - CREATE SEQUENCE(시퀀스 생성)
• 사용자가 특정 객체를 조작하거나 접근할 수 있는 객체 권한
  - DML(SELECT/INSERT/UPDATE/DELETE)
  - GRANT 권한종류 [(컬럼명)] | ALL
  - ON 객체명 | ROLE 이름 | PUBLIC
  - TO 사용자 이름

 

시스템 권한 예시

 

-- <SYSTEM 계정으로 실행>
CREATE USER C##SAMPLE IDENTIFIED BY SAMPLE;
  
-- 생성한 SAMPLE 계정으로 접속 시도 시 접속 권한(CREATE SESSION)이 없어서 접속 불가
GRANT CREATE SESSION TO C##SAMPLE;

-- C##SAMPLE 계정으로 테이블 생성 구문 실행
-- 테이블 생성 권한(CREATE TABLE)이 없어서 생성 불가
GRANT CREATE TABLE TO C##SAMPLE;

CREATE TABLE TEST_TABLE(
  COL1 VARCHAR2(20),
  COL2 NUMBER
);  

-- WITH ADMIN OPTION
-- : 사용자에게 시스템 권한을 부여할 때 사용함
--   권한을 부여받은 사용자는 다른 사용자에게 권한을 지정할 수 있음
GRANT CREATE SESSION TO C##SAMPLE
WITH ADMIN OPTION;

-- C##SAMPLE2 계정 생성하기 (SYSTEM 계정으로 실행)
CREATE USER C##SAMPLE2 IDENTIFIED BY SAMPLE2;

-- C##SAMPLE 계정으로 C##SAMPLE2 계정에 접속 권한 부여하기
GRANT CREATE SESSION TO C##SAMPLE2;

-- 2. 객체 권한 : 사용자가 특정 객체(테이블, 뷰, 시퀀스, 함수)를 조작하거나 접근할 수 있는 권한
--               DML(SELECT/INSERT/UPDATE/DELETE)
--               GRANT 권한종류 [(컬럼명)] | ALL
--               ON 객체명 | ROLE 이름 | PUBLIC
--               TO 사용자 이름

-- WITH GRANT OPTION
-- : 사용자가 특정 객체를 조작하거나 접근 할 수 있는 권한을 부여받으면서
--   그 권한을 다른 사용자에게 다시 부여할 수 있는 권한 옵션
GRANT SELECT ON C##EMPLOYEE.EMPLOYEE TO C##SAMPLE
WITH GRANT OPTION;

-- C##SAMPLE 계정에서 C##EMPLOYEE.EMPLOYEE 조회
SELECT
       EE.*
  FROM C##EMPLOYEE.EMPLOYEE EE;

-- C##SAMPLE 계정이 C##SAMPLE2 계정에게 권한 부여 가능
GRANT SELECT ON C##EMPLOYEE.EMPLOYEE TO C##SAMPLE2;

-- 권한 철회(REVOKE)
REVOKE SELECT ON C##EMPLOYEE.EMPLOYEE FROM C##SAMPLE;

-- 참고
-- WITH GRANT OPTION은 REVOKE시 다른 사용자에게도 부여한 권한을 같이 회수
-- WITH ADMIN OPTION은 특정 사용자의 권한만 회수가 되고 나머지 다른 사용자에게
-- 부여한 권한은 회수가 되지 않음

-- 데이터베이스 ROLE - 권한 제어
-- : 사용자마다 일일히 권한을 부여하는 것은 번거롭기 때문에
--   간편하게 권한을 부여할 수 있는 방법으로 ROLE을 제공한다.

-- 롤(ROLE)
-- : 사용자에게 보다 간편하게 부여할 수 있도록 여러 개의 권한을 묶어놓는 것
--   사용자에게 부여한 권한을 수정하고자 할 때도 롤만 수정하면
--   그 롤에 대한 권한을 부여 받은 사용자들의 권한이 자동으로 수정된다.
--   롤을 활성화 하거나 비활성화 해서 일시적으로 권한을 부여하고 철회할 수 있다.

SELECT
       GRANTEE
     , PRIVILEGE
  FROM DBA_SYS_PRIVS
-- WHERE GRANTEE = 'RESOURCE';
 WHERE GRANTEE = 'CONNECT';
 
-- 롤의 종류
-- 1. 사전 정의된 롤 : 오라클 설치 시 시스템에서 기본적으로 제공 됨
-- EX) CONNECT ROLE, RESOURCE ROLE

-- 2. 사용자가 정의하는 롤
-- : CREATE ROLE 명령으로 롤 생성
--   롤 생성은 반드시 DBA 권한이 있는 사용자만 할 수 있음
--   CREATE ROLE 롤이름; -- 1. 롤 생성
--   GRANT 권한종류 TO 롤이름; -- 2. 생성 된 롤에 권한 추가
--   GRANT 롤이름 TO 사용자이름; -- 3. 사용자에게 롤 부여

CREATE ROLE C##MYROLE;
GRANT CREATE VIEW, CREATE SEQUENCE TO C##MYROLE;
GRANT C##MYROLE TO C##SAMPLE;

-- MYROLE 권한 확인
SELECT
       GRANTEE
     , PRIVILEGE
  FROM DBA_SYS_PRIVS
 WHERE GRANTEE = 'C##MYROLE';
 
-- SAMPLE 계정의 ROLE 확인
SELECT
       DRP.*
  FROM DBA_ROLE_PRIVS DRP
 WHERE GRANTEE = 'C##SAMPLE';
 
-- <SAMPLE 계정으로 확인하기>
CREATE SEQUENCE SEQ_TEST;
DROP SEQUENCE SEQ_TEST;

CREATE OR REPLACE FORCE VIEW VIEW_TEST
AS
SELECT 
       E.A
  FROM EEE E;