[Laskey99] Section 15.2. System Privileges

来源：百度文库编辑：神马文学网时间：2024/04/27 21:35:05

15.2. System Privileges

There are 90 distinct system privileges defined within the Oracle8database. These values are hardcoded and are available immediatelyafter the CREATE DATABASE command has completed. There are threetypes of system privileges:

Privileges that allow you to perform some action to or in the database
Privileges that allow you to create objects within your own schema
Privileges that allow you to create, alter, or drop objects in any schema

For example, the CREATE TABLE system privilege allows you to create atable within your schema, while CREATE ANY TABLE allows you to createa table in any schema.

15.2.1. Privileges That Affect the Entire Database

Table 15.3 liststhe privileges that are traditionally reserved to the databaseadministrator and that have been granted to the DBA role. Theseprivileges allow DBAs to alter the database, create new users, definepublic database links and synonyms, and grant any privilege or rolewithout having first been granted the role with the WITH ADMIN OPTIONoption (see Chapter 13, for information aboutspecifying that option).

Table 15.3. Privileges That Affect the Entire Database

ALTER DATABASE ALTER PROFILE ALTER RESOURCE COST ALTER ROLLBACK SEGMENT ALTER SYSTEM ALTER TABLESPACE ALTER USER AUDIT SYSTEM BECOME USER CREATE LIBRARY CREATE PROFILE CREATE PUBLIC DATABASE LINK CREATE PUBLIC SYNONYM CREATE ROLE CREATE ROLLBACK SEGMENT CREATE TABLESPACE CREATE USER DROP PROFILE DROP PUBLIC DATABASE LINK DROP PUBLIC SYNONYM DROP ROLLBACK SEGMENT DROP TABLESPACE DROP USER GRANT ANY PRIVILEGE GRANT ANY ROLE MANAGE TABLESPACE RESTRICTED SESSION UNLIMITED TABLESPACE

15.2.2. Privileges That Allow You to Create Objects in Your Own Schema

Table 15.4 lists theprivileges that allow you to create your own objects in the database.These privileges have been allocated to the CONNECT and RESOURCEroles. They allow you to connect to the database; to create a table,view, or snapshot; and to create a private database link, synonym, orsnapshot.

Table 15.4. Privileges That Allow You to Create Objects in Your Own Schema

ALTER SESSION CREATE CLUSTER CREATE DATABASE LINK CREATE PROCEDURE CREATE SEQUENCE CREATE SESSION CREATE SNAPSHOT CREATE SYNONYM CREATE TABLE CREATE TRIGGER CREATE TYPE CREATE VIEW FORCE TRANSACTION

You may need more than one system privilege to perform an action. For example, you cannot create a sequence if you do not first have the CREATE SESSION privilege, which allows you to connect to the database. Also, note that you cannot create a table or index unless you have a quota on a tablespace or have the UNLIMITED TABLESPACE system privilege.

15.2.3. Privileges That Allow You to Manipulate Objects in Any Schema

Table 15.4 lists theprivileges that allow you to manipulate all other users'objects. If you have these privileges, you will not need to begranted object privileges on the objects themselves. The privilegesin this category are intended to be used primarily by DBAs andsupport personnel and have been granted to the DBA role.

Table 15.5. Privileges That Allow You to Manipulate Objects in Any Schema

ALTER ANY CLUSTER ALTER ANY INDEX ALTER ANY LIBRARY ALTER ANY PROCEDURE ALTER ANY ROLE ALTER ANY SEQUENCE ALTER ANY SNAPSHOT ALTER ANY TABLE ALTER ANY TRIGGER ALTER ANY TYPE ANALYZE ANY AUDIT ANY BACKUP ANY TABLE COMMENT ANY TABLE CREATE ANY CLUSTER CREATE ANY DIRECTORY CREATE ANY INDEX CREATE ANY LIBRARY CREATE ANY PROCEDURE CREATE ANY SEQUENCE CREATE ANY SNAPSHOT CREATE ANY SYNONYM CREATE ANY TABLE CREATE ANY TRIGGER CREATE ANY TYPE CREATE ANY VIEW DELETE ANY TABLE DROP ANY CLUSTER DROP ANY DIRECTORY DROP ANY INDEX DROP ANY LIBRARY DROP ANY PROCEDURE DROP ANY ROLE DROP ANY SEQUENCE DROP ANY SNAPSHOT DROP ANY SYNONYM DROP ANY TABLE DROP ANY TRIGGER DROP ANY TYPE DROP ANY VIEW EXECUTE ANY LIBRARY EXECUTE ANY PROCEDURE EXECUTE ANY TYPE FORCE ANY TRANSACTION INSERT ANY TABLE LOCK ANY TABLE SELECT ANY SEQUENCE SELECT ANY TABLE UPDATE ANY TABLE

