170601 오라클 데이터베이스 계정 생성 및 권한 설정

- 계정 생성 방법

- 시스템 권한, 객체관리 이해

- 권한 부여와 철회 방법

- 롤의 이해 

 

데이터 베이스 관리자는 사용자들의 데이터베이스에 대한 액세스를 용도에 따라 제한해야한다. 불필요한 접속 및 필요보다 큰 권한을 부여하게되면 DB에 위협이 되는 요소가 많아지기 때문에 계정을 생성하고 권한을 설정해서 위험도를 최소화하고 회피할 수 있어야 한다.

계정 생성과 권한 부여의 기본적인 로직은 CREATE USER문을 이용한 계정 생성 → GRANT문을 이용한 권한부여의 순이다.

계정생성방법

CREATE USER user_name IDENTIFIED BY password

 : user_name 으로 된 계정이 password 의 비밀번호로 생성된다.

관리자 계정으로는 Oracle과 그 외 DB의 차이점이 있다. Oracle은 데이터베이스 서버를 구성하면, sys 및 SYSTEM 계정이 관리자 계정으로 자동 생성된다. 이 계정들에 대해서는 권한을 변경하거나 삭제하는 행위는 삼가하는것이 좋다. 그 외의 DB에서는 root로 관리자 계정이 생성되는데 흔히들 착각하는것이 운영체제의 root가 DB에 접근하는것으로 보이는데, 운영체제의 root는 DB를 설치하는 역활을 해주기는 하지만 DB 내부에서 관리자로 생성되는 root는 이름만 같을 뿐이지 서로 다른존재라고 한다. 

---

시스템 권한(SYSTEM PRIVILEGES) >>

데이터베이스에 대한 액세스 및 데이터베이스의 특정 작업을 수행할 수 있는 권한이며, 관리자계정만이 부여 할 수 있다.

관리자 계정이 사용하는 대표적인 시스템 권한(부여하는것이 아니다!)
CREATE USER	데이터베이스 계정을 생성 할 수 있습니다.
DROP USER	데이터베이스 계정 및 스키마를 삭제 할 수 있습니다.
BACKUP ANY TABLE	exp/expdp 툴을 이용하여 다른 스키마의 테이블을 백업 할 수 있습니다.
SELECT ANY TABLE	다른 스키마에 속한 테이블의 데이터르 조회 할 수 있습니다.
CREATE ANY TABLE	다른 스키마에 속하는 테이블을 생성 할 수 잇습니다.
DROP ANY TABLE	다른 스키마에 속한 테이블을 삭제할 수 있습니다.

★ ANY 가 포함된 시스템 권한은, 다른 스키마에 종속되는 작업에 대한 권한입니다.

개발자들이 사용하는 데이터베이스 계정에 부여되는 권한 (관리자 -(grant)→ 개발자 계정)
CREATE SESSION	오라클 데이터베이스에 접속 할 수 있습니다.
ALTER SESSION	일부 세션의 설정을 [ALTER SESSION SET] 문을 이용하여 변경 할 수 있습니다.
CREATE TABLE	계정의 스키마에 테이블 객체를 생성 할 수 있습니다.
CREATE SEQUENCE	계정의 스키마에 시퀸스 객체를 생성 할 수 있습니다.
CREATE VIEW	계정의 스키마에 뷰 객체를 생성 할 수 있습니다.
CREATE DATABASE LINK	계정의 스키마에 DATEBASE LINK 객체를 생성 할 수 있습니다.
CREATE PROCEDURE	계정의 스키마에 프로시저, 함수, 패키지 같은 내장 프로그램을 생성 할 수 있습니다.

위의 권한을 부여받은 스키마는 자신의 객체를 구현 할 수 있게 됩니다.

객체 권한(OBJECT PRIVILEGES) >>

스키마 객체를 접근할 수 있는 권한을 부여해 준다. INSERT , SELECT 문 등이 객체라 하며 관리자와 객체 소유자가 모두 권한을 부여 할 수있도록 되있지만 대부분의 경우에 관리자가 객체권한을, 권한이 필요한 데이터베이스 계정에게 부여합니다.

