How many types of system privileges are there, Can we add or delete privileges?

How many types of system privileges are there, Can we add or
delete privileges?

Question Posted / nithya

2 Answers
6711 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

How many types of system privileges are there, Can we add or delete privileges?..

Answer / siva rajani

Managing User Privileges and Roles

This chapter explains how to control the ability to execute
system operations and access to schema objects using
privileges and roles. The following topics are included:

* Identifying User Privileges
* Managing User Roles
* Granting User Privileges and Roles
* Revoking User Privileges and Roles
* Granting Roles Using the Operating System or Network
* Listing Privilege and Role Information

Identifying User Privileges

This section describes Oracle user privileges, and includes
the following topics:

* System Privileges
* Object Privileges

A user privilege is a right to execute a particular type of
SQL statement, or a right to access another user's object.
Oracle also provides shortcuts for grouping privileges that
are commonly granted or revoked together.

System Privileges

There are over 80 distinct system privileges. Each system
privilege allows a user to perform a particular database
operation or class of database operations.
lists all system privileges and the operations that they
permit.

For security reasons, system privileges do not allow users
to access the data dictionary. Hence, users with ANY
privileges (such as UPDATE ANY TABLE, SELECT ANY TABLE or
CREATE ANY INDEX) cannot access dictionary tables and views
that have not been granted to PUBLIC.

System Privileges

System Privilege Operations Permitted
ANALYZE

ANALYZE ANY
- Analyze any table, cluster, or index in the database.

AUDIT
AUDIT ANY

- Audit any schema object in the database.

AUDIT SYSTEM

- Enable and disable statement and privilege audit options.

CLUSTER

CREATE CLUSTER

- Create a cluster in own schema.

CREATE ANY CLUSTER

- Create a cluster in any schema. Behaves similarly to
CREATE ANY TABLE.

ALTER ANY CLUSTER

- Alter any cluster in the database.

DROP ANY CLUSTER

- Drop any cluster in the database.

DATABASE

ALTER DATABASE

- Alter the database; add files to the operating system via
Oracle, regardless of operating system privileges.

DATABASE LINK

CREATE DATABASE LINK

- Create private database links in own schema.

INDEX

CREATE ANY INDEX

- Create an index in any schema on any table.

ALTER ANY INDEX

- Alter any index in the database.

DROP ANY INDEX

- Drop any index in the database.

LIBRARY

CREATE LIBRARY

- Create callout libraries in own schema.

CREATE ANY LIBRARY
- Create callout libraries in any schema

DROP LIBRARY

- Drop callout libraries in own schema.

DROP ANY LIBRARY
- Drop callout libraries in any schema.

PRIVILEGE

GRANT ANY PRIVILEGE

- Grant any system privilege (not object privileges).

PROCEDURE

CREATE PROCEDURE

- Create stored procedures, functions, and packages in own
schema.

CREATE ANY PROCEDURE

- Create stored procedures, functions, and packages in any
schema. (Requires that user also have ALTER ANY TABLE,
BACKUP ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, INSERT
ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, or GRANT ANY
TABLE privilege.)

ALTER ANY PROCEDURE

- Compile any stored procedure, function, or package in any
schema.

DROP ANY PROCEDURE

- Drop any stored procedure, function, or package in any schema.

EXECUTE ANY PROCEDURE

- Execute any procedure or function (stand-alone or
packaged), or reference any public package variable in any
schema.

PROFILE
CREATE PROFILE
- Create profiles.

ALTER PROFILE

- Alter any profile in the database.

DROP PROFILE

- Drop any profile in the database.

ALTER RESOURCE COST

- Set costs for resources used in all user sessions.

PUBLIC DATABASE LINK

CREATE PUBLIC DATABASE LINK

- Create public database links.

DROP PUBLIC DATABASE LINK

- Drop public database links.

PUBLIC SYNONYM

CREATE PUBLIC SYNONYM

- Create public synonyms.

DROP PUBLIC SYNONYM

- Drop public synonyms.

ROLE

CREATE ROLE

- Create roles.

ALTER ANY ROLE

- Alter any role in the database.