15.2.4. Summary of System Privileges

Thissection contains a brief summary of all of Oracle's systemprivileges, listed in alphabetical order. Some system privileges arenew in Oracle8; this is noted in the list. A complete list of newOracle8 system privileges can also be found later in this chapter.

ALTER ANY CLUSTER: Allows you to execute the ALTER CLUSTER command for all clusters inthe database.
ALTER ANY INDEX: Allows you to execute the ALTER INDEX command for all indexes in thedatabase.
ALTER ANY LIBRARY (Oracle8): Allows you to execute the CREATE OR REPLACE LIBRARY command to alteran existing external library in another schema.
ALTER ANY PROCEDURE: Allows you to execute the ALTER FUNCTION, ALTER PACKAGE, and ALTERPROCEDURE commands to recompile any function, package, or procedurein the database. Also allows you to execute the CREATE OR REPLACEFUNCTION, CREATE OR REPLACE PACKAGE, and CREATE OR REPLACE PROCEDUREcommands on any existing function, package, or procedure in thedatabase.
ALTER ANY ROLE: Allows you to execute the ALTER ROLE command to add, change, orremove a password on the role.
ALTER ANY SEQUENCE: Allows you to execute the ALTER SEQUENCE command on any sequence inthe database.
ALTER ANY SNAPSHOT: Allows you to execute the ALTER SNAPSHOT command on any snapshot inthe database.
ALTER ANY TABLE: Allows you to execute the ALTER TABLE command on any table in thedatabase. You must have the ALTER ANY TRIGGER privilege to executethe ALTER TABLE ... ENABLE/DISABLE ALL TRIGGERS command on tablesoutside your schema.
ALTER ANY TRIGGER: Allows you to execute the ALTER TRIGGER command for all triggers inthe database. With the ALTER ANY TRIGGER privilege, you can executean ALTER TRIGGER ... ENABLE/DISABLE command, but you must also havethe ALTER ANY TABLE privilege to execute the ALTER TABLE ...ENABLE/DISABLE ALL TRIGGERS command on tables outside your schema.
ALTER ANY TYPE (Oracle8): Allows you to execute the ALTER TYPE command on any user-defined typein any schema.
ALTER DATABASE: Allows you to execute the ALTER DATABASE command.
ALTER PROFILE: Allows you to execute the ALTER PROFILE command.
ALTER RESOURCE COST: Allows you to execute the ALTER RESOURCE COST command.
ALTER ROLLBACK SEGMENT: Allows you to execute the ALTER ROLLBACK SEGMENT command.
ALTER SESSION: Allows you to execute the ALTER SESSION SET SQL_TRACE command.
ALTER SYSTEM: Allows you to execute the ALTER SYSTEM command.
ALTER TABLESPACE: Allows you to execute the ALTER TABLESPACE command.
ALTER USER: Allows you to execute the ALTER USER command. You do not need theALTER USER privilege to execute the ALTER USERuserid IDENTIFIED BYpassword command to change your own password.
ANALYZE ANY: Allows you to execute the ANALYZE command on all tables, clusters,and indexes in the database.
AUDIT ANY: Allows you to execute the AUDIT command on any database object in anyschema.
AUDIT SYSTEM: Allows you to execute the AUDIT command to audit SQL statements.
BACKUP ANY TABLE: Necessary to perform a full export.
BECOME USER: Necessary to import from a full export. There is no SQL statementthat directly uses this privilege. However, there is an internal API(Application Programming Interface) that is used by the Importutility and checks for this privilege.
COMMENT ANY TABLE: Allows you to execute the COMMENT statement for all tables in thedatabase.
CREATE ANY CLUSTER: Allows you to execute the CREATE CLUSTER statement and specify anyschema as owner.
CREATE ANY DIRECTORY (Oracle8): Allows you to execute the CREATE DIRECTORY statement. Since all BFILEdirectories are owned by SYS, there is no CREATE DIRECTORY privilege.
CREATE ANY INDEX: Allows you to execute the CREATE INDEX statement and specify anyschema as owner.
CREATE ANY LIBRARY (Oracle8): Allows you to define a library for external functions in any schema.
CREATE ANY PROCEDURE: Allows you to execute the CREATE FUNCTION, CREATE PROCEDURE, andCREATE PACKAGE statements and specify any schema as owner.
CREATE ANY SEQUENCE: Allows you to execute the CREATE SEQUENCE statement and specify anyschema as owner.
CREATE ANY SNAPSHOT: Allows you to execute the CREATE SNAPSHOT statement and specify anyschema as owner.
CREATE ANY SYNONYM: Allows you to execute the CREATE SYNONYM statement and specify anyschema as owner.
CREATE ANY TABLE: Allows you to execute the CREATE TABLE statement and specify anyschema as owner. You still must have a quota specified in theTABLESPACE clause.
CREATE ANY TRIGGER: Allows you to execute the CREATE TRIGGER statement on any table inthe database.
CREATE ANY TYPE (Oracle8): Allows you to create a user-defined type in any schema.
CREATE ANY VIEW: Allows you to execute the CREATE VIEW statement and specify anyschema as owner.
CREATE CLUSTER: Allows you to create a cluster in your own schema.
CREATE DATABASE LINK: Allows you to create a private database link.
CREATE LIBRARY (Oracle8): Allows you to execute the CREATE LIBRARY command to define a libraryin your schema for external procedures.
CREATE PROCEDURE: Allows you to create a function, package, or procedure in your ownschema.
CREATE PROFILE: Allows you to execute the CREATE PROFILE command.
CREATE PUBLIC DATABASE LINK: Allows you to execute the CREATE PUBLIC DATABASE LINK command. Thisis the one case in which there isn't a corresponding CREATE ANYversion of the privilege. There is no CREATE ANY DATABASE LINK.
CREATE PUBLIC SYNONYM: Allows you to execute the CREATE PUBLIC DATABASE LINK command.
CREATE ROLE: Allows you to execute the CREATE ROLE command.
CREATE ROLLBACK SEGMENT: Allows you to execute the CREATE ROLLBACK command.
CREATE SEQUENCE: Allows you to create a sequence in your own schema.
CREATE SESSION: Allows you to connect to the database.
CREATE SNAPSHOT: Allows you to create a snapshot in your own schema.
CREATE SYNONYM: Allows you to create a private synonym.
CREATE TABLE: Allows you to create a table in your own schema. You still must havea quota specified for the tablespace listed in the TABLESPACE clause.
CREATE TABLESPACE: Allows you to execute the CREATE TABLESPACE command.
CREATE TRIGGER: Allows you to create a trigger on a table in your schema.
CREATE TYPE (Oracle8): Allows you to create a user-defined type in your schema.
CREATE USER: Allows you to execute the CREATE USER command.
CREATE VIEW: Allows you to execute the CREATE VIEW command within your schema.
DELETE ANY TABLE: Allows you to execute the DELETE statement against any table in thedatabase.
DROP ANY CLUSTER: Allows you to execute the DROP CLUSTER command for all clusters inthe database.
DROP ANY DIRECTORY (Oracle8): Allows you to execute the DROP DIRECTORY command. Since all BFILEdirectories are owned by SYS, there is no DROP DIRECTORY privilege.
DROP ANY INDEX: Allows you to execute the DROP INDEX command for all indexes in thedatabase.
DROP ANY LIBRARY (Oracle8): Allows you to execute the DROP LIBRARY command for any library in thedatabase.
DROP ANY PROCEDURE: Allows you to execute the DROP FUNCTION, DROP PACKAGE, and DROPPROCEDURE commands for all functions, packages, and procedures in thedatabase.
DROP ANY ROLE: Allows you to execute the DROP ROLE command.
DROP ANY SEQUENCE: Allows you to execute the DROP SEQUENCE command for any sequence inany schema.
DROP ANY SNAPSHOT: Allows you to execute the DROP SNAPSHOT command for any snapshot inthe database.
DROP ANY SYNONYM: Allows you to execute the DROP SYNONYM command for any synonym in thedatabase.
DROP ANY TABLE: Allows you to execute the DROP TABLE command for any table in thedatabase. You need the DROP ANY TABLE privilege to truncate a tablethat is not in your schema.
DROP ANY TRIGGER: Allows you to execute the DROP TRIGGER command for all triggers inthe database.
DROP ANY TYPE (Oracle8): Allows you to drop any user-defined type in any schema.
DROP ANY VIEW: Allows you to execute the DROP VIEW command for all views in thedatabase.
DROP PROFILE: Allows you to execute the DROP PROFILE command.
DROP PUBLIC DATABASE LINK: Allows you to execute the DROP PUBLIC DATABASE LINK command. As withthe CREATE PUBLIC DATABASE LINK system privilege, there is nocorresponding DROP ANY DATABASE LINK system privilege.
DROP PUBLIC SYNONYM: Allows you to execute the DROP PUBLIC SYNONYM command.
DROP ROLLBACK SEGMENT: Allows you to execute the DROP ROLLBACK SEGMENT command for anytablespace in the database.
DROP TABLESPACE: Allows you to execute the DROP TABLESPACE command for any tablespacein the database.
DROP USER: Allows you to execute the DROP USER command for any user in thedatabase.
EXECUTE ANY LIBRARY (Oracle8): Allows you to execute an external function defined in any library inany schema.
EXECUTE ANY PROCEDURE: Allows you to execute any function, procedure, or package in thedatabase.
EXECUTE ANY TYPE (Oracle8): Allows you to reference and execute any type or method in any schema.
FORCE ANY TRANSACTION: Allows you to execute the COMMIT FORCE command for any in-doubttransaction in the database.
FORCE TRANSACTION: Allows you to execute the COMMIT FORCE command for any in-doubttransaction you have created.
GRANT ANY PRIVILEGE: Allows you to execute the GRANT system privilege command for any rolethat has not been granted to you WITH ADMIN OPTION.
GRANT ANY ROLE: Allows you to execute the GRANT role command for any role that hasnot been granted to you WITH ADMIN OPTION. Also allows you to executethe REVOKE role command for any role that has not been granted to youWITH ADMIN OPTION. You may need to have the ALTER USER privilege tospecify whether or not the role is a default role.
INSERT ANY TABLE: Allows you to execute the INSERT statement for any table in thedatabase. In order to insert directly into data dictionary tables,you must have the INSERT_CATALOG_ROLE role.
LOCK ANY TABLE: Allows you to execute the LOCK TABLE command for all tables in thedatabase.
MANAGE TABLESPACE: Allows you to execute the ALTER TABLESPACE command to taketablespaces offline, to take tablespaces online, or to begin or endbackups.
SELECT ANY SEQUENCE: Allows you to execute the SELECT statement to retrieve the nextsequence value for any sequence in the database, except those ownedby SYS.
SELECT ANY TABLE: Allows you to execute the SELECT statement to query any table in thedatabase. In order to select directly from tables in the datadictionary, you must have the SELECT_CATALOG_ROLE role.
UNLIMITED TABLESPACE: Allows you to have unlimited quotas in every tablespace in thedatabase. This system privilege is automatically granted when theRESOURCE role is granted.The UNLIMITED TABLESPACE system privilege isthe only system privilege that cannot be granted to a role.
UPDATE ANY TABLE: Allows you to execute the UPDATE statement for all tables and viewsin the database. In order to update tables in the data dictionarydirectly, you must have the UPDATE_CATALOG_ROLE role.

15.2.5. New System Privileges in Oracle8

Table 15.6 lists the system privileges that wereintroduced withOracle8.

Table 15.6. Oracle8 System Privileges

ALTER ANY LIBRARY ALTER ANY TYPE CREATE ANY DIRECTORY CREATE ANY LIBRARY CREATE ANY TYPE CREATE LIBRARY CREATE TYPE DROP ANY DIRECTORY DROP ANY LIBRARY DROP ANY TYPE EXECUTE ANY LIBRARY EXECUTE ANY TYPE

Create Bookmark (Key: b)Create Bookmark
Create Note or Tag (Key: t)Create Note or Tag
Download (Key: d)Download
Email This Page (Key: e)Email This Page
PrintPrint

Html View (Key: h)Html View
Zoom Out (Key: -)Zoom Out
Zoom In (Key: +)Zoom In
Toggle to Full Screen (Key: f)
Previous (Key: p)Previous
Next (Key: n)Next