role 비활성화

-- EXPERT 계정에 부여된 role 확인

SYS @ prod > select * from dba_role_privs
  2  where grantee='EXPERT';

GRANTEE                      GRANTED_ROLE             ADM DEF
------------------------------ ------------------------------ --- ---
EXPERT                         RESOURCE                       NO  YES
EXPERT                         CONNECT                        NO  YES

-- EXPERT 계정에 connect role 만 활성화

SYS @ prod > alter user expert default role connect;

User altered.

SYS @ prod > select * from dba_role_privs
  2  where grantee='EXPERT';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
EXPERT                         RESOURCE                       NO  NO     --> 비활성화 상태
EXPERT                         CONNECT                        NO  YES

SYS @ prod > conn expert/expert
Connected.

-- RESOURCE role 이 비활성화 상태이므로 테이블 생성 안됨
EXPERT @ prod > create table role_test (role_name varchar2(15));
create table role_test (role_name varchar2(15))
*
ERROR at line 1:
ORA-01031: insufficient privileges

-- EXPERT 계정에서 자신에게 부여되었지만 비활성화 상태인 role 활성화
EXPERT @ prod > set role resource;

Role set.

-- RESOURCE role 활성화 후 테이블 생성 됨
EXPERT @ prod > create table role_test (role_name varchar2(15));

Table created.

-- 자신에게 활성화되어 있는 role 확인
EXPERT @ prod > select * from session_roles;

ROLE
------------------------------
RESOURCE

EXPERT @ prod > conn /as sysdba
Connected.

-- set role 명령어는 session 레벨이기 때문에 session 종료 후에는 다시 비활성화 됨
SYS @ prod > select * from dba_role_privs
  2  where grantee='EXPERT';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
EXPERT                         RESOURCE                       NO  NO
EXPERT                         CONNECT                        NO  YES

SYS @ prod > conn expert/expert
Connected.

EXPERT @ prod > create table ex(a number(10));
create table ex(a number(10))
*
ERROR at line 1:
ORA-01031: insufficient privileges

EXPERT @ prod > conn /as sysdba
Connected.

-- EXPERT 계정의 모든 role 활성화
SYS @ prod > alter user expert default role all;

User altered.

YS @ prod > select * from dba_role_privs
 2  where grantee='EXPERT';

RANTEE                        GRANTED_ROLE                   ADM DEF
----------------------------- ------------------------------ --- ---
XPERT                         RESOURCE                       NO  YES
XPERT                         CONNECT                        NO  YES

-- EXPERT 계정의 CONNECT를 제외한 모든 role 활성화
SYS @ prod > alter user expert default role all except connect;

User altered.

SYS @ prod > select * from dba_role_privs
  2  where grantee='EXPERT';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ -a-- ---
EXPERT                         RESOURCE                       NO  YES
EXPERT                         CONNECT                        NO  NO

SYS @ prod > conn expert/expert
ERROR:
ORA-01045: user EXPERT lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.
 @  > conn /as sysdba
Connected.

SYS @ prod > alter user expert default role all except resource;

User altered.

SYS @ prod > select * from dba_role_privs
  2  where grantee='EXPERT';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
EXPERT                         RESOURCE                       NO  NO
EXPERT                         CONNECT                        NO  YES

SYS @ prod > alter user expert default role all;

User altered.

SYS @ prod > select * from dba_role_privs
  2  where grantee='EXPERT';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
EXPERT                         RESOURCE                       NO  YES
EXPERT                         CONNECT                        NO  YES