SQL 19편: 사용자, 권한, 롤 관리

사용자

데이터 베이스에 접속하여 데이터를 관리하는 계정을 사용자(USER)라고 표현한다. 

 

데이터베이스 스키마

데이터 간 관계, 데이터 구조, 제약 조건 등 데이터를 저장 및 관리하기 위해 정의한 데이터베이스 구조의 범위를 스키마를 통해 그룹 단위로 분류한다. 사용자는 데이터를 사용 및 관리하기 위해 오라클 데이터베이스에 접속하는 개체를 의미하고, 스키마는 오라클 데이터베이스에 접속한 사용자와 연결된 객체를 의미한다. 예를 들어 사용자 A가 생성한 테이블, 뷰, 제약 조건, 인덱스 등의 사용자 A가 만든 모든 객체는 사용자 A의 스키마가 된다. 

 

 

사용자 생성

CREATE USER 사용자 이름

IDENTIFIED BY 패스워드

DEFAULT TABLESPACE 테이블 스페이스 이름

TEMPORARY TABLESPACE 테이블 스페이스(그룹) 이름

QUOTA 테이블 스페이스 크기 ON 테이블 스페이스 이름

PROFILE 프로파일 이름

PASSWORD EXPIRE 

ACCOUNT [LOCK/UNLOCK];

 

사용자 생성 후에는 권한 설정을 해주어야 한다. 생성 직후에는 CREATE SESSION 권한이 없기 때문에 데이터베이스에 연결 조차 불가능하다.

 

 

데이터베이스에 연결하기 

CONN 사용자이름/비밀번호

 

 

사용자 정보 변경하기(패스워드)

ALTER USER 사용자이름

IDENTIFIED BY 바꿀 비밀번호;

 

 

사용자 삭제

DROP USER 사용자이름;

 

 

사용자와 스키마의 객체 모두 삭제

DROP USER 사용자이름 CASCADE;

 

 

 

 

---

권한의 종류

1. 시스템 권한

- 사용자 생성과 정보 수정 및 삭제, DB 접근, 오라클 DB의 여러 자원과 객체 생성 및 관리 등의 권한을 포함

- DB관리 권한이 있는 사용자가 부여할 수 있는 권한

- ANY 키워드가 들어 있는 권한은 소유자와 상관없이 사용 가능한 권한을 의미

분류	시스템 권한	설명
USER(사용자)	CREATE USER	사용자 생성 권한
	ALTER USER	생성된 사용자의 정보 수정 권한
	DROP USER	생성된 사용자의 삭제 권한
SESSION(접속)	CREATE SESSION	DB 접속 권한
	ALTER SESSION	DB 접속 상태에서 환경 값 변경 권한
TABLE(테이블)	CREATE TABLE	자신의 테이블 생성 권한
	CREATE ANY TABLE	임의의 스키마 소유 테이블 생성 권한
	ALTER ANY TABLE	임의의 스키마 소유 테이블 수정 권한
	DROP ANY TABLE	임의의 스키마 소유 테이블 삭제 권한
	INSERT ANY TABLE	임의의 스키마 소유 테이블 데이터 삽입 권한
	UPDATE ANY TABLE	임의의 스키마 소유 테이블 데이터 수정 권한
	DELETE ANY TABLE	임의의 스키마 소유 테이블 데이터 삭제 권한
	SELECT ANY TABLE	임의의 스키마 소유 테이블 데이터 조회 권한
INDEX(인덱스)	CREATE ANY INDEX	임의의 스키마 소유 테이블 인덱스 생성 권한
	ALTER ANY INDEX	임의의 스키마 소유 테이블 인덱스 수정 권한
	DROP ANY INDEX	임의의 스키마 소유 테이블 인덱스 삭제 권한
VIEW(뷰)	...	뷰와 관련된 권한
SEQUENCE(시퀀스)	...	시퀀스와 관련된 권한
SYNONYM(동의어)	...	동의어와 관련된 권한
PROFILE(프로파일)	...	프로파일과 관련된 권한
ROLE(롤)	...	롤과 관련된 권한

 

 

