Teach Yourself Oracle 8 In 21 Days

- 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.