사용자, 권한, 롤 관리

1. 사용자 관리

🌹 사용자 USER: 데이터를 관리하는 계정

사용자 관리가 필요한 이유- 업무 분할과 효율, 보안을 위해서 업무에 따른 사용자를 나누는 과정이 필요하기 때문

way1 - 업무별 사용자 생성 후, 각 사용자 업무에 맞는 데이터 구조를 만들어서 관리

way2- 대표 사용자를 통해서 업무에 맞는 데이터 구조를 먼저 정의한 후에 사용할 수 있는 데이터 영역을 각 사용자에게 지정

🌹 데이터베이스 스키마 Database Schema

데이터를 저장하거나 관리하기 위해 정의한 데이터베이스 구조의 범위를 그룹단위로 분류한 것

오라클 데이터베이스에 접속한 사용자와 연결된 객체

예) 동의어, 인덱스, 테이블, 뷰,...........................

사용자 생성 - SYSTEM 혹은 SYS 계정에서 수행해야 함!!

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

필수 지정 조건인 사용자 이름과 패스워드 외에는 간단한 사항만 확인해보자

DEFAULT TABLESPACE : 사용자가 생성하는 객체에 대한 기본 테이블 스페이스 지정. 생략 시, 데이터베이스 기본 테이블스페이스에 저장됨 -DB에 대해서 기본 테이블 스페이스가 지정되지 않은 경우, SYSTEM 테이블 스페이스에 저장됨

TEMPORARY TABLESPACE : 임시 segment에 대한 테이블 스페이스(그룹) 지정 -미지정시, 기본 임시 저장 테이블 스페이스 혹은 SYSTEM 테이블 스페이스에 저장됨

QUOTA : 테이블 스페이스에 할당할 수 있는 최대 공간을 지정 -여러 QUOTA절이 CREATE USER clause에 들어갈 수 있음!!

PROFILE : 사용자에게 할당할 프로필을 지정 -프로필 : 사용자가 사용할 수 있는 데이터베이스 리소스(데이터나 객체 등 사용할 수 있는 요소, 자원을 의미) 양 제한

PASSWORD EXPIRE : 사용자 암호를 만료시킬지 여부 지정

ACCOUNT LOCK : 사용자 계정의 접근을 비활성화 ACCOUNT UNLOCK : 사용자 계정의 잠금을 해제하고 접근할 수 있도록 함

Reference: https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8003.htm#SQLRF01503

오라클 DB 저장 공간

reference: https://itragdoll.tistory.com/67

🌻 세그먼트: 하나의 테이블 스페이스에 저장된 각 데이터베이스 객체

하나의 테이블 스페이스 ⊃ 多 segment

多 테이블 스페이스 ⊃ 1개의 segment ⊃ 한 개 이상의 익스텐트

하나의 세그먼트는 여러 개의 데이터 파일들이 같은 테이블 스페이스 내부에 할당되어 있다면, 다수의 데이터 파일에 나누어 저장될 수 있음

🌻 익스텐트 extent : 하나의 세그먼트에 할당된 공간

🌻 오라클 블록 : DB를 구성하는 가장 최소 저장 공간. 실제 데이터가 저장되는 공간

⭐ 계정을 생성하기만 하면 안됨!! 데이터베이스 접속권한을 부여해야 아래의 명령어로 접속 시도를 할 수 있음!

💡

CONN 계정명/비밀번호;

▶️ 접속 권한 부여:(SYSTEM 혹은 SYS 계정에서)

💡

GRANT CREATE SESSION TO 계정명;

사용자 정보 조회

사용자 정보는 데이터 사전 뷰를 이용하여 확인 가능하다

ALL_USERS 테이블

SELECT * FROM ALL_USERS
[WHERE USERNAME='계정명'];

위와 같이 수행하게 되면, USERNAME 뿐 아니라, USER_ID(고유번호), CREATED(계정생성날짜)를 확인해볼 수 있다

2. DBA_USERS 테이블

SELECT * FROM DBA_USERS [WHERE USERNAME='계정명'];

DBA_USERS 테이블에서는 처음에 위에서 살펴보았던 CREATE USERS 절에 포함된 모든 정보를 보여준다

3. DBA_OBJECTS 테이블

SELECT *
FROM DBA_OBJECTS
WHERE OWNER='계정명';

이번에는 DBA_OBJECTS테이블을 이용하게 되면, 해당 계정에서 생성한 테이블뿐 아니라 뷰, 제약조건 등 모든 데이터베이스 객체를 확인해볼 수 있다!

🌺 사용자 정보(비밀번호) 변경하기

ALTER USER 계정명
IDENTIFIED BY 변경할_비밀번호;

🌺 사용자 삭제하기

DROP USER 계정명;

🌺 사용자와 객체 모두 삭제

DROP USER 계정명 CASCADE;

2. 권한 관리

사용자 정보 외에 접속 사용자에 따른 접근가능 데이터 영역 및 권한을 지정함으로써 데이터 안전을 보장하기 위함

🌹 권한의 종류