시스템 권한 부여하기

GRANT 시스템권한 TO [사용자이름/롤이름/PUBLIC]

WITH ADMIN OPTION;

 

- 여러 종류의 권한을 부여하려면 쉼표로 구분하여 권한 이름을 여러 개 명시해주면 된다.

- PUBLIC은 모든 사용자에게 권한을 부여하겠다는 의미이다.

- WITH ADMIN OPTION은 현재 GRANT문을 통해 부여받은 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여한다.

 

 

시스템 권한 취소하기

REVOKE [시스템 권한] FROM [사용자이름/롤이름/PUBLIC]

 

 

 

 

2. 객체 권한

특정 사용자가 생성한 테이블, 인덱스, 뷰, 시퀀스 등과 관련된 권한. 시스템 권한과는 달리 '특정 객체에게만' 적용되는 권한이다. 그래서 객체 권한을 부여할 때 특정 객체 이름을 명시하여 권한을 부여한다.

TABLE(테이블)	ALTER	변경 권한
	DELETE	삭제 권한
	INDEX	생성 권한
	INSERT	삽입 권한
	REFERENCES	참조 데이터 생성 권한
	SELECT	조회 권한
	UPDATE	데이터 수정 권한
VIEW(뷰)	DELETE	뷰 데이터 삭제 권한
	INSERT	데이터 삽입 권한
	REFERENCES	참조 데이터 생성 권한
	SELECT	조회 권한
	UPDATE	데이터 수정 권한
SEQUENCE(시퀀스)	ALTER	수정 권한
	SELECT	CURRVAL, NEXTVAL 사용 권한
PROCEDURE(프로시저)	...	프로시저 관련 권한
FUNCTION(함수)	...	함수 관련 권한
PACKAGE(패키지)	...	패키지 관련 권한

 

 

객체 권한 부여하기

GRANT [객체 권한/ALL PRIVILEGES]

ON [스키마.객체이름]

TO [사용자 이름/롤이름/PUBLIC]

[WITH GRANT OPTION];

 

 

객체 권한 취소하기

GRANT [객체 권한/ALL PRIVILEGES]

ON [스키마.객체이름]

TO [사용자이름/롤이름/PUBLIC]

[CASCADE CONSTRAINTS/FORCE];

 

 

 

---

롤 관리

롤은 여러 종류의 권한을 묶어 놓은 그룹을 뜻한다. 여러 권한을 한 번에 부여하고 해제할 수 있게 함으로써 권한 관리 효율을 높일 수 있다. 

 

 

1. 사전 정의된 롤

새로운 사용자를 생성하면 보통 CONNECT 롤과 RESOURCE 롤을 부여하는 경우가 많다.

 

1) CONNECT 롤

- CREATE SESSION 권한(사용자가 접속 하는데 필요한 권한)

- ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW 는 10G 버전부터 제외

 

2) RESOURCE 롤

여러 객체를 생성할 수 있는 기본 시스템 권한을 묶어 놓은 롤

CREATE TRIGGER, CREATE SEQUENCE, CREATE TYPE, CREATE PROCEDURE, CREATE CLUSTER, CREATE OPERATOR, CREATE INDEXTYPE, CREATE TABLE

 

3) DBA롤

데이터 베이스를 관리하는 시스템 권한을 대부분 가지고 있는 롤

 

 

 

 

2. 사용자 정의 롤

 

롤 생성 및 권한 부여

CREATE ROLE 롤이름;

GRANT 권한1, 권한2, 권한3 ... TO 롤이름;

 

롤 취소

REVOKE 롤이름 FROM 사용자이름

 

롤 삭제

DROP ROLE 롤이름

 

롤과 권한 확인

USER_SYS_PRIVIS : 권한 조회 가능

USER_ROLE_RRIVIS : 롤 조회 가능