DROP ANY ROLE

- Drop any role in the database.

GRANT ANY ROLE

- Grant any role in the database.

ROLLBACK SEGMENT

CREATE ROLLBACK SEGMENT

- Create rollback segments.

ALTER ROLLBACK SEGMENT

- Alter rollback segments.

DROP ROLLBACK SEGMENT

- Drop rollback segments.

SESSION

CREATE SESSION

- Connect to the database.

ALTER SESSION

- Issue ALTER SESSION statements.

RESTRICTED SESSION

- Connect when the database has been started using STARTUP
RESTRICT. (The OSOPER and OSDBA roles contain this privilege.)

SEQUENCE

CREATE SEQUENCE

- Create a sequence in own schema.

CREATE ANY SEQUENCE

- Create any sequence in any schema.

ALTER ANY SEQUENCE

- Alter any sequence in any schema.

DROP ANY SEQUENCE

- Drop any sequence in any schema.

SELECT ANY SEQUENCE

- Reference any sequence in any schema.

SNAPSHOT

CREATE SNAPSHOT

- Create snapshots in own schema. (User must also have the
CREATE TABLE privilege.)

CREATE SNAPSHOT

- Create snapshots in any schema. (User must also have the
CREATE ANY TABLE privilege.)

ALTER SNAPSHOT

- Alter any snapshot in any schema.

DROP ANY SNAPSHOT

- Drop any snapshot in any schema.

SYNONYM

CREATE SYNONYM

- Create a synonym in own schema.

CREATE SYNONYM

- Create any synonym in any schema.

DROP ANY SYNONYM

- Drop any synonym in any schema.

SYSTEM

ALTER SYSTEM

- Issue ALTER SYSTEM statements.

TABLE

CREATE TABLE

- Create tables in own schema. Also allows grantee to create
indexes (including those for integrity constraints) on table
in own schema. (The grantee must have a quota for the
tablespace or the UNLIMITED TABLESPACE privilege.)

CREATE ANY TABLE

- Create tables in any schema. (If grantee has CREATE ANY
TABLE privilege and creates a table in another user's
schema, the owner must have space quota on that tablespace.
The table owner need not have the CREATE [ANY] TABLE privilege.)

ALTER ANY TABLE

- Alter any table in any schema and compile any view in any
schema.

BACKUP ANY TABLE

- Perform an incremental export using the Export utility of
tables in any schema.

DROP ANY TABLE

- Drop or truncate any table in any schema.

LOCK ANY TABLE

- Lock any table or view in any schema.

COMMENT ANY TABLE

- Comment on any table, view, or column in schema.

SELECT ANY TABLE

- Query any table, view, or snapshot in any schema.

INSERT ANY TABLE

- Insert rows into any table or view in any schema.

UPDATE ANY TABLE

- Update rows in any table or view in any schema.

DELETE ANY TABLE

- Delete rows from any table or view in any schema.

TABLESPACE

CREATE TABLESPACE

- Create tablespaces; add files to the operating system via
Oracle, regardless of the user's operating system privileges.

ALTER TABLESPACE

- Alter tablespaces; add files to the operating system via
Oracle, regardless of the user's operating system privileges.

MANAGE TABLESPACE

- Take any tablespace offline, bring any tablespace online,
and begin and end backups of any tablespace.

DROP TABLESPACE

- Drop tablespaces.

UNLIMITED TABLESPACE

- Use an unlimited amount of any tablespace. This privilege
overrides any specific quotas assigned. If revoked, the
grantee's schema objects remain but further tablespace
allocation is denied unless allowed by specific tablespace
quotas. This system privilege can be granted only to users
and not to roles. In general, specific tablespace quotas are
assigned instead of granting this system privilege.

TRANSACTION

FORCE TRANSACTION

- Force the commit or rollback of own in-doubt distributed
transaction in the local database.

FORCE ANY TRANSACTION

- Force the commit or rollback of any in-doubt distributed
transaction in the local database.

TRIGGER

CREATE TRIGGER

- Create a trigger in own schema.

CREATE ANY TRIGGER

- Create any trigger in any schema associated with any table
in any schema.

