231221 Linux_role 권한 관리, role 비활성화/활성화 설정

# INSA SESSION

SQL> show user
USER is "INSA"
SQL> SELECT * FROM role_sys_privs;

 

# 터미널(putty) 화면 출력

출처: https://yunamom.tistory.com/338

SQL> col role format a15
SQL> col privilege format a20

SQL> SELECT * FROM role_sys_privs;

SQL> set linesize 200 : 가로 사이즈, SESSION 이 살아있는 동안에 적용된다.
SQL> set pagesize 1000 : 행당 사이즈 조절
SQL> SELECT * FROM role_sys_privs;

SQL> col owner format a10
SQL> col table_name format a15
SQL> col column_name format a15
SQL> select * from role_tab_privs;

SQL> select * from hr.departments;

27. SYS SESSION 
SQL> set pagesize 1000
SQL> SELECT * FROM dba_roles;
SQL> select * from dba_sys_privs where grantee = 'CONNECT';

SQL> col grantee format a10
SQL> col privailege format a15
SQL> /

SQL> select * from dba_sys_privs where grantee = 'RESOURCE';

 

# 함부로 부여하지 말것!

select * from dba_tab_privs where grantee = 'DBA';

SQL> create role mgr;
SQL> SELECT count(*) FROM hr.employees; <- select any table 시스템 권한이 있어서 select 가능

SQL> grant select any table to mgr;
SQL> select * from dba_sys_privs where grantee = 'MGR';

SQL> grant mgr to insa;
SQL> select * from dba_role_privs where grantee = 'INSA';

28. INSA SESSION
SQL> SELECT * FROM session_roles;

SQL> conn insa/oracle : 권한부여확인 -> 재접속해야 mgr 확인 가능, 활성화
SQL> SELECT * FROM session_roles;

SQL> SELECT * FROM role_sys_privs;

SQL> SELECT * FROM role_tab_privs;

SQL> select * from hr.employees;
SQL> select * from hr.locations;

SQL> SELECT * FROM sys.user$; : 오류발생

SQL> SELECT * FROM user_role_privs;

#)DEF : default role은 기본적으로 활성화되어 있다.

# SYS SESSION

select * from sys.user$;

 

29. SYS SESSION 
# 일반적으로 role을 유저한테 부여하면 default role 로 활설화 된다.
SQL> SELECT * FROM dba_role_privs WHERE grantee = 'INSA';

# insa 유저가 받은 role 중에 mgr role 은 제외한 후 다른 role 은 활성화
#) role 비활성화 설정 시, user별로 지정
SQL> alter user insa default role all except mgr;
(insa session 계정 활성화 하되, grant_role 에서 'mgr'은 제외(비활성화 하기) )
SQL> alter user insa default role all except mgr;
SQL> SELECT * FROM dba_role_privs WHERE grantee = 'INSA';

30. INSA SESSION
SQL> conn insa/oracle : insa 재접속
SQL> SELECT * FROM session_roles;

role 전후 확인

SQL> SELECT * FROM hr.locations;

SQL> SELECT * FROM user_role_privs;

SQL> SELECT * FROM role_sys_privs;

31. SYS SESSION 
# insa 계정 모두 비활성화 설정하기
SQL> alter user insa default role none;
SQL> select * from dba_role_privs where grantee = 'INSA';

32. INSA SESSION 
SQL> conn insa/oracle
SQL> SELECT * FROM session_roles;
SQL> select * from user_role_privs;
SQL> select * from role_sys_privs;

SQL> select * from role_tab_privs;

33. SYS SESSION
SQL> alter user insa default role all except prog;
SQL> select * from dba_role_privs where grantee = 'INSA';

34. INSA SESSION 
SQL> conn insa/oracle
SQL> SELECT * FROM session_roles;

SQL> select * from user_role_privs;

SQL> select * from role_sys_privs;

SQL> select * from role_tab_privs;

35. SYS SESSION
SQL> alter user insa default role all;
SQL> select * from dba_role_privs where grantee = 'INSA';

36. INSA SESSION 
SQL> conn insa/oracle
SQL> SELECT * FROM session_roles;

SQL> select * from user_role_privs;

SQL> select * from role_sys_privs;

SQL> select * from role_tab_privs;

SQL> select * from hr.departments;

SQL> create view emp_view
  2  as select * from hr.employees;
SQL> select * from emp_view;

SQL> SELECT * FROM user_views;
SQL> select text from user_views where view_name = 'EMP_VIEW';

 

SQL> select text from user_views where view_name = 'EMP_VIEW';

SQL> set long 200 : 글자수 까지 출력

SQL> select text from user_views where view_name = 'EMP_VIEW';

38. SYS SESSION 
SQL> REVOKE mgr FROM insa;
SQL> select * from dba_role_privs where grantee = 'INSA';

# 비밀번호를 이용해서 role 생성
SQL> create role mgr identified by oracle; 

- 패스워드를 통해 role 생성, passwd 가 아는 user에 한해서 insa 계정에서 직접 활성화해야한다.
SQL> grant select any table to mgr;
SQL> select * from dba_sys_privs where grantee = 'MGR';

SQL> grant mgr to insa;

SQL> select * from dba_role_privs where grantee = 'INSA';

39. INSA SESSION 
SQL> conn insa/oracle : 재접속
SQL> select * from session_roles; : passwd 가 아는 user에 한해서 insa 계정에서 직접 활성화해야한다.

SQL> select * from session_privs;

SQL> select * from user_role_privs;

# user가 직접 role 활성화 : set 명령어를 이용
SQL> set role mgr; 
- 패스워드 아는 user에 한해서 권한 부여 가능

SQL> set role mgr identified by oracle;

SQL> select * from session_roles; : prog 는 자동으로 비활성화

SQL> select * from user_role_privs;

# passwd 지정했으므로  all 은 불가능. 
SQL> set role all;

 

SQL> set role prog, mgr identified by oracle;
SQL> select * from session_roles;
SQL> select * from user_role_privs;

 

# user가 스스로 role 비활성화 설정 가능
SQL> set role all except mgr;
SQL> select * from user_role_privs;
SQL> select * from session_roles;

40. SYS SESSION
# passwd 지정 하지 않는다. role의 비밀번호 지우기
SQL> alter role mgr not identified;

41. INSA SESSION 
SQL> conn insa/oracle
SQL> select * from session_roles;
SQL> select * from user_role_privs;

# role 비활성화
SQL> set role none;
SQL> select * from session_roles;

# role 모두 활성화

SQL> set role all;

SQL> select * from session_roles;