오라클 데이터베이스 관리자 기초- (10)권한 관리 및 롤 관리

* 권한 관리

오라클 사용자 권한의 두가지 유형

- 시스템 권한 : 사용자가 데이터베이스에서 특정 작업을 수행할 수 있도록 함.

- object 권한 : 사용자가 특정 object를 access 및 조작할 수 있게 한다. 

 

시스템 권한

- 100개 이상의 시스템 권한이 존재

- any 키워드는 사용자가 임의의 스키마에서 권한을 가짐을 의미. any 키워드는 일반 사용자에게 절대 주면 안 된다.(다른 사용자의 스키마에 테이블을 만드는 등의 위험이 있음)

- grant 명령은 사용자 또는 사용자 그룹에 권한 추가

- reovoke는 권한을 삭제

- CREATE INDEX라는 권한은 없다.(내 테이블의 index를 만들때는 권한이 필요 없다)

 

시스템 권한 부여

- grant 명령을 사용해서 시스템 권한 부여. 여러개를 주고 싶을 경우 ,로 나열

- with admin option : 이 권한에 대해서는 관리자와 같은 권한을 부여.

- TO 다음에 user외에도 role(롤), PUBLIC(모든 사용자에게 권한 부여)이라고 써줄 수 있다.

 

sysdba 및 sysoper 권한

- sysdba가 'sysoper privileges with admin option' 권한을 가지고 있으므로 훨씬 더 크다.

- 데이터베이스 관리자만이 관리자 권한으로 데이터베이스에 연결할 수 있어야 함.

- sysdba의 권한은 sys만 사용한다. 
- sysdba로 연결하면 데이터베이스나 데이터베이스 내의 객체에 작업을 수행할 수 있는 제한되지 않은 권한이 사용자에게 부여됨.

 

시스템 권한 취소

- revoke 명령을 사옹하여 사용자의 시스템 권한 제거

- admin option권한이 있는 사용자는 시스템 권한을 취소할 수 있음

- grant 명령을 사옹하여 부여받은 권한만 취소 가능

 

admin option을 사용해 시스템 권한 취소

- emi는 권한을 누가 줬는지 모르기때문에 DBA가 아닌 jeff가 권한을 부여했어도 사용하는데 문제 없다

- DBA가 jeff의 권한을 없애도 emi의 권한 사용에는 문제가 없다.

- 따라서 권한을 함부로 부여해서는 안된다.

 

object 권한

- view에는 alter, index 권한이 없다. (그림상 오류)

 

객체 권한 부여

- grant 명령을 사용해 객체 권한 부여

- 객체가 권한을 부여한 사용자의 스키마에 있어야 하거나 권한을 부여한 사용자가 grant option 권한을 가져아 함

 

객체 권한 취소

- revoke 명령을 사용해 객체 권한 부여를 취소

- 권한을 취소하는 사용자는 객체 권한을 부여한 사용자여야 함.

 

with grant option을 사용해 객체 권한 취소

- emi가 jeff의 권한을 뺏을 수 없다(부모 자식 관계가 존재)

- bob이 emi의 권한을 직접 없앨 수 없기때문에 jeff의 권한을 삭제하면 jeff에게 부여받은 권한도 모두 삭제된다.

 

권한 정보 얻기

DBA_SYS_PRIVS : 현재 사용자나 롤에 부여된 시스템 권한(privilege) 나열 

SESSION_PRIVS : 현재 세션에서 사용할 수 있는 권한 나열 

DBA_COL_PRIVS : 데이터 베이스의 모든 객체에 대해 부여된 모든 권한 나열

 

* 롤

- CREATE TABLE은 A만 가능

- INSERT와 UPDATE는 B만 가능 

- 위와 같이 구현되려면 사용자 A, B, C외에도 jobs를 가지고 있는 계정까지 최소 4개의 계정이 필요하다. 

 

롤의 장점

- 권한 관리 용이성

- 권한 관리를 유연하게 할 수 있음(실질적인 가장 큰 장점) 

- 권한의 선택적 가용성

