Полезная информация


Teach Yourself Oracle 8 In 21 Days

Previous chapterNext chapterContents


- Appendix C -
Oracle Roles and Privileges

This appendix lists the Oracle default roles and system privileges. Table C.1 lists the Oracle commands and the privileges necessary to run them. Table C.2 lists the default Oracle roles and the system privileges they contain.

Table C.1. Oracle commands and required privileges.

Command Required system privilege
ALTER CLUSTER ALTER ANY CLUSTER.
ALTER DATABASE ALTER DATABASE.
ALTER FUNCTION ALTER ANY PROCEDURE.
ALTER INDEX ALTER ANY INDEX.
ALTER PACKAGE ALTER ANY PROCEDURE.
ALTER PROCEDURE ALTER ANY PROCEDURE.
ALTER PROFILE To change profile resource limits, the ALTER PROFILE system privilege is required. To modify password limits and protection, the ALTER PROFILE and ALTER USER system privileges are required.
ALTER RESOURCE COST ALTER RESOURCE COST.
ALTER ROLE The ALTER ANY ROLE system privilege is required, or you must have been granted ROLE with ADMIN OPTION.
ALTER ROLLBACK SEGMENT ALTER ROLLBACK SEGMENT.
ALTER SEQUENCE The ALTER ANY SEQUENCE system privilege is required, you must have the ALTER system privilege on the sequence, or the sequence must be in your own schema.
ALTER SESSION The ALTER SESSION system privilege is required to enable and disable the SQL Trace facility or change the default label format.
ALTER SNAPSHOT The ALTER ANY SNAPSHOT system privilege is required, or a snapshot must be contained in your own schema.
ALTER SNAPSHOT LOG Only the owner of a master table or a user with the SELECT system privilege for the master table can use this command.
ALTER SYSTEM ALTER SYSTEM.
ALTER TABLE The ALTER ANY TABLE system privilege is required, the table must be contained in your own schema, or you must have the ALTER system privilege on the table.
ALTER TABLESPACE The ALTER TABLESPACE system privilege is required or, using MANAGE TABLESPACE, you can take the system offline or online, begin or end a backup, and make the system read-only or read-write.
ALTER TRIGGER The ALTER ANY TRIGGER system privilege is required, or the trigger must be in your own schema.
ALTER TYPE The ALTER ANY TYPE system privilege is required, or the object type must be in your schema and you must have CREATE TYPE or CREATE ANY TYPE system privileges.
ALTER USER The ALTER USER system privilege is required. You can change your own password without this privilege.
ALTER VIEW The ALTER ANY TABLE system privilege is required, or the view must be in your own schema.
ALTER CLUSTER ALTER ANY TABLE.
ANALYZE The ANALYZE ANY system privilege is required, or the schema object to be analyzed must be in your own schema.
AUDIT (SQL Statements) AUDIT SYSTEM.
AUDIT (Schema Objects) The AUDIT ANY system privilege is required, or the object you choose for auditing must be in your own schema.
COMMENT The COMMENT ANY TABLE system privilege is required, or the view, snapshot, or table must be in your own schema.
COMMIT No privileges are required.
CREATE CLUSTER The CREATE CLUSTER system privilege is required for your own schema; for another user's schema, you need CREATE ANY CLUSTER plus either a space quota on the tablespace containing the cluster or the UNLIMITED TABLESPACE system privilege.
CREATE CONTROLFILE The OSDBA role must be enabled.
CREATE DATABASE The OSDBA role must be enabled.
CREATE DATABASE LINK For a private link, CREATE DATABASE LINK is required. For a public link, CREATE PUBLIC DATABASE LINK is required along with the CREATE SESSION privilege on the remote database.
CREATE DIRECTORY CREATE ANY DIRECTORY.
CREATE FUNCTION The CREATE ANY PROCEDURE system privilege is required, or CREATE PROCEDURE is required if the function is in your own schema.
CREATE INDEX The owner of the schema must have the space quota on the tablespace or the UNLIMITED TABLESPACE system privilege. For your own schema, either the table or the cluster must be in the schema, you must have the INDEX privilege on the table, or you must have the CREATE ANY INDEX system privilege. To create an index in another schema, you must have the CREATE ANY INDEX system privilege.
CREATE LIBRARY The CREATE ANY LIBRARY system privilege is required. To use the library, you must have the EXECUTE object system privilege for the library.
CREATE PACKAGE The CREATE ANY PROCEDURE system privilege is required. The CREATE PROCEDURE system privilege is required if the package is in your own schema.
CREATE PACKAGE BODY The CREATE ANY PROCEDURE system privilege is required. The CREATE PROCEDURE system privilege is required if the package is in your own schema.
CREATE PROCEDURE The CREATE ANY PROCEDURE system privilege is required with the CREATE PROCEDURE system privilege if the procedure is in your own schema, or the ALTER ANY PROCEDURE system privilege to replace a procedure.
CREATE PROFILE CREATE PROFILE.
CREATE ROLE CREATE ROLE.
CREATE ROLLBACK SEGMENT The CREATE ROLLBACK SEGMENT system privilege is required, and you must have either the UNLIMITED TABLESPACE system privilege or the space quota on the tablespace.
CREATE SCHEMA You must have the necessary privileges for included statements, if any.
CREATE SEQUENCE For your own schema, the CREATE SEQUENCE system privilege is required. The CREATE ANY SEQUENCE system privilege is required for another user's schema.
CREATE SNAPSHOT The CREATE SNAPSHOT, CREATE TABLE, and CREATE VIEW system privileges are required in your own schema; the CREATE ANY SNAPSHOT system privilege is required in another schema and you must have a sufficient quota in the tablespace or UNLIMITED TABLESPACE.
CREATE SNAPSHOT LOG The CREATE TABLE system privilege is required if you own the master table, the CREATE ANY TABLE system privilege is required in another schema, and the COMMENT ANY TABLE and SELECT system privileges are required on the master table.
CREATE SYNONYM The CREATE SYNONYM system privilege is required for your own schema. For another user's schema, the CREATE ANY SYNONYM system privilege is required. For public schemas, the CREATE PUBLIC SYNONYM system privilege is required.
CREATE TABLE The CREATE TABLE system privilege is required to create a table in your own schema. The CREATE ANY TABLE system privilege is required to create a table in other schemas and requires either a sufficient quota on the tablespace or UNLIMITED TABLESPACE.
CREATE TABLESPACE The CREATE TABLESPACE system privilege is required, and the SYSTEM tablespace must contain at least two rollback segments, including the SYSTEM rollback segment.
CREATE TRIGGER The CREATE TRIGGER system privilege is required for your own schema. The CREATE ANY TRIGGER system privilege is required for other schemas.
CREATE TYPE The CREATE TYPE system privilege is required for your own schema. The CREATE ANY TYPE system privilege is required for other schemas.
CREATE TYPE BODY The CREATE TYPE system privilege is required for your own schema. The CREATE ANY TYPE system privilege is required for other schemas.
CREATE USER CREATE USER.
CREATE VIEW The CREATE VIEW system privilege is required for your own schema. The CREATE ANY VIEW system privilege is required for other schemas.
DELETE DELETE.
DROP CLUSTER The DROP ANY CLUSTER system privilege is required, or the cluster must be in your own schema.
DROP DATABASE LINK To drop your own database link, it must be in your own schema. To drop a public database link, you must have the DROP PUBLIC DATABASE LINK system privilege.
DROP DIRECTORY DROP ANY DIRECTORY.
DROP FUNCTION DROP ANY PROCEDURE.
DROP INDEX The DROP ANY INDEX system privilege is required, or the index must be in your own schema.
DROP LIBRARY DROP LIBRARY.
DROP PACKAGE DROP ANY PROCEDURE.
DROP PROCEDURE DROP ANY PROCEDURE.
DROP PROFILE DROP PROFILE.
DROP ROLE The DROP ANY ROLE system privilege is required, or you must have been granted the role with the ADMIN option.
DROP ROLLBACK SEGMENT DROP ROLLBACK SEGMENT.
DROP SEQUENCE The DROP ANY SEQUENCE system privilege is required, or the sequence must be in your own schema.
DROP SNAPSHOT The snapshot must be in your own schema, or the DROP ANY SNAPSHOT system privilege is required.
DROP SNAPSHOT LOG The DROP ANY TABLE system privilege is required, or the table must be in your own schema.
DROP SYNONYM (PRIVATE) SYNONYM must be in your own schema, or you must have the DROP ANY SYNONYM system privilege.
DROP SYNONYM (PUBLIC) SYNONYM must be in your own schema, or you must have the DROP ANY PUBLIC SYNONYM system privilege.
DROP TABLE The DROP ANY TABLE system privilege is required, or the table must be in your own schema.
DROP TABLESPACE DROP TABLESPACE.
DROP TRIGGER The DROP ANY TRIGGER system privilege is required, or the trigger must be in your own schema.
DROP TYPE The DROP ANY TYPE system privilege is required, or you must be in your own schema.
DROP TYPE BODY The object TYPE BODY must be in your own schema and you must have the CREATE TYPE or CREATE ANY TYPE system privilege or the DROP ANY TYPE system privilege.
DROP USER DROP USER.
DROP VIEW The DROP ANY VIEW system privilege is required, or the view must be in your own schema.
GRANT (SYSTEM To grant a system privilege you must have GRANT ANY
PRIVILEGES and ROLES) PRIVILEGE system privilege or you must have been granted the system privilege with the ADMIN OPTION. To grant a role you must have GRANT ANY ROLE system privileges, you must have been granted the role with the ADMIN OPTION, or you must have created the role.
INSERT This must be in your own schema, or you must have the INSERT system privilege on the table. To insert into any table, you must have the INSERT ANY TABLE system privilege.
LOCK TABLE The LOCK ANY TABLE system privilege is required, the table or view must be in your own schema, or you must have any object privilege on the table or view.
NOAUDIT AUDIT SYSTEM.
(SQL Statements)
NOAUDIT The AUDIT ANY system privilege is required or you must
(Schema Objects) be in your own schema.
RENAME The object must be in your own schema.
REVOKE (SYSTEM To revoke a system privilege, you must have been granted
PRIVILEGES and ROLES) the system privilege or role with the ADMIN OPTION. To revoke a role, you must have GRANT ANY ROLE system privileges. To revoke a schema object privilege, you must have previously granted the object privileges to each user and role.
ROLLBACK To roll back the current transaction, no privileges are necessary. To roll back an in-doubt distributed transaction, the FORCE TRANSACTION system privilege is required. To roll back an in-doubt distributed transaction originally committed by another user, the FORCE ANY TRANSACTION system privilege is required.
SAVEPOINT None necessary.
SELECT This command requires that you have the SELECT system privilege on the appropriate table or snapshot or that you have the SELECT ANY TABLE system privilege for any table or snapshot.
SET CONSTRAINT(S) The SELECT system privilege on the table is required, or you must be in your own schema.
SET ROLE You must have already been granted the roles that you name in the SET ROLE statement.
SET TRANSACTION This must be the first statement in your transaction.
TRUNCATE The DELETE TABLE system privilege is required or the table or cluster must be in your schema.
UPDATE You must have UPDATE privileges on the appropriate table or base table or view.

Table C.2. Roles and their system privileges.

Role System privileges
CONNECT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW.
DBA All system privileges with ADMIN OPTION.
DELETE_CATALOG_ROLE DELETE privileges on all dictionary packages for this role.
EXECUTE_CATALOG_ROLE EXECUTE privileges on all dictionary packages for this role.
EXP_FULL_DATABASE SELECT ANY TABLE, BACKUP ANY TABLE.
IMP_FULL_DATABASE BECOME USER.
RESOURCE CREATE CLUSTER, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER.
SELECT_CATALOG_ROLE SELECT privileges on all catalog tables and views for this role.


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.