如果在对用户赋权时赋予了DBA或者RESOURCE角色,那么该用户会自动被赋予UNLIMITED TABLESPACE系统权限。如果要撤销上述角色的话,也会导致UNLIMITED TABLESPACE系统权限被撤销。当然,UNLIMITED TABLESPACE权限也可以被单独撤销。
SQL> show user
USER is "SYS"
SQL> create user t identified by t;
User created.
SQL> grant connect to t;
Grant succeeded.
SQL> conn t/t
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
SQL> conn / as sysdba
Connected.
SQL> grant resource to t;
Grant succeeded.
SQL> conn t/t
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
10 rows selected.
SQL> conn / as sysdba
Connected.
SQL> select * from dba_sys_privs where grantee='RESOURCE';
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ------------------------------- ------------------------
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO
8 rows selected.
SQL> revoke resource from t;
Revoke succeeded.
SQL> conn t/t
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
SQL> conn / as sysdba
Connected.
SQL> grant dba to t;
Grant succeeded.
SQL> conn t/t
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
ALTER TABLESPACE
MANAGE TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
CREATE TABLE
CREATE ANY TABLE
ALTER ANY TABLE
BACKUP ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
COMMENT ANY TABLE
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE
CREATE CLUSTER
CREATE ANY CLUSTER
ALTER ANY CLUSTER
DROP ANY CLUSTER
CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX
CREATE SYNONYM
CREATE ANY SYNONYM
DROP ANY SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE ANY VIEW
DROP ANY VIEW
CREATE SEQUENCE
CREATE ANY SEQUENCE
ALTER ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
ALTER ANY ROLE
AUDIT ANY
ALTER DATABASE
FORCE TRANSACTION
FORCE ANY TRANSACTION
CREATE PROCEDURE
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE ANY TRIGGER
ALTER ANY TRIGGER
DROP ANY TRIGGER
CREATE PROFILE
ALTER PROFILE
DROP PROFILE
ALTER RESOURCE COST
ANALYZE ANY
GRANT ANY PRIVILEGE
CREATE MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
ALTER ANY MATERIALIZED VIEW
DROP ANY MATERIALIZED VIEW
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE TYPE
CREATE ANY TYPE
ALTER ANY TYPE
DROP ANY TYPE
EXECUTE ANY TYPE
UNDER ANY TYPE
CREATE LIBRARY
CREATE ANY LIBRARY
ALTER ANY LIBRARY
DROP ANY LIBRARY
EXECUTE ANY LIBRARY
CREATE OPERATOR
CREATE ANY OPERATOR
DROP ANY OPERATOR
EXECUTE ANY OPERATOR
CREATE INDEXTYPE
CREATE ANY INDEXTYPE
ALTER ANY INDEXTYPE
DROP ANY INDEXTYPE
UNDER ANY VIEW
QUERY REWRITE
GLOBAL QUERY REWRITE
EXECUTE ANY INDEXTYPE
UNDER ANY TABLE
CREATE DIMENSION
CREATE ANY DIMENSION
ALTER ANY DIMENSION
DROP ANY DIMENSION
MANAGE ANY QUEUE
ENQUEUE ANY QUEUE
DEQUEUE ANY QUEUE
CREATE ANY CONTEXT
DROP ANY CONTEXT
CREATE ANY OUTLINE
ALTER ANY OUTLINE
DROP ANY OUTLINE
ADMINISTER RESOURCE MANAGER
ADMINISTER DATABASE TRIGGER
MERGE ANY VIEW
ON COMMIT REFRESH
RESUMABLE
SELECT ANY DICTIONARY
DEBUG CONNECT SESSION
DEBUG ANY PROCEDURE
FLASHBACK ANY TABLE
GRANT ANY OBJECT PRIVILEGE
CREATE EVALUATION CONTEXT
CREATE ANY EVALUATION CONTEXT
ALTER ANY EVALUATION CONTEXT
DROP ANY EVALUATION CONTEXT
EXECUTE ANY EVALUATION CONTEXT
CREATE RULE SET
CREATE ANY RULE SET
ALTER ANY RULE SET
DROP ANY RULE SET
EXECUTE ANY RULE SET
EXPORT FULL DATABASE
IMPORT FULL DATABASE
CREATE RULE
CREATE ANY RULE
ALTER ANY RULE
DROP ANY RULE
EXECUTE ANY RULE
ANALYZE ANY DICTIONARY
ADVISOR
CREATE JOB
CREATE ANY JOB
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
MANAGE SCHEDULER
SELECT ANY TRANSACTION
DROP ANY SQL PROFILE
ALTER ANY SQL PROFILE
ADMINISTER SQL TUNING SET
ADMINISTER ANY SQL TUNING SET
CREATE ANY SQL PROFILE
MANAGE FILE GROUP
MANAGE ANY FILE GROUP
READ ANY FILE GROUP
CHANGE NOTIFICATION
CREATE EXTERNAL JOB
161 rows selected.
SQL> conn / as sysdba
Connected.
SQL> revoke dba from t;
Revoke succeeded.
SQL> conn t/t
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
When you grant the DBA and RESOURCE roles to a user or role with Oracle7 release 7.2.2 or later, the user or role is also granted the UNLIMITED TABLESPACE system privilege. When you revoke either role from a user or role, the UNLIMITED TABLESPACE system privilege is also revoked. The UNLIMITED TABLESPACE can also be revoked independent of the DBA and RESOURCE roles.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20750200/viewspace-704019/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/20750200/viewspace-704019/