- 운영 체제를 통한 부여

 

롤 생성

Not identified : 비밀번호 없이 생성

IDENTIFIED BY password : 비밀번호 부여. 그러나 이렇게 사용할 일은 없다. 

 

Predefined roles(미리 정의된 롤)

- oracle사의 기본 권고사항은 미리 정의된 롤을 사용하지 말고 참고만 할 것을 권고.

 

롤 수정

- NOT IDENTIFIED 명령을 사용해 비밀번호를 없애줄 수 있음.

 

롤 할당

- 순환해서 할당할 수 없다.

- 시스템 권한과 부여할때와 같은 방식으로 할당한다.

 

default 롤 설정(실무에서는 사용할 일이 없음)

- 활성화 할 role 설정

- default role 설정. 따로 지정해주지 않으면 모든 role를 사용한다.

- ALTER USER scott DEFAULT ROLE ALL이 기본 설정

 

롤 활성화 및 비활성화(실무에서는 사용할 일이 없음)

- 롤을 비활성화해서 사용자가 권한 받은 롤을 일시적으로 취소

- 롤을 활성화해서 일시적으로 부여

- set role 명령은 롤을 활성화 및 비활성화 함.

- 기본 롤은 로그인시 사용자에 대해 활성화

- 롤을 활성화 할때 암호가 필요할 수도 있다. 

 

user의 롤 취소

- user의 롤을 취소하려면 admin option 또는 GRANT ANY ROLE 권한이 필요

- REVOKE를 사용한다. 

 

롤 제거

- 부여된 모든 사용자와 롤에서 해당 롤을 제거

- 데이터베이스에서 롤 제거

 

롤 생성 지침

- 그림상의 사용자는 사람이 아닌 직위를 의미, 마찬가지로 사용자 롤도 직위 롤이라는 의미

- 응용 프로그램 롤은 직무(업무) 롤을 의미

 

롤 정보 얻기(암기)

DBA_USERS : 데이터베이스의 모든 유저 확인 

DBA_ROLES : 데이터베이스의 모든 롤 확인

 

DBA_ROLE_PRIVS : 사용자 및 롤에 할당된 롤 확인

DBA_SYS_PRIVS :  사용자 및 롤에 할당된 시스템 권한 확인

DBA_TAB_PRIVS :  사용자 및 롤에 할당된 객체(object) 권한 확인

 

ROLE_ROLE_PRIVS : 롤에 할당된 롤 확인

ROLE_SYS_PRIVS : 롤에 할당된 시스템 권한 확인

ROLE_TAB_PRIVS : 롤에 할당된 객체 권한 확인

 

SESSION_ROLES : 사용자가 현재 활성화 한 롤 확인

 

[실습]user에 대한 권한과 role 조회

<사용 명령어> 
SELECT grantee, privilege, admin_option FROM dba_sys_privs;
- User 또는 role에 할당된 시스템 권한을 조회 한다.
- GRANTEE : 권한을 할당 받은 user
- PRIVILEGE : 권한
- ADMIN_OPTION : 할당 받은 권한을 관리자와 동일하게 사용할 수 있는지 여부

 

SELECT grantee, owner, table_name, privilege, grantor FROM dba_tab_privs;
- User 또는 role에 할당된 객체 권한을 조회 한다.
- OWNER : 객체 권한이 할당된 개체의 소유 스키마
- TABLE_NAME : 객체 권한이 할당된 table명
- GRANTOR : 객체 권한을 할당해준 user (sys일 경우 owner와 동일하게 표시된다.)

 

SELECT grantee, granted_role FROM dba_role_privs;
- User 또는 role에 할당된 role을 조회 한다.
- GRANT_ROLE : 할당된 role명

CREATE TABLESPACE usr
DATAFILE '/app/ora12c/oradata/disk1/usr01.dbf' SIZE 5M;

 

CREATE USER u1  
IDENTIFIED BY u1
DEFAULT TABLESPACE usr
QUOTA UNLIMITED ON usr;

