[SQL] 사용자 권한 제어

OS는 사용자를 추가해주면 그 사용자를 즉시 사용 가능하다.

OS → 사용자추가 → 사용가능

하지만 오라클은 사용자를 추가해도 바로 그 사용자를 쓸 수 없다. 적절한 권한을 주어야 한다.

오라클 → 사용자추가 → 권한 할당 → 사용가능

그렇다면 권한 할당은 누가 해주는것일까? 바로 DBA의 권한을 가진 사용자(보통 DBA)가 해주는 일이다.

---

권한이란 사용자가 데이터베이스 시스템을 관리하거나 객체를 이용할 수 있는 권리를 의미함.

권한은 크게 2가지로 나뉜다.
  - 시스템 권한 : 데이터베이스 관리자나 일반 사용자에 의해 데이터베이스를 관리하는 권한.
                        사용자 생성, 삭제, 객체의 생성 및 수정, 데이터베이스 백업 등이 있음.
    ┌ DBA의 시스템권한
    └ 일반사용자의 시스템권한
  - 오브젝트 권한

권한을 할당하려면 DBA권한을 가진 사용자로 접속해야 한다.


◆ DBA의 시스템 권한

시스템 권한	기능
CREATE USER	사용자를 생성할 수 있는 권한
DROP USER	사용자를 삭제할 수 있는 권한
DROP ANY TABLE	모든 테이블을 삭제할 수 있는 권한
QUERY REWRITE	함수기반인덱스를 생성하기 위한 권한
BACKUP ANY TABLE	Export 유틸리티를 사용해서 임의의 테이블을 백업할 수 있는 권한

◆ 일반 사용자의 시스템 권한

시스템 권한	기능
CREATE SESSION	데이터베이스에 접속할 수 있는 권한
CREATE TABLE	테이블을 생성할 수 있는 권한
CREATE SEQUENCE	시퀀스를 생성할 수 있는 권한
CREATE VIEW	뷰를 생성할 수 있는 권한
CREATE PROCEDURE	프로시저, 함수, 패키지를 생성할 수 있는 권한

◆ 권한 할당

SQL> grant 권한 to 계정명;

해당 권한을 해당 계정에게 부여한다.
계정명에 PUBLIC이 오면 모든 사용자라는 뜻이다.


◆ 권한 해제

SQL> revoke 권한 from 계정명;

해당 권한을 해당 계정으로부터 해제한다.


◆ 롤(Roll)
다수 사용자와 다양한 권한을 효과적으로 관리하기 위하여 서로 관련된 권한을 그룹화한 개념이다. 즉, 사용자별로 일일이 권한을 주기보다는 그룹에 권한을 주는 것이 훨씬 효과적이기 때문에 권한을 그룹화해서 관리하는 것이다.
롤은 쉽게 생각해서 권한들을 다발로 묶어놓은 것으로 생각하면 된다. 필요시 얼마든지 다양한 롤을 정의할 수 있다.


◆ 사전 정의된 롤

롤 종류	롤에 부여된 권한
CONNECT	ALTER SESSION  CREATE DATABASE LINK  CREATE CLUSTER  CREATE SEQUENCE  CREATE SESSION  CREATE SYNONYM  CREATE TABLE  CREATE VIEW
RESOURCE	CREATE CLUSTER  CREATE PROCEDURE  CREATE SEQUENCE  CREATE TABLE  CREATE TRIGGER
DBA	WITH ADMIN OPTION이 있는 모든 시스템 권한

● CONNECT 롤
사용자가 데이터베이스에 접속하여 세션을 생성하거나 테이블 또는 뷰와 같은 객체를 생성할 수 있는 권한을 그룹화한 롤.

● RESOURCE 롤
사용자에게 자신의 테이블, 시퀀스, 프로시저, 트리거와 같은 객체를 생성할 수 있는 권한을 부여한 롤.

● DBA 롤
시스템 자원의 무제한적인 사용이나 시스템 관리에 필요한 모든 권한 그리고 DBA권한을 다른 사용자에게 부여할 수 있는 강력한 권한을 보유한 롤이다. 또한 모든 사용자의 CONNECT, RESOURCE, DBA권한을 포함한 어떠한 권한을 부여하거나 철회할 수 있다.

◆ 롤 생성

SQL> create role 롤명 [NOT IDENTIFIED | IDENTIFIED] (BY 패스워드 | EXTERNALLY];

NOT IDENTIFIED : 롤 활성화 시 암호에 의한 검증과정 생략
IDENTIFIED : 롤 활성화 시 암호에 의한 검증과정 활성화
BY 패스워드 : 롤 활성화 시 사용되는 암호 지정
EXTERNALLY : 롤 활성화 시 운영체제 인증을 통한 사용자 검증

그러나 보통 롤을 부여했다는 것은 DBA가 고심해서 부여했을 것이므로 패스워드 과정은 생략한다.


◆ 롤에 시스템권한 부여

SQL> grant 시스템권한 to 롤명;

◆ 롤에 객체권한 부여

SQL> grant 객체권한 on 롤명;

◆ 롤을 또다른 롤에게 부여

SQL> grant 작은롤 to 큰롤;

위에서 부여한 롤들을 필요시 사용자들에게 권한 부여하듯 주면 된다.


◆ Synonym(동의어)
하나의 특정 객체에 다른 이름을 지정하는 방법.
테이블에서 컬럼의 별명과 비슷한 개념이다.


◆ Synonym 생성

SQL> create [public] synonym 별명 for 객체명;

PUBLIC : 모든 사용자가 해당 별명을 알 수 있다. 하지만 이 옵션을 안주면 객체 소유자와 별명을 만든 사용자밖엔 모른다.


◆ Synonym 삭제

SQL> drop synonym 별명;

◆ Synonym 조회 관련 데이터 딕셔너리
  - USER_SYNONYMS