ALTER ANY TRIGGER

- Enable, disable, or compile any trigger in any schema.

DROP ANY TRIGGER

- Drop any trigger in any schema.

USER

CREATE ANY USER

- Create users; assign quotas on any tablespace, set default
and temporary tablespaces, and assign a profile as part of a
CREATE USER statement.

BECOME ANY USER

- Become another user. (Required by any user performing a
full database import.)

ALTER USER

- Alter other users: change any user's password or
authentication method, assign tablespace quotas, set default
and temporary tablespaces, assign profiles and default
roles, in an ALTER USER statement. (Not required to alter
own password.)

DROP USER

- Drop another user.

VIEW

CREATE VIEW

- Create a view in own schema.

CREATE ANY VIEW

- Create a view in any schema. To create a view in another
user's schema, you must have CREATE ANY VIEW privileges, and
the owner must have the required privileges on the objects
referenced in the view.

DROP ANY VIEW

- Drop any view in any schema.

System Privilege Restrictions

The dictionary protection mechanism prevents unauthorized
users from accessing dictionary objects.

Access to dictionary objects is restricted to the users
SYSDBA and SYSOPER. System privileges providing access to
objects in other schemas do not give you access to
dictionary objects. For example, the SELECT ANY TABLE
privilege allows you to access views and tables in other
schemas, but does not enable you to select dictionary
objects (base tables, views, packages, and synonyms).

Also, attempting to connect with the SQL*Plus command
connect SYS/password results in failure. However, the
following two SQL*Plus commands are valid:

connect SYS/password as SYSDBA
connect SYS/password as SYSOPER

Use the 07_DICTIONARY_ACCESSIBILITY parameter (default=TRUE)
to revert the behavior to Oracle7 (and remove the
restrictions on system privileges).

See Also: For details about the 07_DICTIONARY_ACCESSIBILITY
parameter

Accessing Frequently Used Dictionary Objects

Users with explicit object privileges and the SYSDBA can
access dictionary objects. If, however, you need access to
dictionary objects and do not have explicit object
privileges, you can be granted the following roles:

* SELECT_CATALOG_ROLE

Enables users to SELECT all exported catalog views and
tables granted to this role. Grant this role to users who
must access all exported views and tables in the data
dictionary.

* EXECUTE_CATALOG_ROLE

Provides EXECUTE privilege on exported packages in the
dictionary.

* DELETE_CATALOG_ROLE

Enables users to delete records from the AUD$ table.

These roles enable database administrators to access certain
objects in the dictionary while maintaining dictionary
security.
etc.,

--------------------------------
Details See Link :
http://download.oracle.com/docs/cd/A58617_01/server.804/a58397/ch21.htm

Is This Answer Correct ?

4 Yes

0 No

How many types of system privileges are there, Can we add or delete privileges?..

Answer / sarika jayadave

There are over 80 distinct system privileges. Each system
privilege allows a user to perform a particular database
operation or class of database operations.

Such as:- 1. ANALYZE ANY
2. CREATE CLUSTER
3. CREATE ANY CLUSTER
4. ALTER ANY CLUSTER
5. DROP ANY CLUSTER
6. ALTER DATABASE
7. CREATE ANY INDEX
8. ALTER ANY INDEX
9. DROP ANY INDEX
10. GRANT ANY PRIVILEGE
11. CREATE PROCEDURE
12. CREATE ANY PROCEDURE
13. ALTER ANY PROCEDURE
14. DROP ANY PROCEDURE
15. EXECUTE ANY PROCEDURE
16. CREATE TABLE
17. CREATE ANY TABLE
18. ALTER ANY TABLE
19. BACKUP ANY TABLE
20. DROP ANY TABLE
21. LOCK ANY TABLE
22. COMMENT ANY TABLE
23. SELECT ANY TABLE
24. INSERT ANY TABLE
25. UPDATE ANY TABLE
26. DELETE ANY TABLE
etc....................

Granting revoking of privilages is possible but adding new
privileges or deleting existing privileges is not possible.

Is This Answer Correct ?

3 Yes

0 No

Post New Answer