CREATE USER u2
IDENTIFIED BY u2
DEFAULT TABLESPACE usr
QUOTA UNLIMITED ON usr;

CREATE USER u3
IDENTIFIED BY u3
DEFAULT TABLESPACE usr
QUOTA UNLIMITED ON usr;

// 예제용 User를 생성

SELECT username, default_tablespace, temporary_tablespace, account_status, profile
FROM dba_users
ORDER BY 1;

//생성된 User를 확인.

CREATE ROLE r1;

CREATE ROLE r2;

// 롤 생성

 

DESC dba_roles;

// dba_roles 구조 확인

SELECT role FROM dba_roles  
ORDER BY 1;

// 생성된 role을 확인

GRANT create session TO u1,u2,u3;

// u1, u2, u3에게 시스템 권한인 create session을 할당.(직접 권한)

 

GRANT create table TO r1; 

// r1에게 role에게 시스템 권한인 create table을 할당.

 

DESC dba_sys_privs;

// dba_sys_privs의 구조 확인

 

SELECT grantee, privilege, admin_option FROM dba_sys_privs
WHERE grantee in ('U1','U2','U3','R1','R2')
ORDER BY 1;

// WHERE절에 정의한 role과 user에 할당된 시스템 권한을 확인.

CREATE TABLE u2.u21 (no NUMBER);

 

CREATE TABLE u3.u31 (no NUMBER);
// 객체 권한 할당을 위한 예제 table을 각각 u2, u3에 생성. 관리자 계정이기때문에 문제 없이 생성된다.

 

GRANT select ON u2.u21 TO u1;

//u1에게 u2의 u21 테이블에 select 할 수 있는 객체 권한을 할당 

 

GRANT select ON u3.u31 TO r2;

// r2 Role에게 u3의 u31 테이블에 select 할 수 있는 객체 권한을 할당.

 

DESC dba_tab_privs;

// dba_tab_privs의 구성 요소 확인

SELECT grantee, owner, table_name, privilege, grantor FROM dba_tab_privs
WHERE grantee in ('U1','U2','U3','R1','R2')
ORDER BY 1;

// Role과 User에 할당된 객체 권한을 확인. 여기서 grantor는 부여한 사람이 아닌 부여할 수 있도록 결정한 사용자가 출력된다. 

GRANT r1,r2 TO u1;

// u1 user에게 r1,r2 role을 할당.

 

GRANT r2 TO u2;

// u2 user에 r2 role을 할당

 

DESC dba_role_privs;

// dba_role_privs 구성 요소 확인 

 

SELECT grantee, granted_role FROM dba_role_privs
WHERE grantee in ('U1','U2','U3','R1','R2')
ORDER BY 1;

// Role과 User에 할당된 role을 확인

 

[실습]시스템 권한 할당(grant)과 해제(revoke)

<사용 명령어> 
GRANT <시스템 권한>,<시스템 권한> .......
TO [<user명 | role명>,<user명 | role명> ...... | PUBLIC];
[WITH ADMIN OPTION];
- User 또는 role에 시스템 권한을 할당한다.
- PUBLIC : DB의 모든 user에게 권한을 할당한다.
- WITH ADMIN OPTION : 할당된 권한을 SYS 관리자와 동일하게 할당하거나 해제할 수 있다.

 

REVOKE <시스템 권한>,<시스템 권한> .......
FROM [<user명 | role명>,<user명 | role명> ...... | PUBLIC];
- User 또는 role에 시스템 권한을 해제

SELECT grantee, privilege, admin_option FROM dba_sys_privs 
WHERE grantee in ('U1','U2','U3') 
ORDER BY 1;

// u1, u2, u3 의 권한을 조회. (사용자가 없다면 실습 2.1을 참고 해서 생성)

 

GRANT create any table TO u1;

// u1에게 create any table 시스템 권한 할당

GRANT create sequence TO u1
WITH ADMIN OPTION;

// u1에게 create sequence 시스템 권한 할당을 with admin option으로 부여

 

GRANT create table TO u2;

// u2에게 create table 시스템 권한 할당

