SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jan 28 10:28:02 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn /as sysdba;
Connected.
===========================================================================================
ROLE_SYS_PRIVS
============================================================================================
SQL> create role tk_role;
Role created.
SQL> grant create session to tk_role;
Grant succeeded.
SQL> grant create any table to tk_role;
Grant succeeded.
SQL> grant drop tablespace to tk_role;
Grant succeeded.
SQL> grant restricted session to tk_role;
Grant succeeded.
SQL> desc role_sys_privs;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLE NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
SQL> select * from role_sys_privs where role='TK_ROLE';
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TK_ROLE CREATE SESSION NO
TK_ROLE DROP TABLESPACE NO
TK_ROLE CREATE ANY TABLE NO
TK_ROLE RESTRICTED SESSION NO
SQL> grant ALTER ANY TABLE to tk_role WITH ADMIN OPTION;
Grant succeeded.
SQL> grant DROP ANY TABLE to tk_role WITH ADMIN OPTION;
Grant succeeded.
SQL> grant CREATE TABLESPACE to tk_role WITH ADMIN OPTION;
Grant succeeded.
SQL> grant UPDATE ANY TABLE to tk_role WITH ADMIN OPTION;
Grant succeeded.
SQL> select * from role_sys_privs where role='TK_ROLE';
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TK_ROLE CREATE SESSION NO
TK_ROLE DROP ANY TABLE YES
TK_ROLE ALTER ANY TABLE YES
TK_ROLE DROP TABLESPACE NO
TK_ROLE CREATE ANY TABLE NO
TK_ROLE UPDATE ANY TABLE YES
TK_ROLE CREATE TABLESPACE YES
TK_ROLE RESTRICTED SESSION NO
8 rows selected.
============================================================================================
ROLE_TAB_PRIVS
============================================================================================
=
SQL> CREATE ROLE TK_TAB;
Role created.
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON SCOTT.EMP TO TK_TAB;
Grant succeeded.
SQL> DESC ROLE_TAB_PRIVS;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
SQL> COLUMN ROLE FORMAT A10;
SQL> COLUMN OWNER FORMAT A10;
SQL> COLUMN TABLE_NAME FORMAT A10;
SQL> SELECT ROLE,OWNER,TABLE_NAME,PRIVILEGE FROM ROLE_TAB_PRIVS WHERE ROLE='TK_T
AB';
ROLE OWNER TABLE_NAME PRIVILEGE
---------- ---------- ---------- ----------------------------------------
TK_TAB SCOTT EMP DELETE
TK_TAB SCOTT EMP INSERT
TK_TAB SCOTT EMP SELECT
TK_TAB SCOTT EMP UPDATE
============================================================================================
ROLE_ROLE_PRIVS
===========================================================================================
SQL> CREATE ROLE TK_PRIV;
Role created.
SQL> GRANT CONNECT,RESOURCE,DBA TO TK_PRIV;
Grant succeeded.
SQL> SELECT * FROM ROLE_ROLE_PRIVS WHERE ROLE='TK_PRIV';
ROLE GRANTED_ROLE ADM
---------- ------------------------------ ---
TK_PRIV DBA NO
TK_PRIV CONNECT NO
TK_PRIV RESOURCE NO
============================================================================================
DBA_ROLE_PRIVS
===========================================================================================
SQL> create user ravi identified by kumar;
User created.
SQL> GRANT TK_TAB TO RAVI;
Grant succeeded.
SQL> GRANT TK_ROLE TO RAVI;
Grant succeeded.
SQL> GRANT TK_PRIV TO RAVI;
Grant succeeded.
SQL> DESC DBA_ROLE_PRIVS;
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='RAVI';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
RAVI TK_TAB NO YES
RAVI TK_PRIV NO YES
RAVI TK_ROLE NO YES
SQL> SELECT * FROM ROLE_ROLE_PRIVS WHERE ROLE='TK_PRIV';
ROLE GRANTED_ROLE ADM
---------- ------------------------------ ---
TK_PRIV DBA NO
TK_PRIV CONNECT NO
TK_PRIV RESOURCE NO
SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='TK_ROLE';
ROLE PRIVILEGE ADM
---------- ---------------------------------------- ---
TK_ROLE CREATE SESSION NO
TK_ROLE DROP ANY TABLE YES
TK_ROLE ALTER ANY TABLE YES
TK_ROLE DROP TABLESPACE NO
TK_ROLE CREATE ANY TABLE NO
TK_ROLE UPDATE ANY TABLE YES
TK_ROLE CREATE TABLESPACE YES
TK_ROLE RESTRICTED SESSION NO
8 rows selected.
SQL> COLUMN COLUMN_NAME FORMAT A10;
SQL> COLUMN TABLE_NAME FORMAT A10;
SQL> COLUMN PRIVILEGE FORMAT A10;
SQL> SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE='TK_TAB';
ROLE OWNER TABLE_NAME COLUMN_NAM PRIVILEGE GRA
---------- ---------- ---------- ---------- ---------- ---
TK_TAB SCOTT EMP DELETE NO
TK_TAB SCOTT EMP INSERT NO
TK_TAB SCOTT EMP SELECT NO
TK_TAB SCOTT EMP UPDATE NO
TK_TAB SCOTT DEPT SELECT NO
============================================================================================= DBA_ROLES [ CREATING ROLE ]
===========================================================================================
SQL> create role tk_role;
Role created.
SQL> CREATE ROLE TK_TAB;
Role created.
SQL> CREATE ROLE TK_PRIV;
Role created.
SQL> CREATE ROLE TK_TK IDENTIFIED BY KUMAR;
Role created.
SQL> CREATE ROLE TK_HH IDENTIFIED BY EXTERNALLY;
Role created.
SQL> column role format a30;
SQL> SELECT * 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
GATHER_SYSTEM_STATISTICS NO
LOGSTDBY_ADMINISTRATOR NO
ROLE PASSWORD
------------------------------ --------
AQ_ADMINISTRATOR_ROLE NO
AQ_USER_ROLE NO
GLOBAL_AQ_USER_ROLE GLOBAL
OEM_MONITOR NO
HS_ADMIN_ROLE NO
WKUSER NO
WM_ADMIN_ROLE NO
JAVAUSERPRIV NO
JAVAIDPRIV NO
JAVASYSPRIV NO
JAVADEBUGPRIV NO
ROLE PASSWORD
------------------------------ --------
EJBCLIENT NO
JAVA_ADMIN NO
JAVA_DEPLOY NO
CTXAPP NO
XDBADMIN NO
AUTHENTICATEDUSER NO
OLAP_DBA NO
SALES_HISTORY_ROLE NO
TK_ROLE NO
TK_TAB NO
TK_PRIV NO
ROLE PASSWORD
------------------------------ --------
TK_TK YES
TK_HH YES
35 rows selected.
SQL>
===========================================================================================
MODIFYING ROLES
===========================================================================================
SQL> ALTER ROLE TK_PRIV IDENTIFIED BY KUMAR;
Role altered.
SQL> ALTER ROLE TK_HH NOT IDENTIFIED;
Role altered.
SQL> ALTER ROLE TK_TK IDENTIFIED BY EXTERNALLY;
Role altered.
SQL> SELECT * 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
GATHER_SYSTEM_STATISTICS NO
LOGSTDBY_ADMINISTRATOR NO
ROLE PASSWORD
------------------------------ --------
AQ_ADMINISTRATOR_ROLE NO
AQ_USER_ROLE NO
GLOBAL_AQ_USER_ROLE GLOBAL
OEM_MONITOR NO
HS_ADMIN_ROLE NO
WKUSER NO
WM_ADMIN_ROLE NO
JAVAUSERPRIV NO
JAVAIDPRIV NO
JAVASYSPRIV NO
JAVADEBUGPRIV NO
ROLE PASSWORD
------------------------------ --------
EJBCLIENT NO
JAVA_ADMIN NO
JAVA_DEPLOY NO
CTXAPP NO
XDBADMIN NO
AUTHENTICATEDUSER NO
OLAP_DBA NO
SALES_HISTORY_ROLE NO
TK_ROLE NO
TK_TAB NO
TK_PRIV YES
ROLE PASSWORD
------------------------------ --------
TK_TK YES
TK_HH NO
35 rows selected.
SQL>
===========================================================================================
REVOKING ROLE FROM USERS
===========================================================================================
SQL> SELECT* FROM DBA_ROLE_PRIVS WHERE GRANTEE='RAVI';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
RAVI TK_TAB NO YES
RAVI TK_PRIV NO YES
RAVI TK_ROLE NO YES
SQL> REVOKE TK_TAB FROM RAVI;
Revoke succeeded.
SQL> SELECT* FROM DBA_ROLE_PRIVS WHERE GRANTEE='RAVI';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
RAVI TK_PRIV NO YES
RAVI TK_ROLE NO YES
============================================================================================ REMOVING ROLES
============================================================================================
SQL> SELECT * 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
GATHER_SYSTEM_STATISTICS NO
LOGSTDBY_ADMINISTRATOR NO
ROLE PASSWORD
------------------------------ --------
AQ_ADMINISTRATOR_ROLE NO
AQ_USER_ROLE NO
GLOBAL_AQ_USER_ROLE GLOBAL
OEM_MONITOR NO
HS_ADMIN_ROLE NO
WKUSER NO
WM_ADMIN_ROLE NO
JAVAUSERPRIV NO
JAVAIDPRIV NO
JAVASYSPRIV NO
JAVADEBUGPRIV NO
ROLE PASSWORD
------------------------------ --------
EJBCLIENT NO
JAVA_ADMIN NO
JAVA_DEPLOY NO
CTXAPP NO
XDBADMIN NO
AUTHENTICATEDUSER NO
OLAP_DBA NO
SALES_HISTORY_ROLE NO
TK_ROLE NO
TK_TAB NO
TK_PRIV YES
ROLE PASSWORD
------------------------------ --------
TK_TK YES
TK_HH NO
35 rows selected.
SQL> DROP ROLE TK_TAB;
Role dropped.
SQL> SELECT * 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
GATHER_SYSTEM_STATISTICS NO
LOGSTDBY_ADMINISTRATOR NO
ROLE PASSWORD
------------------------------ --------
AQ_ADMINISTRATOR_ROLE NO
AQ_USER_ROLE NO
GLOBAL_AQ_USER_ROLE GLOBAL
OEM_MONITOR NO
HS_ADMIN_ROLE NO
WKUSER NO
WM_ADMIN_ROLE NO
JAVAUSERPRIV NO
JAVAIDPRIV NO
JAVASYSPRIV NO
JAVADEBUGPRIV NO
ROLE PASSWORD
------------------------------ --------
EJBCLIENT NO
JAVA_ADMIN NO
JAVA_DEPLOY NO
CTXAPP NO
XDBADMIN NO
AUTHENTICATEDUSER NO
OLAP_DBA NO
SALES_HISTORY_ROLE NO
TK_ROLE NO
TK_PRIV YES
TK_TK YES
ROLE PASSWORD
------------------------------ --------
TK_HH NO
34 rows selected.
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn /as sysdba;
Connected.
===========================================================================================
ROLE_SYS_PRIVS
============================================================================================
SQL> create role tk_role;
Role created.
SQL> grant create session to tk_role;
Grant succeeded.
SQL> grant create any table to tk_role;
Grant succeeded.
SQL> grant drop tablespace to tk_role;
Grant succeeded.
SQL> grant restricted session to tk_role;
Grant succeeded.
SQL> desc role_sys_privs;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLE NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
SQL> select * from role_sys_privs where role='TK_ROLE';
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TK_ROLE CREATE SESSION NO
TK_ROLE DROP TABLESPACE NO
TK_ROLE CREATE ANY TABLE NO
TK_ROLE RESTRICTED SESSION NO
SQL> grant ALTER ANY TABLE to tk_role WITH ADMIN OPTION;
Grant succeeded.
SQL> grant DROP ANY TABLE to tk_role WITH ADMIN OPTION;
Grant succeeded.
SQL> grant CREATE TABLESPACE to tk_role WITH ADMIN OPTION;
Grant succeeded.
SQL> grant UPDATE ANY TABLE to tk_role WITH ADMIN OPTION;
Grant succeeded.
SQL> select * from role_sys_privs where role='TK_ROLE';
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TK_ROLE CREATE SESSION NO
TK_ROLE DROP ANY TABLE YES
TK_ROLE ALTER ANY TABLE YES
TK_ROLE DROP TABLESPACE NO
TK_ROLE CREATE ANY TABLE NO
TK_ROLE UPDATE ANY TABLE YES
TK_ROLE CREATE TABLESPACE YES
TK_ROLE RESTRICTED SESSION NO
8 rows selected.
============================================================================================
ROLE_TAB_PRIVS
============================================================================================
=
SQL> CREATE ROLE TK_TAB;
Role created.
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON SCOTT.EMP TO TK_TAB;
Grant succeeded.
SQL> DESC ROLE_TAB_PRIVS;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
SQL> COLUMN ROLE FORMAT A10;
SQL> COLUMN OWNER FORMAT A10;
SQL> COLUMN TABLE_NAME FORMAT A10;
SQL> SELECT ROLE,OWNER,TABLE_NAME,PRIVILEGE FROM ROLE_TAB_PRIVS WHERE ROLE='TK_T
AB';
ROLE OWNER TABLE_NAME PRIVILEGE
---------- ---------- ---------- ----------------------------------------
TK_TAB SCOTT EMP DELETE
TK_TAB SCOTT EMP INSERT
TK_TAB SCOTT EMP SELECT
TK_TAB SCOTT EMP UPDATE
============================================================================================
ROLE_ROLE_PRIVS
===========================================================================================
SQL> CREATE ROLE TK_PRIV;
Role created.
SQL> GRANT CONNECT,RESOURCE,DBA TO TK_PRIV;
Grant succeeded.
SQL> SELECT * FROM ROLE_ROLE_PRIVS WHERE ROLE='TK_PRIV';
ROLE GRANTED_ROLE ADM
---------- ------------------------------ ---
TK_PRIV DBA NO
TK_PRIV CONNECT NO
TK_PRIV RESOURCE NO
============================================================================================
DBA_ROLE_PRIVS
===========================================================================================
SQL> create user ravi identified by kumar;
User created.
SQL> GRANT TK_TAB TO RAVI;
Grant succeeded.
SQL> GRANT TK_ROLE TO RAVI;
Grant succeeded.
SQL> GRANT TK_PRIV TO RAVI;
Grant succeeded.
SQL> DESC DBA_ROLE_PRIVS;
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='RAVI';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
RAVI TK_TAB NO YES
RAVI TK_PRIV NO YES
RAVI TK_ROLE NO YES
SQL> SELECT * FROM ROLE_ROLE_PRIVS WHERE ROLE='TK_PRIV';
ROLE GRANTED_ROLE ADM
---------- ------------------------------ ---
TK_PRIV DBA NO
TK_PRIV CONNECT NO
TK_PRIV RESOURCE NO
SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='TK_ROLE';
ROLE PRIVILEGE ADM
---------- ---------------------------------------- ---
TK_ROLE CREATE SESSION NO
TK_ROLE DROP ANY TABLE YES
TK_ROLE ALTER ANY TABLE YES
TK_ROLE DROP TABLESPACE NO
TK_ROLE CREATE ANY TABLE NO
TK_ROLE UPDATE ANY TABLE YES
TK_ROLE CREATE TABLESPACE YES
TK_ROLE RESTRICTED SESSION NO
8 rows selected.
SQL> COLUMN COLUMN_NAME FORMAT A10;
SQL> COLUMN TABLE_NAME FORMAT A10;
SQL> COLUMN PRIVILEGE FORMAT A10;
SQL> SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE='TK_TAB';
ROLE OWNER TABLE_NAME COLUMN_NAM PRIVILEGE GRA
---------- ---------- ---------- ---------- ---------- ---
TK_TAB SCOTT EMP DELETE NO
TK_TAB SCOTT EMP INSERT NO
TK_TAB SCOTT EMP SELECT NO
TK_TAB SCOTT EMP UPDATE NO
TK_TAB SCOTT DEPT SELECT NO
============================================================================================= DBA_ROLES [ CREATING ROLE ]
===========================================================================================
SQL> create role tk_role;
Role created.
SQL> CREATE ROLE TK_TAB;
Role created.
SQL> CREATE ROLE TK_PRIV;
Role created.
SQL> CREATE ROLE TK_TK IDENTIFIED BY KUMAR;
Role created.
SQL> CREATE ROLE TK_HH IDENTIFIED BY EXTERNALLY;
Role created.
SQL> column role format a30;
SQL> SELECT * 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
GATHER_SYSTEM_STATISTICS NO
LOGSTDBY_ADMINISTRATOR NO
ROLE PASSWORD
------------------------------ --------
AQ_ADMINISTRATOR_ROLE NO
AQ_USER_ROLE NO
GLOBAL_AQ_USER_ROLE GLOBAL
OEM_MONITOR NO
HS_ADMIN_ROLE NO
WKUSER NO
WM_ADMIN_ROLE NO
JAVAUSERPRIV NO
JAVAIDPRIV NO
JAVASYSPRIV NO
JAVADEBUGPRIV NO
ROLE PASSWORD
------------------------------ --------
EJBCLIENT NO
JAVA_ADMIN NO
JAVA_DEPLOY NO
CTXAPP NO
XDBADMIN NO
AUTHENTICATEDUSER NO
OLAP_DBA NO
SALES_HISTORY_ROLE NO
TK_ROLE NO
TK_TAB NO
TK_PRIV NO
ROLE PASSWORD
------------------------------ --------
TK_TK YES
TK_HH YES
35 rows selected.
SQL>
===========================================================================================
MODIFYING ROLES
===========================================================================================
SQL> ALTER ROLE TK_PRIV IDENTIFIED BY KUMAR;
Role altered.
SQL> ALTER ROLE TK_HH NOT IDENTIFIED;
Role altered.
SQL> ALTER ROLE TK_TK IDENTIFIED BY EXTERNALLY;
Role altered.
SQL> SELECT * 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
GATHER_SYSTEM_STATISTICS NO
LOGSTDBY_ADMINISTRATOR NO
ROLE PASSWORD
------------------------------ --------
AQ_ADMINISTRATOR_ROLE NO
AQ_USER_ROLE NO
GLOBAL_AQ_USER_ROLE GLOBAL
OEM_MONITOR NO
HS_ADMIN_ROLE NO
WKUSER NO
WM_ADMIN_ROLE NO
JAVAUSERPRIV NO
JAVAIDPRIV NO
JAVASYSPRIV NO
JAVADEBUGPRIV NO
ROLE PASSWORD
------------------------------ --------
EJBCLIENT NO
JAVA_ADMIN NO
JAVA_DEPLOY NO
CTXAPP NO
XDBADMIN NO
AUTHENTICATEDUSER NO
OLAP_DBA NO
SALES_HISTORY_ROLE NO
TK_ROLE NO
TK_TAB NO
TK_PRIV YES
ROLE PASSWORD
------------------------------ --------
TK_TK YES
TK_HH NO
35 rows selected.
SQL>
===========================================================================================
REVOKING ROLE FROM USERS
===========================================================================================
SQL> SELECT* FROM DBA_ROLE_PRIVS WHERE GRANTEE='RAVI';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
RAVI TK_TAB NO YES
RAVI TK_PRIV NO YES
RAVI TK_ROLE NO YES
SQL> REVOKE TK_TAB FROM RAVI;
Revoke succeeded.
SQL> SELECT* FROM DBA_ROLE_PRIVS WHERE GRANTEE='RAVI';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
RAVI TK_PRIV NO YES
RAVI TK_ROLE NO YES
============================================================================================ REMOVING ROLES
============================================================================================
SQL> SELECT * 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
GATHER_SYSTEM_STATISTICS NO
LOGSTDBY_ADMINISTRATOR NO
ROLE PASSWORD
------------------------------ --------
AQ_ADMINISTRATOR_ROLE NO
AQ_USER_ROLE NO
GLOBAL_AQ_USER_ROLE GLOBAL
OEM_MONITOR NO
HS_ADMIN_ROLE NO
WKUSER NO
WM_ADMIN_ROLE NO
JAVAUSERPRIV NO
JAVAIDPRIV NO
JAVASYSPRIV NO
JAVADEBUGPRIV NO
ROLE PASSWORD
------------------------------ --------
EJBCLIENT NO
JAVA_ADMIN NO
JAVA_DEPLOY NO
CTXAPP NO
XDBADMIN NO
AUTHENTICATEDUSER NO
OLAP_DBA NO
SALES_HISTORY_ROLE NO
TK_ROLE NO
TK_TAB NO
TK_PRIV YES
ROLE PASSWORD
------------------------------ --------
TK_TK YES
TK_HH NO
35 rows selected.
SQL> DROP ROLE TK_TAB;
Role dropped.
SQL> SELECT * 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
GATHER_SYSTEM_STATISTICS NO
LOGSTDBY_ADMINISTRATOR NO
ROLE PASSWORD
------------------------------ --------
AQ_ADMINISTRATOR_ROLE NO
AQ_USER_ROLE NO
GLOBAL_AQ_USER_ROLE GLOBAL
OEM_MONITOR NO
HS_ADMIN_ROLE NO
WKUSER NO
WM_ADMIN_ROLE NO
JAVAUSERPRIV NO
JAVAIDPRIV NO
JAVASYSPRIV NO
JAVADEBUGPRIV NO
ROLE PASSWORD
------------------------------ --------
EJBCLIENT NO
JAVA_ADMIN NO
JAVA_DEPLOY NO
CTXAPP NO
XDBADMIN NO
AUTHENTICATEDUSER NO
OLAP_DBA NO
SALES_HISTORY_ROLE NO
TK_ROLE NO
TK_PRIV YES
TK_TK YES
ROLE PASSWORD
------------------------------ --------
TK_HH NO
34 rows selected.
No comments:
Post a Comment