반응형
/********************************************************************************************
-- Title : [8i] 롤 관리(OLN)
-- Reference : OLN
-- Key word : role
********************************************************************************************/
/****************************************************************************************
-- 미리 정의된 롤 사용
****************************************************************************************/
-- Title : [8i] 롤 관리(OLN)
-- Reference : OLN
-- Key word : role
********************************************************************************************/
/****************************************************************************************
-- 미리 정의된 롤 사용
****************************************************************************************/
/****************************************************************************************
-- 롤 정보 출력
****************************************************************************************/
SQL> SELECT role, password_required
2 FROM dba_roles;
-- 롤 정보 출력
****************************************************************************************/
SQL> SELECT role, password_required
2 FROM dba_roles;
ROLE PASSWORD
------------------------------ --------
CONNECT NO
RESOURCE NO
DBA NO
SELECT_CATALOG_ROLE NO
EXECUTE_CATALOG_ROLE NO
DELETE_CATALOG_ROLE NO
EXP_FULL_DATABASE NO
IMP_FULL_DATABASE NO
RECOVERY_CATALOG_OWNER NO
AQ_ADMINISTRATOR_ROLE NO
AQ_USER_ROLE NO
SNMPAGENT NO
OEM_MONITOR NO
HS_ADMIN_ROLE NO
MANAGER NO
SALES_CLERK YES
HR_CLERK EXTERNAL
------------------------------ --------
CONNECT NO
RESOURCE NO
DBA NO
SELECT_CATALOG_ROLE NO
EXECUTE_CATALOG_ROLE NO
DELETE_CATALOG_ROLE NO
EXP_FULL_DATABASE NO
IMP_FULL_DATABASE NO
RECOVERY_CATALOG_OWNER NO
AQ_ADMINISTRATOR_ROLE NO
AQ_USER_ROLE NO
SNMPAGENT NO
OEM_MONITOR NO
HS_ADMIN_ROLE NO
MANAGER NO
SALES_CLERK YES
HR_CLERK EXTERNAL
17 rows selected.
/****************************************************************************************
-- 롤 생성
****************************************************************************************/
SQL> CREATE ROLE sales_clerk;
Role created.
SQL> CREATE ROLE hr_clerk
2 IDENTIFIED BY bonus;
2 IDENTIFIED BY bonus;
Role created.
SQL> CREATE ROLE hr_manager
2 IDENTIFIED EXTERNALLY;
2 IDENTIFIED EXTERNALLY;
Role created.
/****************************************************************************************
-- 롤 수정
****************************************************************************************/
SQL> ALTER ROLE sales_clerk
2 IDENTIFIED BY commission;
Role altered.
SQL> ALTER ROLE hr_clerk
2 IDENTIFIED EXTERNALLY;
2 IDENTIFIED EXTERNALLY;
Role altered.
SQL> ALTER ROLE hr_manager
2 NOT IDENTIFIED;
2 NOT IDENTIFIED;
Role altered.
/****************************************************************************************
-- 롤 지정
****************************************************************************************/
SQL> GRANT sales_clerk TO scott;
Grant succeeded.
SQL> GRANT hr_manager TO scott
2 WITH ADMIN OPTION;
2 WITH ADMIN OPTION;
Grant succeeded.
/****************************************************************************************
-- 기본 롤 설정
****************************************************************************************/
SQL> ALTER USER scott
2 DEFAULT ROLE hr_clerk, sales_clerk;
User altered.
SQL> ALTER USER scott
2 DEFAULT ROLE ALL;
2 DEFAULT ROLE ALL;
User altered.
SQL> ALTER USER SCOTT DEFAULT ROLE ALL
2 EXCEPT hr_clerk;
2 EXCEPT hr_clerk;
User altered.
SQL> ALTER USER scott DEFAULT ROLE NONE;
User altered.
/****************************************************************************************
-- 롤 Enable과 Disable 예
****************************************************************************************/
SQL> SET ROLE sales_clerk
2 IDENTIFIED BY commission;
Role set.
SQL> SET ROLE hr_clerk;
SET ROLE hr_clerk
*
ERROR at line 1: -- 생성 시 EXTERNALLY옵션 설정됨.
ORA-01989: role 'HR_CLERK' not authorized by operating system
SET ROLE hr_clerk
*
ERROR at line 1: -- 생성 시 EXTERNALLY옵션 설정됨.
ORA-01989: role 'HR_CLERK' not authorized by operating system
SQL> SET ROLE ALL EXCEPT sales_clerk;
Role set.
SQL> SET ROLE NONE;
Role set.
/****************************************************************************************
-- 사용자에게서 롤 제거
****************************************************************************************/
SQL> REVOKE sales_clerk FROM scott;
Revoke succeeded.
SQL> REVOKE hr_manager FROM PUBLIC;
REVOKE hr_manager FROM PUBLIC
*
ERROR at line 1:
ORA-01951: ROLE 'HR_MANAGER' not granted to 'PUBLIC'
REVOKE hr_manager FROM PUBLIC
*
ERROR at line 1:
ORA-01951: ROLE 'HR_MANAGER' not granted to 'PUBLIC'
/****************************************************************************************
-- 롤 제거
****************************************************************************************/
SQL>DROP ROLE hr_manager;
Role dropped.
/****************************************************************************************
-- 미리 정의된 롤 사용
****************************************************************************************/
반응형