SELECT grantee, privilege, admin_option FROM dba_sys_privs
WHERE grantee in ('U1','U2','U3')
ORDER BY 1;

// u1, u2, u3 의 권한을 조회. 새롭게 할당받은 권한 확인 

CONN u1/u1

CREATE TABLE u3.test (no NUMBER);

// ANY 키워드는 관리 권한의 하나로 모든 스키마를 대상으로 한다. 즉 u1은 모든 스키마에 table을 생성 할 수 있다.

 

CONN u3/u3

 

SELECT * FROM tab;

// u1이 생성한 table 그러나 u3 소유이다. 자신이 소유한 테이블이기때문에 삭제하는데는 문제가 없다. 

CONN u1/u1

 

GRANT create sequence TO u2  
WITH ADMIN OPTION;

// with admin option이 있기때문에 u1으로 로그인해도 권한을 다시 u2에게 할당할 수 있다.

 

CONN u2/u2

 

GRANT create sequence TO u3;

// 권한은 u1-u2-u3 순으로 할당 됨.

 
CONN / as sysdba

 

SELECT grantee, privilege, admin_option FROM dba_sys_privs
WHERE grantee in ('U1','U2','U3') and privilege = 'CREATE SEQUENCE'
ORDER BY 1;

// 시스템 권한은 현재 권한의 소유 여부만 저장될 뿐 누구로부터 할당 되었는지 관리하지 않음으로 할당의 선후 관계를 확인 할 수 없음.

CONN u2/u2

 

REVOKE create sequence FROM u1;

//u2는 자신에게 CREATE sequence 권한을 할당한 user가 비록 u1이지만, 시스템 권한은 할당한 사용자를 관리하지 않음으로 해제가 가능. 객체 권한과의 운영상의 가장 큰 차이점.

CONN / as sysdba;

 

SELECT grantee, privilege, admin_option FROM dba_sys_privs
WHERE grantee in ('U1','U2','U3') and privilege = 'CREATE SEQUENCE'
ORDER BY 1;

// u2와 u3만 남아있는 것을 확인할 수 있다.

 

SELECT grantee, privilege, admin_option FROM dba_sys_privs
WHERE grantee in ('U1','U2','U3')
ORDER BY 1;

// User 또는 role에 할당된 시스템 권한을 조회

REVOKE create any table, create table, create sequence FROM PUBLIC;

// Public으로 할당된 권한만 public으로 해제 할 수 있다.

 

REVOKE create any table FROM u1;

REVOKE create table FROM u2;

REVOKE create sequence FROM u2,u3;

//다양한 권한이 다양한 user에게 직접 할당 된 경우 모두 하나씩 해제해야 한다.

 

SELECT grantee, privilege, admin_option FROM dba_sys_privs
WHERE grantee in ('U1','U2','U3')
ORDER BY 1;

// 권한 확인

 

[실습]객체 권한 할당(grant)과 해제(revoke)

<사용 명령어> 
GRANT <객체 권한>,<객체 권한> ....... ON <[user명].객체명>, <[user명].객체명> ......
TO [<user명 | role명>,<user명 | role명> ...... | PUBLIC];
[WITH GRANT OPTION];
- User 또는 role에 객체 권한을 할당한다.
- PUBLIC : DB의 모든 user에게 권한을 할당한다.
- WITH GRANT OPTION : 할당된 권한을 다른 user에게 할당할 수 있다.

- ON절에 내 것이 아닌 대상이 적혀있다. 

 

REVOKE <객체 권한>,<객체 권한> ....... ON <[user명].객체명>, <[user명].객체명> ......
FROM [<user명 | role명>,<user명 | role명> ...... | PUBLIC];
- User 또는 role에 객체 권한을 해제한다.

- 직접 할당한 권한만 해제 가능하다.

CONN u1/u1

CREATE TABLE u11 (no NUMBER);

 

SELECT * FROM tab;

GRANT select ON u11 TO u2 WITH GRANT OPTION;

// u11 table에 대한 SELECT 권한을 u2에게 WITH GRANT OPTION으로 할당

 

