[오라클로 배우는 데이터베이스 입문] 15. 사용자, 권한, 롤 관리

1. 사용자 관리

- 사용자란? 데이터베이스에 접속하여 데이터를 관리하는 계정

- 사용자 관리의 필요성: 업무 분할과 효율, 보안을 고려해 업무에 따라 여러 사용자를 나누어 사용하며 업무별 사용자를 생성한 후에 각 사용자 업무에 맞는 데이터 구조를 만들어 관리할 수 있음

- 데이터베이스 스키마란? 데이터 간 관계, 데이터 구조, 제약조건 등 데이터를 저장 및 관리하기 위해 정의한 데이터베이스 구조의 범위를 그룹단위로 분류한 객체

※ 오라클 데이터베이스에서는 스키마와 사용자를 구별하지 않고 사용하기도 함, 사용자는 데이터를 사용, 관리하기 위해 오라클 데이터베이스에 접속하는 개체를 뜻하고 스키마는 오라클 데이터베이스에 접속한 사용자와 연결된 객체를 의미

- 사용자 생성:

CREATE USER 사용자 이름(필수) IDENTIFIED BY 패스워드(필수) DEFAULT TABLESPACE 테이블 스페이스 이름(선택) TEMPORARY TABLESPACE 테이블 스페이스(그룹) 이름(선택) QUOTA 테이블 스페이스크기 ON 테이블 스페이스 이름(선택) PROFILE 프로파일 이름(선택) PASSWORD EXPIRE(선택) ACCOUNT [LOCK/UNLOCK](선택);

SCOTT계정에서는 사용자 생성 불가(권한이 없기 때문)

SYSTEM 사용자로 접속 후 사용자 생성

ORCLSTUDY 계정은 데이터베이스 연결(CREATE SESSION)권한을 받지 못했기 때문에 CONN명령어를 통한 접속 불가

SYSTEM 사용자 접속 후 ORCLSTUDY 사용자에게 접속 권한 부여한 후 ORCLSTUDY 사용자 재접속

- 사용자 정보 조회

사용자 또는 사용자 소유 객체 정보를 얻기 위해 다음과 같이 데이터 사전 사용

SELECT * FROM ALL_USERS WHERE USERNAME='ORCLSTUDY';

SELECT * FROM DBA_USERS WHERE USERNAME='ORCLSTUDY';

WHERE OWNER='ORCLSTUDY';

- 오라클 사용자의 변경과 삭제

2. 권한 관리

- 시스템 권한이란? 사용자 생성과 정보 수정 및 삭제, 데이터베이스 접근, 오라클 데이터베이스의 여러 자원과 객체 생성 및 관리 등의 권한(ANY 키워드 들어있는권한은 소유자에 상관없이 사용 가능)

권한 분류	시스템 권한	설명
USER(사용자)	CREATE USER	사용자 생성 권한
	ALTER USER	생성된 사용자의 정보 수정 권한
	DROP USER	생성된 사용자의 삭제 권한
SESSION(접속)	CREATE SESSION	데이터베이스 접속 권한
	ALTER SESSION	데이터베이스 접속 상태에서 환경 값 변경 권한
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 [사용자 이름/롤(role)DLFMA/PUBLIC] [WITH ADMIN OPTION];

[시스템권한] : 오라클 데이터베이스에서 제공하는 시스템 권한 지정, 한번에 여러 권한 부여하려면 쉼표(,)로 구분하여 여러개 명시(필수)

[사용자 이름/롤(role)이름/PUBLIC]: 권한을 부여하려는 대상 지정, 사용자 이름, 롤으로 지정할 수 있음/ 여러 사용자 또는 롤을 지정하려면 쉼표(,)로 구분, PUBLIC은 현재 오라클 데이터베이스의 모든 사용자에게 권한을 부여하겠다는 의미(필수)

[WITH ADMIN OPTION] : 현재 GRANT문을 통해 부여받은 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여 받음/ 현재 사용자가 권한이 사라져도, 권한을 재부여한 다른 사용자의 권한은 유지됨(선택)

사용자 생성 및 권한 부여

생성된 사용자의 권한 확인

- RESOURCE키워드는 오라클 데이터베이스에서 제공하는 롤(role)중 하나/ 여러 권한을 하나의 이름으로 묶어 권한 부여 관련 작업을 간편하게 하려고 사용함 - RESOURCE 롤: 사용자를 생성할 때 사용 테이블스페이스의 영역을 무제한 사용가능(UNLIMITED TABLESPACE)하게 해주는 권한이 포함되어 있음 - 단, 엄밀한 관리가 필요한 경우 적절치 않으므로 사용자 생성 및 수정할때 QUOTA절로 사용 영역을 제한하기도 한다.    EX) ALTER USER ORCLSTUDY QUOTA 2M ON USERS;

