[Oracle] - 사용자, 권한, 롤 관리

* 사용자 관리

- 사용자란?

• 데이터베이스에 접속하여 데이터를 사용/관리하는 계정
• 업무의 분할과 효율, 보안을 고려하여 생성( 데이터를 활용한 서비스의 규모가 크면 데이터 분량이 방대해지기에 업무 분할, 효율, 보안을 고려해 업무에 따라 여러 사용자들을 나눈다.)

 

• 사용자 관리 방식

1. 업무별 사용자를 생성한 후에 각 사용자 업무에 맞는 데이터 구조를 만들어 관리하는 방식

2. 대표 사용자를 통해 업무에 맞는 데이터 구조를 먼저 정의한 뒤 사용할 수 있는 데이터 영역을 각 사용자에게 지정하는 방식

 

 

---

 

 

- 데이터베이스 스키마(schema)란?

: 데이터를 저장 및 관리하기 위해 정의한 데이터베이스 구조의 범위를 분류하는 그룹 단위

• 오라클 데이터베이스에서는 스키마와 사용자 구별 안하고 사용하기도 한다. 
• 사용자 : 데이터를 사용 및 관리하기 위해 데이터베이스에 접속하는 개체 ex) HR 계정
• 스키마 : 오라클 데이터베이스에 접속한 사용자와 연결된 객체 ex) HR계정으로 만든 모든 객체(테이블, 뷰, 제약 조건, 인덱스, 시퀀스, 동의어 등)

 

 

---

 

 

- 사용자 생성

• 기본 형식

 

<예시>

- HR계정으로 사용자 생성

CREATE USER ORCLSTUDY
IDENTIFIED BY ORACLE;

=> CREATE USER 명령어 실행 안됨 (사용자 생성할 권한이 없기 때문에)

 

* 일반적으로 사용자 생성은 데이터베이스 관리 권한을 가진 사용자가 권한을 가진다.(SYS, SYSTEM)

 

- SYS 사용자로 접속 후 사용자 생성(SQL * PLUS)

CREATE USER ORCLSTUDY
IDENTIFIED BY ORACLE;

=> SYS라는 SYSDBA 사용자로 접속한 후 CREATE문을 실행하면 생성가능

=> 새로 생성한 ORCLSTUDY 사용자로 CONN명령어 실행해 접속 시도하면 데이터베이스 연결을 위한 권한(CREATE SESSION 권한) 이 없기 때문에 접속 안됨.

 

-SYS 사용자로 접속 후 ORCLSTUDY 사용자에게 권한 부여

GRANT CREATE SESSION TO ORCLSTUDY;

=> ORCLSTUDY에 권한 부여

=> ORCLSTUDY 사용자로 데이터 베이스 접속 성공

 

 

 

---

 

 

 

- 사용자 정보 조회

• 사용자가 소유 객체 정보를 얻기 위해 데이터 사전 사용하는 방법

…_USERS

SELECT * FROM ALL_USERS
 WHERE USERNAME = 'ORCLSTUDY';

SELECT * FROM DBA_USERS
 WHERE USERNAME = 'ORCLSTUDY';

SELECT * FROM DBA_OBJECTS
 WHERE OWNER = 'ORCLSTUDY';

 

 

 

 

---

 

 

 

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

• 변경 : ALTER 
• 삭제 : DROP
  • CASCAD

<예시>

- ORCLSTUDY 사용자의 패스워드 ORCLE로 변경

ALTER USER ORCLSTUDY
IDENTIFIED BY ORCLE;

=> 패스워드 변경 성공 (패스워드 변경하면 기존 패스워드로는 접속 불가능)

=> 패스워드 바뀌었으니 다시 연결해야함

 

 

- 사용자 삭제

DROP USER ORCLSTUDY;

=> ORCLSTUDY 사용자로 접속하고 있는 세션을 종료해야 삭제가 가능함

 

 

- 사용자와 객체 모두 삭제

DROP USER ORCLSTUDY CASCADE;

 

 

 

 

---

 

 

 

* 권한 관리

• 접속 사용자에 따라 접근할 수 있는 데이터 영역을 지정
• 시스템 권한(system privilege)와 객체 권한(object privilege)으로 분류

ex) 인터넷 카페의 사용자 등급에 따라 사용 가능한 메뉴가 달라지도록 설정

 

- 시스템 권한이란?

: 사용자 생성, 정보 수정, 삭제, 데이터베이스 접근, 여러 자원과 객체 생성 및 관리 등의 권한을 포함

 

• 시스템 권한(일부)

시스템 권한 분류	시스템 권한	설명
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문을 통해 부여받은 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여받음

 

 

<예시>