CONN u2/u2

 

GRANT select ON u1.u11 TO u3;

// u2는 u1 사용자의 u11 테이블에 대한 SELECT 권한을 u3에게 할당

 

CONN u1/u1

SELECT grantee, owner, table_name, privilege, grantor FROM user_tab_privs
WHERE table_name = 'U11';

// u11에 대한 권한을 u2에게만 주었지만 u2 사용자의 WITH GRANT OPTION 사용으로 인해 u3까지 권한이 할당됨

REVOKE select ON u11 FROM u3; 

// 비록 자신이 소유한 table의 권한이지만 u3의 권한은 직접 할당한게 아닌 u2가 준 권한이므로 해제 불가능

 

REVOKE select ON u11 FROM u2;

// u2의 권한을 해제하면 u2가 할당한 u3의 권한까지 cascade하게 해제 된다.

 

SELECT grantee, owner, table_name, privilege, grantor FROM user_tab_privs
WHERE table_name = 'U11';

CONN / as sysdba

 

GRANT select ON u1.u11 TO u2;

// sys 관리자가 일반 user table의 객체 권한을 할당 하는 경우 grantor가 객체 소유자가 된다.

 

CONN u1/u1

 

SELECT grantee, owner, table_name, privilege, grantor FROM user_tab_privs
WHERE table_name = 'U11';

// 할당을 sys가 했지만 소유자로 인식

 

[실습]Role 생성과 권한 할당

<사용 명령어> 
CREATE ROLE 
[IDENTIFIED BY <암호>];
- Role을 생성한다.

 

DROP ROLE ;
- Role을 삭제한다.

 

GRANT TO <user명 | role 명>;
- Role을 user나 role에 할당 한다.
- Role이 role에 할당되는 경우 순환해서 할당되지 않는다.

DESC dba_roles;

// dba_roles의 구조 확인

SELECT role FROM dba_roles;

// 미리 만들어진 role은 새로운 role을 만들 때 참조하기 위한 role. connect, resource, dba 등은 매우 유용한 예제. R1과 R2 역시 확인이 가능하다

CREATE ROLE rm;  
CREATE ROLE rs1;
CREATE ROLE rs2;
CREATE ROLE rs3;

// 사용될 role을 생성

SELECT role FROM dba_roles
WHERE role IN ('RM','RS1','RS2','RS3');

// 만들어진 롤 확인

GRANT create session, create table, create sequence TO rs1;

GRANT create trigger, create procedure TO rs2;

// rs1, rs2 role에 각각 시스템 권한을 부여.

 

SELECT role, privilege FROM role_sys_privs
WHERE role IN ('RM','RS1','RS2','RS3');
ORDER BY 1;

// role에 부여된 시스템 권한을 조회. 

 

SELECT grantee, privilege FROM dba_sys_privs 
WHERE grantee IN ('RM','RS1','RS2','RS3');
ORDER BY 1;

// role_sys_privs의 정보는 모두 dba_sys_privs에서 조회 가능.

GRANT select, insert ON u1.u11 TO rs3;

// rs3 role에 u1의 u11테이블에 대한 select, insert 객체 권한을 부여.

SELECT role, owner, table_name, privilege FROM role_tab_privs
WHERE role IN ('RM','RS1','RS2','RS3');

// role에 부여된 객체 권한을 조회.

SELECT grantee, owner, table_name, privilege, grantor FROM dba_tab_privs
WHERE grantee IN ('RM','RS1','RS2','RS3');

// grantor가 u1으로 나온다. 

GRANT rs1, rs2, rs3 TO rm; 

// role에 role을 부여한다.

 

SELECT role, granted_role FROM role_role_privs
WHERE role IN ('RM','RS1','RS2','RS3');

// role에 부여된 role을 조회한다.

 

SELECT grantee, granted_role FROM dba_role_privs
WHERE grantee IN ('RM','RS1','RS2','RS3');

 

GRANT rm TO rs1;

// role에 role을 부여할 때는 순환해서는 안된다.