시스템 권한 System privilege - 데이터베이스 관리 권한이 있는 사용자가 부여할 수 있는 권한 - 사용자 생성 및 정보 수정 및 삭제, 데이터베이스 접근, 오라클 데이터베이스의 객체 생성 및 관리 등에 대한 권한 -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)이름/PUBLIC]
[WITH ADMIN OPTION];

WITH ADMIN OPTION: SYSTEM 계정에서 시스템권한을 부여한 사용자가 다른 사용자에게 해당 권한을 부여해줄 수 있는 해당권한에 대한 관리자로써 역할할 수 있게됨

🌟 GRANT로 RESOURCE 롤을 부여하는 것의 중요성 🌟

❓ 롤 ❓

여러 권한을 하나의 이름으로 묶어 권한 부여 관련 작업을 간편하게 해주는 도구!

🌹 RESOURCE: 사용자 생성 시, 사용하는 테이블 스페이스 영역을 무제한으로 사용 가능하게(UNLIMITED TABLESPACE)해주는 권한이 포함되어, 테이블 생성 및 조작을 시도할 시 아래와 같은 에러가 발생하는 경우를 막아줄 수 있다

💡

ORA-01950: 테이블 스페이스 USERS 권한이 없습니다

BUT 엄밀한 관리가 필요한 경우, 사용자를 생성 및 수정할 때 QUOTA절로 사용 영역에 제한을 둘 필요가 있다!

CREATE USER FLOWER
IDENTIFIED BY 1234
QUOTA 2M ON USERS;--테이블스페이스 크기, 테이블 스페이스 이름

ALTER USER FLOWER
QUOTA 2M ON USERS;

🌺 시스템 권한 취소

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

2. 객체 권한 Object privilege

특정 사용자가 생성한 데이터베이스 객체에 대해서 다른 사용자가 특정 작업을 할 수 있도록 설정하는 권한

특정 사용자 계정에서 권한을 부여할 수 있음!

객체 권한 분류-TABLE(테이블)

객체 권한	설명
ALTER	테이블 변경 권한
DELETE	테이블 데이터 삭제 권한
INDEX	테이블 인덱스 생성 권한
INSERT	테이블 데이터 삽입 권한
REFERENCES	참조 데이터 생성 권한(외래키 설정 관련)
SELECT	테이블 조회 권한
UPDATE	테이블 데이터 수정 권한

객체 권한 분류-VIEW(뷰)

객체 권한	설명
DELETE	뷰 데이터 삭제 권한
INSERT	뷰 데이터 삽입 권한
REFERENCES	참조 데이터 생성 권한
SELECT	뷰 조회 권한
UPDATE	뷰 데이터 수정 권한

객체 권한 분류-SEQUENCE(시퀀스)

객체 권한	설명
ALTER	시퀀스 수정 권한
SELECT	시퀀스의 CURRVAL, NEXTVAL 사용권한

객체 권한 분류-PROCEDURE(프로시져)

객체 권한	설명
EXECUTE	프로시져를 실행하는 권한

이외에도 FUNCTION, PACKAGE 에 대한 객체 권한들도 존재한다

🌺 객체 권한 부여

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

ALL PRIVILEGES : 객체의 모든 권한을 부여

스키마.객체이름은 사용자이름을 지정한 후 사용자이름.객체이름으로 사용할 수도 있음! 혹은 GRANT 다음에 롤 이름이 옴으로써 ON절을 생략해도 된다!

WITH GRANT OPTION: 해당 객체 권한을 부여받는 사용자가 다른 사용자에게 해당 권한을 부여할 수 있는 권리도 부여받게 해줌! 현재 권한을 부여받은 사용자의 권한이 사라지면, 다른 사용자에게 재부여된 권한도 함께 사라짐(연쇄적)

🌷 예시- A계정에서 B 계정 사용자에게 TEMP 테이블 권한 부여하기

먼저 A 계정에 접속해서,

아래와 같은 TEMP 테이블을 먼저 생성하자

CONN 아이디/비밀번호;

CREATE TABLE TEMP(
	COL1 VARCHAR2(30),
	COL2 VARCHAR2(30)
);

그 다음에는 A 계정에서 B 계정에 TEMP 태이블에 대한 데이터 조회 및 삽입 권한을 부여하자!

GRANT SELECT, INSERT ON TEMP TO B;

이렇게 객체권한을 부여하면, 아래와 같은 A계정의 TEMP 테이블에 대해서 SELECT, INSERT 작업을 할 수 있고 실제 작업 결과를 COMMIT하게 되면 A 계정에서도 확인해볼 수 있다!

위와 같이, 다른 B계정에서 A계정.객체 로 접근하면 아래 결과와 같이 테이블을 조회해볼 수 있음을 확인할 수 있다!

그리고 B계정에서 INSERT 작업을 하게 되면 아래와 같이 데이터가 잘 삽입된 것을 확인해볼 수 있다

여기서 궁금한 것 첫 번째는, 커밋을 했는데, 본 계정 내부의 테이블에도 잘 반영이 되었을지다!!

확인해보자!

그러면 위와 같이 B계정에서 삽입한 데이터가 잘 반영되었음을 확인해볼 수 있다!