- SYS계정으로 ORCLSTUDY 생성

CREATE USER ORCLSTUDY
IDENTIFIED BY ORACLE;

 

- ORCLSTUDY사용자에게 CREATE SESSION 권한 부여

GRANT RESOURCE, CREATE SESSION, CREATE TABLE TO ORCLSTUDY;

=> 권한 부여( 데이터베이스 접속, 테이블 생성 가능, 소유테이블 생성-> INSERT, SELECT문 가능)

 

✔️ RESOURCE

: 오라클 데이터베이스에서 제공하는 롤 중 하나로 사용자를 생성할 때 사용 테이블 스페이스의 영역을 무제한 사용가능하게 해 주는 권한이 포함되어 있다.

 

* 롤 : 여러 권한을 하나의 이름으로 묶어 권한 부여 관련 작업을 간편하게 하려고 사용하는 것

• GRANT문에 RESOURCE 지정하지 않으면 사용자에게 테이블 생성 권한을 부여해도 CREATE문으로 테이블 생성할 수 없거나 생성해도 INSERT문에서 오류(테이블 스페이스 즉, 테이블이 저장되는 공간 영역을 지정하지 않아서)가 뜬다.
• UNLINITED TABLESPACE 권한은 엄밀한 관리가 필요한 경우에는 적절하지 않기에 QUOTA절로 사용 영역에 제한을 둔다. (이러한 이슈로 오라클 데이터베이스 12C버전에서는 UNLINITED TABLESPACE권한 부여 X)

ALTER USER ORCLSTUDY
QUOTA 2M ON USERS;

 

 

 

 

---

 

 

 

 

- 시스템 권한 취소

• 기본 형식

 

<예시>

- ORCLSTUDY사용자 RESOURCE, CREATE TABLE권한 취소

=> 테이블 생성 불가

 

 

 

 

---

 

 

 

 

- 객체 권한이란?

: 특정 사용자가 생성한 객체(테이블, 인덱스, 뷰, 시퀀스 등)와 관련된 권한

ex) HR 소유 테이블에 ORCLSTUDY 사용자가 SELECT나 INSERT 등의 작업이 가능하도록 허용 가능

 

• 주로 사용하는 객체 권한

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

 

 

 

 

---

 

 

 

 

- 객체 권한 부여

• 기본 형식

 

 

<예시>

- ORCLSTUDY 사용자에게 TEMP테이블 권한 부여

 

- ORCL에게 TEMP 테이블의 여러 권한 한번에 부여

 

- ORCLSTUDY로 사용 권한 부여받은 TEMP 테이블 사용

 

 

 

 

---

 

 

 

- 객체 권한 취소

• 기본 형식

 

<예시>

- ORCLSTUDY 에 부여된 TEMP테이블 사용 권한 취소

=> TEMP 테이블 조회 실패함

 

 

 

 

---

 

 

 

* 롤 관리

- 롤이란?

: 여러 종류의 권한을 묶어 놓은 그룹으로 여러 권한을 한 번에 부여하고 해제할 수 있어 권한 관리에 효율적이다.

• 사전 정의된 롤(predefined roles)
• 사용자 정의 롤(user roles)

- 사전 정의된 롤

• CONNECT 롤
  • 10g 버전 : 사용자가 데이터베이스에 접속하는데 필요한 CREATE SESSION권한을 가지고 있음
  • 9i 버전 : ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW

• RESOURCE 롤
  • 사용자가 테이블, 시퀀스를 비롯한 여러 객체를 생성할 수 있는 기본 시스템 권한을 묶어놓은 롤
  • CREATE TRIGGER, CREATE SEQUENCE, CREATE TYPE, CREATE PROCEDURE, CREATE CLUSTER, CREATE PERATOR, CREATE INDEXTYPE, CREAT TABLE

* 새로운 사용자 생성 - CONNECT롤, RESOURCE롤 부여

하지만 CONNECT롤 - 뷰 생성하는 CREATE VIEW 권한 , 동의어 생성 CREATE SYNONYM 권한 없으므로 따로 부여해야함

• DBA 롤
  • 데이터베이스를 관리하는 시스템 권한을 대부분 가지고 있음(11g 버전 기준 202개 권한)

 

 

---

 

 

 

- 사용자 정의 롤

: 필요에 의해 직접 권한을 포함시켜 생성한 롤

 

 

<예시>

- SYS 계정으로 ROLESTUDY 롤 생성 및 권한 부여

 

- ORCLSTUDY 사용자에게 롤(ROLESTUDY)부여

 

- ORCLSTUDY에 부여된 롤, 권한 확인

 

- 부여된 롤 취소

 

- 롤 삭제