- 시스템 권한 취소

REBOKE [시스템권한] FROM [사용자 이름/롤(Role)이름/PUBLIC];

권한 취소 후 더이상 테이블을 생성할 수 없는 ORCLSTUDY사용자

- 객체 권한이란? 특정 사용자가 생성한 테이블,인덱스,뷰,시퀀스 등과 관련된 권한[ex) SCOTT 소유 테이블에 ORCLSTUDY 사용자가 SELECT, INSERT 등 작업가능하도록 허용하는 것]

객체 권한 분류	객체 권한	설명
TALBLE(테이블)	ALTER	테이블 변경 권한
	DELETE	테이블 데이터 삭제 권한
	INDEX	데이블 인덱스 생성 권한
	INSERT	테이블 데이터 삽입 권한
	REFERENCES	참조 데이터 생성 권한
	SELECT	테이블 조회 권한
VIEW(뷰)	DELETE	뷰 데이터 삭제 권한
	INSERT	뷰 데이터 삽입 권한
	REFERENCES	참조 데이터 생성 권한
	SELECT	뷰 조회 권한
	UPDATE	뷰 데이터 수정 권한
SEQUENCE(시퀀스)	ALTER	시퀀스 수정 권한
	SELECT	시퀀스의 CURRVAL과 NEXTVAL 사용 권한
PROCEDURE(프로시저)	(생략)	프로시저 관련 권한
FUNTION(함수)	(생략)	함수 관련 권한
PACKAGE(패키지)	(생략)	패키지 관련 권한
이하 생략

- 객체 권한 부여

GRANT [객체권한/ALL PRIVILEGES] ON[스키마.객체 이름] TO[사용자 이름/롤(Role)이름/PUBLIC] [WITH GRANT OPTION];

[객체권한/ALL PRIVILEGES]: 오라클 데이터베이스에서 제공하는 객체 권한 지정, 한 번에 여러 종류 권한 부여하려면 쉼표(,)로 구분하여 권한을 여러 개 명시/ ALL PRIVILEGES는 객체의 모든 권한을 부여함을 의미(필수)

[스키마.객체 이름]: 권한을 부여할 대상 객체 명시(필수)

[사용자 이름/롤(Role)이름/PUBLIC]: 권한을 부여하려는 대상 지정, 사용자 이름 지정 혹은 롤(Role)을 지정, 여러사용자 혹은 롤을 지정할 경우 쉼표(,)로 구분, PUBLIC은 모든 사용자에게 권한을 부여하겠다는 의미(필수)

[WITH GRANT OPTION]: 현재 GRANT문을 통해 부여받은 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여 받음/ 현재 사용자가 권한이 사라져도, 권한을 재부여한 다른 사용자의 권한도 사라짐(선택)

SCOTT사용자의 테이블 데이터 조회, 삽입 권한 ORCLSTUDY에게 부여

- 객체 권한 취소

REBOKE [시스템권한/ALL PRIVILEGES](필수) ON [스키마.객체 이름](필수) FROM [사용자 이름/롤(Role)이름/PUBLIC](필수) [CASCADE CONSTRAINS/FORCE](선택);

ORCLSTUDY에 부여된 TEMP테이블 권한 취소 및 취소 확인

3. 롤 관리

롤이란? 여러 종류의 권한을 묶어 놓은 그룹, 여러 권한을 한 번에 부여하고 해제할 수 있으므로 권한 관리효율을 높일 수 있음

- 사전 정의된 롤(predefined roels)

○ CONNECT 롤: CREATE SESSION 권한

○ RESOURCE 롤: 테이블, 시퀀스를 비롯한 여러 객체를 생성할 수 있는 기본 시스템 권한을 묶은 롤

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

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

- 사용자 정의 롤(user roles): 필요에 의해 직접 권한을 포함시킨 롤을 뜻함

롤 생성 절차 1) CREATE ROLE문으로 롤 생성 2) GRANT명령어로 생성한 롤에 권한 포함시킴 3) GRANT명령어로 권한이 포함된 롤을 특정 사용자에게 부여 4) REVOKE명령어로 롤을 취소시킴

○ 롤 생성과 권한 포함

ROLESTUDY 롤 생성 및 ORCLSTUDY사용자에게 권한 부여 및 부여된 권한 확인

○ 부여된 롤 취소 및 삭제

REVOKE(롤 부여 취소), DROP(롤 삭제)