🌟 지금은 시간차를 두고, COMMIT을 해주어 문제가 없지만, 동시에 같은 테이블을 다루는 것은 조심하자! 잘못하면 교착상태(락)이 걸릴 가능성이 있기 때문이다! 반드시 커밋을 해주는 것을 확인하고 또 확인하자!

그러면, B계정에서 A 계정으로부터 부여받지 않은 객체 권한을 시도하면 어떻게 될까? 궁금하지 않은가?

DELETE FROM KH.TEMP;

위와 같이 B계정에서 DELETE 질의를 하고자 시도한다면,

💡

SQL 오류: ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges" *Cause: An attempt was made to perform a database operation without the necessary privileges. *Action: Ask your database administrator or designated security administrator to grant you the necessary privileges

불충분한 권한이라는 오류를 맞이하게 된다!

마지막으로 , 객체 권한을 조회하는 것이 궁금하다!

이는 USER_SYS_PRIVS 데이터 사전 뷰를 이용하면 가능하다!

🌺 객체 권한 취소

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

💡

잠깐, 나중에 나오는 JDBC 부분과 개념을 정립을 위해서 내용과는 무관하지만, 정리해보자! 아래의 그림을 참고하면, 1. JDBC는 다양한 DBMS에 자바 코드로 접근할 수 있도록 도와주는 도구 2. DBMS는 MYSQL, ORACLE 등의 다양한 방법의 SQL을 이용하여 DB에 접근할 수 있도록 도와주는 도구 라고 정리해볼 수 있다! 그리고 엄연하게 JDBC ≠ DBMS 임을 잊지 말자!

이미지 출처: https://www.javatpoint.com/jdbc-driver

다시 본론으로 돌아와서 객체 권한 옵션 중 생성부분과 다른 CASCADE 부분을 살펴보자

CASCADE CONSTRAINTS/FORCE] - CASCADE CONSTRAINTS 는 REFERENCES나 ALL PRIVILEGES 를 부여했던 경우에 대해서 해당 권한을 취소하는 것이다. 단, REFERENCES는 ALL PRIVILEGES를 통해 암묵적으로 부여되었을 수도 있다. 그리고, REFERENCES 권한을 사용함으로써 참조 무결성 제약조건이 해제된다!(외래키 조건이 사라지므로 데이터 값이 일치하는 등을 고려할 필요가 없어짐] -CASCADE FORCE는 종속성이 있는 객체에 대해서 EXECUTE 권한을 취소할 수 있다

REFERENCE: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9020.htm

REVOKE SELECT, INSERT
ON     TEMP
FROM   TEST;

위와 같이 기존에 부여했던 권한을 취소시켜보자

그러면 B계정에서 SELECT나 INSERT를 할 수 있을지 확인해보자

위와 같이, 권한이 취소된 후에는 테이블이나 뷰가 존재하지 않는다는 ORA-00942 오류가 출력된다!

3. 롤 관리

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

🌺 1. 사전 정의된 롤

A. CONNECT 롤 : 사용자가 접속하는 권한이 묶여져 있음

오라클 9i버전까지는 아래의 권한들이 묶여져 있었지만 10g 부터는 CREATE SESSION 권한만 존재

💡

CREATE SESSION, ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE VIEW

B. RESOURCE 롤

사용자가 여러 객체(테이블, 뷰, 인덱스)를 생성할 수 있는 기본 권한이 묶여져 있음

💡

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

C. DBA 롤

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

🌺 2. 사용자 정의 롤

사용자의 필요에 의해 직접 권한을 포함시킨 롤

💡

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

🌟 롤 생성시에는 다양한 권한을 모두 가진 SYSTEM 계정을 이용함이 보다 나을 것!

(예)

1) SYSTEM 계정에 접속 CONN ~~~~~~~/~~~~~~

2) ROLE 생성

💡

CREATE ROLE ROLEST;

3) GRANT로 권한을 롤에게 부여

💡

GRANT CONNECT, RESOURCE, DBA, CREATE VIEW, CREATE SYNONYM TO ROLEST;

4) GRANT로 롤을 사용자에게 전달

💡

GRANT ROLEST TO KH;

🌟 부여된 객체 권한 조회하기(부여받은 계정에서)

💡

SELECT * FROM USER_SYS_PRIVS; SELECT * FROM USER_ROLE_PRIVS;

USER_SYS_PRIVS를 통해서는 객체 권한에서 표현되던 방식인 CREATE SYNONYM 등으로 표시되고, USER_ROLE_PRIVS를 통해서는 롤 이름으로 표현됨을 확인해볼 수 있다!

5) 만약, 생성한 롤을 삭제하고 싶다면

💡

REVOKE 롤이름 FROM 사용자; REVOKE ROLEST FROM KH;

로 부여된 롤을 먼저 취소하고

💡

DROP ROLE 롤이름; DROP ROLE ROLEST;

로 모든 해당 롤을 모두 삭제하게 됨으로써, 모든 사용자의 해당 롤이 취소되게 된다!

즉, ROLEST롤이 A,B,C사용자에게 부여된 상태에서 사용자 A 계정에서 위의 과정을 수행하기만 해도 나머지 B,C 사용자의 롤 리스트에서 제거된다!