Grant the user system permissions sql> grant create table,create sequence,create view to tpcc; grant succeeded. Querying system permissions granted to users Sql> col grantee for a20sql> col privilege for a30sql> col admin_option for a15sql> select * from dba_sys_privs where grantee = ' TPCC '; grantee privilege admin_ OPTION--------------- ------------------------------ ---------------tpcc CREATE TABLE NOTPCC UNLIMITED TABLESPACE NOTPCC CREATE VIEW NOTPCC ALTER SESSION NOTPCC create sequence no revoke the system permissions granted to the user sql> revoke create sequence from tpcc; Revoke succeeded. sql> select * from dba_sys_privs where grantee = ' TPCC '; grantee privilege &nbsP; admin_option--------------- --------------------------- --- ---------------tpcc create TABLE notpcc unlimited TABLESPACE NOTPCC CREATE VIEW NOTPCC ALTER SESSION no the object permissions granted to the user sql> grant Select on scott.emp to tpcc; grant succeeded. Querying the object permissions granted to a user sql> col owner for a20sql> col table_name for a20SQL> col grantee for a15SQL> col grantor for A15sql> col privilege for a30sql> select grantee,owner,table_name,grantor, privilege from dba_tab_privs where grantee = ' TPCC '; grantee owner TABLE_NAME grantor privilege--------------- -------------------- -------------------- --------------- ------------------------------ tpcc sys dbms_lock SYS EXECUTETPCC SCOTT EMP SCOTT select revoke the object permissions granted to the user sql> revoke select on scott.emp from tpcc; Revoke succeeded. Sql> select grantee,owner,table_name,grantor,privilege from dba_tab_privs where grantee = ' TPCC '; grantee owner TABLE_NAME GRANTOR privilege--------------- -------------------- -------------------- --------------- ------------------------------tpcc SYS DBMS_LOCK SYS execute the role of the query database Sql> col role for a30SQL> select * from dba_roles; role PASSWORD_REQUIRED authentication_type------------------------------ ------------------------ ---------------------------------connect no NONERESOURCE NO nonedba NO noneselect_catalog_ role NO noneexecute_catalog_role NO nonedelete_ catalog_role no NONEEXP_FULL_DATABASE NO NONEIMP_FULL_DATABASE no NONELOGSTDBY_ADMINISTRATOR NO NONEDBFS_ROLE NO noneaq_administrator_role no none Query the permissions granted to a role sql> select * from role_sys_privs where role in (' CONNECT ', ' ResouRCE '); role PRIVILEGE admin_ OPTION------------------------------ ------------------------------ ---------------resource CREATE SEQUENCE NORESOURCE CREATE TRIGGER NORESOURCE create cluster noresource CREATE PROCEDURE NORESOURCE create type NOCONNECT CREATE SESSION noresource CREATE OPERATOR NORESOURCE CREATE TABLE NORESOURCE CREATE INDEXTYPE no query the role granted to the user sql> Col admin_option for a15sql> col default_role for a15sql> col granted_role for a30sql> select * from dba_role_privs where grantee = ' TPCC '; grantee granted_role ADMIN_OPTION default_role--------------- ------------------------------ --------------- -------------- -tpcc resource NO YESTPCC CONNECT no &nbsP; yes queries the user for permissions Sql> conn tpcc/tpccconnected.sql > select * from session_privs; PRIVILEGE------------------------------Create sessionalter sessionunlimited tablespacecreate TABLECREATE CLUSTERCREATE VIEWCREATE SEQUENCECREATE PROCEDURECREATE Triggercreate typecreate operatorprivilege------------------------------Create indextype
Oracle Database permissions and role management
