Oracle Database 权限与角色管理
授予用户的系统权限SQL> grant create table,create sequence,create view to tpcc;
Grant succeeded.
查询授予用户的系统权限
SQL> col grantee for a20
SQL> col privilege for a30
SQL> col admin_option for a15
SQL> select * from dba_sys_privs where grantee ='TPCC';
GRANTEE PRIVILEGE ADMIN_OPTION
--------------- ------------------------------ ---------------
TPCC CREATE TABLE NO
TPCC UNLIMITED TABLESPACE NO
TPCC CREATE VIEW NO
TPCC ALTER SESSION NO
TPCC CREATE SEQUENCE NO
撤销授予用户的系统权限
SQL> revoke create sequence from tpcc;
Revoke succeeded.
SQL> select * from dba_sys_privs where grantee ='TPCC';
GRANTEE PRIVILEGE ADMIN_OPTION
--------------- ------------------------------ ---------------
TPCC CREATE TABLE NO
TPCC UNLIMITED TABLESPACE NO
TPCC CREATE VIEW NO
TPCC ALTER SESSION NO
授予用户的对象权限
SQL> grant select on scott.emp to tpcc;
Grant succeeded.
查询授予用户的对象权限
SQL> col owner for a20
SQL> col table_name for a20
SQL> col grantee for a15
SQL> col grantor for a15
SQL> col privilege for a30
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
TPCC SCOTT EMP SCOTT SELECT
撤销授予用户的对象权限
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
查询数据库的角色
SQL> col role for a30
SQL> select * from dba_roles;
ROLE PASSWORD_REQUIRED AUTHENTICATION_TYPE
------------------------------ ------------------------ ---------------------------------
CONNECT NO NONE
RESOURCE NO NONE
DBA NO NONE
SELECT_CATALOG_ROLE NO NONE
EXECUTE_CATALOG_ROLE NO NONE
DELETE_CATALOG_ROLE NO NONE
EXP_FULL_DATABASE NO NONE
IMP_FULL_DATABASE NO NONE
LOGSTDBY_ADMINISTRATOR NO NONE
DBFS_ROLE NO NONE
AQ_ADMINISTRATOR_ROLE NO NONE
查询授予角色的权限
SQL> select * from role_sys_privs where role in ('CONNECT','RESOURCE');
ROLE PRIVILEGE ADMIN_OPTION
------------------------------ ------------------------------ ---------------
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE TYPE NO
CONNECT CREATE SESSION NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE INDEXTYPE NO
查询授予用户的角色
SQL> col admin_option for a15
SQL> col default_role for a15
SQL> col granted_role for a30
SQL> select * from dba_role_privs where grantee = 'TPCC';
GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
--------------- ------------------------------ --------------- ---------------
TPCC RESOURCE NO YES
TPCC CONNECT NO YES
查询用户获得的权限
SQL> conn tpcc/tpcc
Connected.
SQL> select * from session_privs;
PRIVILEGE
------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
PRIVILEGE
------------------------------
CREATE INDEXTYPE
页:
[1]