객체유형	객체 권한의 이름
TABLE	SELECT , INSERT , DELETE , UPDATE , ALTER, INDEX, REFERENCES
VIEW	SELECT , INSERT , DELETE , UPDATE
SEQUENCE	SELECT , ALTER
PROCEDURE	EXECUTE

* index - 색인 기능으로 미리 컬럼에 있는 값에 대해 row id(포인터)를 부여하여 값과 row id를 조합해 Entry를 만들어두고 정렬해서 저장합니다. 

이 Entry를 이용해서 데이터를 찾으면 메모리에 모든 테이블 데이터가 올라가야할 부담이 없습니다.

권한부여방법

GRANT 객체권한이름, 객체권한이름,... ON 스키마이름.객체이름 TO 계정이름, 계정이름,... ;

- GRANT

SQL > GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, ALTER SESSION

TO HR1, HR2 ; 

→ HR1 계정과 HR2 계정에게, 데이터베이스 서버에 접속하여, 테이블, 뷰를 생성할 수 있는 권한과 세션의 설정을 변경 할 수 있는 권한을 부여

---

-REVOKE 

SQL > REVOKE CREATE VIEW, CREATE SYNONYM 

FROM HR1 ; 

→ HR1 계정으로부터 CREATE VIEW, CREATE SYNONYM 권한 철회

---

- PUBLIC

SQL > GRANT CREATE SESSION 

TO PUBLIC ;

→ 모든 데이터베이스 계정이 CREATE SESSION 권한을 사용할 수 있게끔 설정 

---

-특정 객체 권한

SQL > GRANT SELECT, INSERT, DELETE, ALTER, INDEX, UPDATE, REFERENCES

ON HR.LOCATIONS -- 객체권한은 ON절에 객체를 하나밖에 못적는다.

TO HR1, HR2 ;   

→ HR의 LOCATIONS 테이블을 HR1,HR2가 SELECT,INSERT,DELETE 등.... 을 사용할 권한을 부여한다. LOCATION 테이블에만 국한된권한 부여라는것이 특징. 

---

-외래키 사용을 위한 REFERENCES 권한 부여 

SQL > GRANT REFERENCES 

ON HR.DEPARTMENTS

TO HR1 ;

   

→ HR1 에게 HR.DEPARTMENTS 를 참조할 수 있도록 권한을 부여합니다.

-참조중인 테이블은 그냥 권한 해제 할 수 없다. 

- CASCADE CONSTRAINTS 를 사용해서 참조관계의 테이블 정리하기

SQL > REVOKE REFERENCES

ON HR.DEPARTMENTS

FROM HR1 CASCADE CONSTRAINTS

→ 레퍼런스 권한을 이용하여 구성된 외래키 목록을 모두 삭제한 후 계정에게 부여된 REFERENCES 권한이 철회 됩니다.

---

ORACLE의 롤 (ROLE) 

ROLE은 관리자에게 받은 권한을 사용자에게 대신 부여해 줍니다. 어떤 장점이 있길래 권한을 대신 전달해 주는지 살펴봅시다.

Direct-Privilege	Role-Privilege
데이터베이스 계정이나 롤에게, 권한을 직접 부여하거나 철회하면 변경사항이 이 후에 접속하는 세션 및 이미 데이터베이스 접속한 사용자 세션에도 바로 적용됩니다.	데이터베이스 계정이나 롤에게 , 롤을 통해서 권한이 부여되면 롤권한이라고 한다. 변경한 이 후에 접속한 세션부터 적용되며, 변경전에 접속한 사용자 세션에는 적용되지 않는다. 오라클 DB를 생성하면 DBA, CONNECT, RESOURCE라는 이름들의 롤이 기본적으로 생성됩니다.
	CONNECT-ROLE	CREATE SESSION
	RESOURCE-ROLE	객체를 생성할 수 있는 시스템권한  (CREATE VIEW , CREATE SYNONYM 제외)
	DBA-ROLE	관리업무의 202개의 시스템 권한 (SYS 계정에는 모든 권한, SYSTEM 계정에는 202개의 권한)

직접 권한을 부여하는 방법의 경우 하나하나의 객체에 하나하나의 권한을 모두 일일히 지정해 주어야 합니다. 당연히 작업량이 많고 번거럽겠죠.

타 DB 에 비해 ORACLE은 권한지정이 상당히 디테일하기 때문에 권한의 종류도 많고 그 대상도 세분화 되있습니다. 그렇기 때문에 ROLE이라는 기능을 사용해서 권한을 나눠주게 되는데요. ROLE 은 권한을 담은 상자라고 생각하시면 됩니다. 관리자는 ROLE이라는 상자에 나누어줄 권한을 담아주고 사용자들이 ROLE에 붙게되면 그 권한을 할당받게 되는 것이죠. ROLE은 권한을 사용자에게 나누어 주기도 하지만. 권한을 나누어줄 권한. 즉 사용자가 다른 객체에게 권한을 줄수있도록 ROLE에서 ROLE로 주는방식으로 설정할 수 도 있습니다.(GRANT OPTION)

롤 생성 → 롤에 권한 설정 → 롤에서 권한 전달

ROLE 생성 방법 

: 롤 생성시에 비밀번호를 설정 할 수도 있습니다. 

CREATE ROLE 롤_이름 [IDENTIFIED BY '비밀번호']

ROLE에 권한 부여 

: 롤에 객체에 명시된 권한을 부여합니다. 권한은 ON 절에서 대상을 정의할 수 있습니다. 시스템 권한 및 롤은 DBA만 다른 데이터베이스 계정에게 부여 할 수 있습니다.

GRANT 권한,권한,... [ON 스키마.테이블] TO 롤_이름

ROLE 에서 USER or ROLE로 권한 부여 

: 이작업을 수행하면 변경후에 접속한 세션부터 적용됩니다. 

GRANT 롤_이름 TO 계정이름 or another_롤_이름

-롤 생성

SQL > CREATE ROLE EMPROLE1 ;

→ EMPROLE1 이라는 ROLE을 생성

---

- 롤에 권한부여 

SQL > GRANT CREATE SESSION, CREATE TABLE

TO EMPROLE1 ;

→ 세션과 테이블을 생성할 수 있는 권한을 EMPROLE1에게 부여한다.

---

- 특정 객체 권한 

SQL > GRANT SELECT 

ON HR.EMPLOYEES

TO EMPROLE1 ;

→ HR.EMPLOYEES 테이블의 SELECT 권한을 EMPROLE1 롤에 부여함

---

- 권한을 부여받은 롤에서부터 계정과 다른 롤에 전달

SQL > GRANT EMPROLE1 

TO HR1, HR2 ;

→ EMPROLE1에 등록된 권한을 HR1, HR2 에게도 부여해 줍니다.

---

데이터베이스에 생성하는 계정의 권한부여 권장 지침 >>

- 스키마 계정 :  스키마 객체들의 구축이 완료되면 , 스키마 계정은 암호를 파기하고 계정에 대하여 LOCK을 설정 하도록 합시다. 

 권한을 소유한 계정이기 때문에 방치시킬경우 DB를 위협하는 위험요소가 될 가능성이 있습니다. 

- 프로그램 접속 계정 : 스키마 객체에 대하여 프로그램 기능과 관련된 객체 권한만 제한적으로 부여. 

- 개발자 및 엔지니어 접속계정 : 프로그램 개발과 관련된 스키마 객체에 대한 객체 권한만 제한적으로 부여합니다. SELECT권한을 필요한 테이블의                                   데이터 영역에만 사용 할 수 있도록 설정해 줍니다. 불필요한 권한 부여는 보안에 위협이됩니다.