Administration of Security

Oracle Tips by Burleson

Security in Oracle has six layers:

* The first layer of security is the raw assignment and management of users, which we have already discussed.

* The second layer is the high-level system grants that give the users already built permission to create, alter, and use database objects such as tables, indexes, and clusters.

* The third layer of security comprises the object-level grants that allow users to interact with database objects.

* The fourth layer is the column grant layer that grants or restricts access to the specific columns inside a database object. The fifth layer (new in Oracle8i) involves the use of policies and contexts to control row-level access.

* The sixth level of security deals with controlling access to system resources, such as CPUs and parallel query resources, and is accomplished through the use of profiles (Oracle7 and Oracle8) and resource plans and groups (new in Oracle8i).

Security in Oracle is becoming a very complex topic, and entire books have been written concerning this one aspect of Oracle.

User Grants and Privileges

As mentioned in the introduction to this chapter, two types of privileges can be granted: system privileges and object privileges.

* System privileges allow a user to perform a particular system wide action or to perform a particular action on a particular type of object. For example, the privilege to create a table (CREATE TABLE) or insert rows into any table (INSERT ANY TABLE) are system privileges.

* Object privileges allow a user to perform a particular action on a specific object, including tables, views, sequences, procedures, functions, and packages. For example, the privilege to insert rows into a particular table is an object privilege. Object privilege grants always include the name of the object for which the privilege is granted.

System Privileges

All users require the CREATE SESSION privilege to access the database. This privilege is automatically granted to all users when you assign the grants using Oracle Enterprise Manager. But if you create the user in command-line mode, you must remember to explicitly grant each user the CREATE SESSION system privilege, either directly or through a role, as shown here:

GRANT system_priv_listrole_list TO user_listrole_list|PUBLIC
[WITH ADMIN OPTION]
For example:
GRANT create session, create table, dba
TO scott

WITH ADMIN OPTION;

System privileges can be granted to other users when the grant made includes the WITH ADMIN OPTION.

There are more than 100 distinct privileges, most of which are self-explanatory. Table 9.1 lists all the system privileges.

Table 9.1 System Privileges

Class/Privilege	Description
CREATE CLUSTER	Create clusters in grantee’s schema.
CREATE ANY CLUSTER	Create a cluster in any schema except SYS. Behaves similarly to CREATE ANY TABLE.
ALTER ANY CLUSTER	Alter clusters in any schema except SYS.
DROP ANY CLUSTER	Drop clusters in any schema except SYS.
CREATE ANY CONTEXT	Create any context namespace.
DROP ANY CONTEXT	Drop any context namespace.
ALTER DATABASE	Alter the database.
ALTER SYSTEM	Issue ALTER SYSTEM statements.
AUDIT SYSTEM	Issue AUDIT sql_statements statements.
CREATE DATABASE LINK	Create private database links in grantee’s schema.
CREATE PUBLIC DATABASE LINK	Create public database links.
DROP PUBLIC DATABASE LINK	Drop public database links.
CREATE DIMENSION	Create dimensions in the grantee’s schema.
CREATE ANY DIMENSION	Create dimensions in any schema except SYS.
ALTER ANY DIMENSION	Alter dimensions in any schema except SYS.
DROP ANY DIMENSION	Drop dimensions in any schema except SYS.
CREATE ANY DIRECTORY	Create directory database objects.
DROP ANY DIRECTORY	Drop directory database objects.
CREATE INDEXTYPE	Create an indextype in the grantee’s schema.
CREATE ANY INDEXTYPE	Create an indextype in any schema except SYS.
DROP ANY INDEXTYPE	Drop an indextype in any schema except SYS.
EXECUTE ANY INDEXTYPE	Reference an indextype in any schema except SYS.
CREATE INDEX	Create in the grantee’s schema an index on any table in the grantee’s schema or a domain index.
CREATE ANY INDEX	Create in any schema except SYS a domain index or an index on any table in any schema except SYS.
ALTER ANY INDEX	Alter indexes in any schema except SYS.
DROP ANY INDEX	Drop indexes in any schema except SYS.
QUERY REWRITE	Enable rewrite using a materialized view, or create a function-based index, when that materialized view or index references tables and views that are in the grantee’s own schema.
GLOBAL QUERY REWRITE	Enable rewrite using a materialized view, or create a function-based index, when that materialized view or index references tables or views in any schema except SYS.
CREATE LIBRARY	Create external procedure/function libraries in grantee’s schema.
CREATE ANY LIBRARY	Create external procedure/function libraries in any schema except SYS.
DROP LIBRARY	Drop external procedure/function libraries in the grantee’s schema.
DROP ANY LIBRARY	Drop external procedure/function libraries in any schema except SYS.
CREATE MATERIALIZED VIEW	Create a materialized view in the grantee’s schema.
CREATE ANY MATERIALIZED VIEW	Create materialized views in any schema except SYS.
ALTER ANY MATERIALIZED VIEW	Alter materialized views in any schema except SYS.
DROP ANY MATERIALIZED VIEW	Drop materialized views in any schema except SYS.
QUERY REWRITE	Enable rewrite using a materialized view, or create a function-based index, when that materialized view or index references tables and views that are in the grantee’s own schema.
GLOBAL QUERY REWRITE	Enable rewrite using a materialized view, or create a function-based index, when that materialized view or index references tables or views in any schema except SYS.
CREATE OPERATOR	Create an operator and its bindings in the grantee’s schema.
CREATE ANY OPERATOR	Create an operator and its bindings in any schema except SYS.
DROP ANY OPERATOR	Drop an operator in any schema except SYS.
EXECUTE ANY OPERATOR	Reference an operator in any schema except SYS.
CREATE ANY OUTLINE	Create outlines that can be used in any schema that uses outlines.
ALTER ANY OUTLINE	Modify outlines.
DROP ANY OUTLINE	Drop outlines.
CREATE PROCEDURE	Create stored procedures, functions, and packages in grantee’s schema.
CREATE ANY PROCEDURE	Create stored procedures, functions, and packages in any schema except SYS.
ALTER ANY PROCEDURE	Alter stored procedures, functions, or packages in any schema except SYS.
DROP ANY PROCEDURE	Drop stored procedures, functions, or packages in any schema except SYS.
EXECUTE ANY PROCEDURE	Execute procedures or functions (standalone or packaged). Reference public package variables in any schema except SYS.
CREATE PROFILE	Create profiles.
ALTER PROFILE	Alter profiles.
DROP PROFILE	Drop profiles.
CREATE ROLE	Create roles.
ALTER ANY ROLE	Alter any role in the database.
DROP ANY ROLE	Drop roles.
GRANT ANY ROLE	Grant any role in the database.
CREATE ROLLBACK SEGMENT	Create rollback segments.
ALTER ROLLBACK SEGMENT	Alter rollback segments.
DROP ROLLBACK SEGMENT	Drop rollback segments.
CREATE SEQUENCE	Create sequences in grantee’s schema.
CREATE ANY SEQUENCE	Create sequences in any schema except SYS.
ALTER ANY SEQUENCE	Alter any sequence in the database.
DROP ANY SEQUENCE	Drop sequences in any schema except SYS.
SELECT ANY SEQUENCE	Reference sequences in any schema except SYS.
CREATE SESSION	Connect to the database.
ALTER RESOURCE COST	Set costs for session resources.
ALTER SESSION	Issue ALTER SESSION statements.
RESTRICTED SESSION	Log on after the instance is started using the SQL*Plus STARTUP RESTRICT statement.
CREATE SNAPSHOT	Create snapshots in grantee’s schema.
CREATE ANY SNAPSHOT	Create snapshots in any schema except SYS.
ALTER ANY SNAPSHOT	Alter any snapshot in the database.
DROP ANY SNAPSHOT	Drop snapshots in any schema except SYS.
GLOBAL QUERY REWRITE	Enable rewrite using a snapshot, or create a function-based index, when that snapshot or index references tables or views in any schema except SYS.
QUERY REWRITE	Enable rewrite using a snapshot, or create a function-based index, when that snapshot or index references tables and views that are in the grantee’s own schema.
CREATE SYNONYM	Create synonyms in grantee’s schema.
CREATE ANY SYNONYM	Create private synonyms in any schema except SYS.
CREATE PUBLIC SYNONYM	Create public synonyms.
DROP ANY SYNONYM	Drop private synonyms in any schema except SYS.
DROP PUBLIC SYNONYM	Drop public synonyms.
CREATE ANY TABLE	Create tables in any schema except SYS. The owner of the schema containing the table must have space quota on the tablespace to contain the table.
ALTER ANY TABLE	Alter any table or view in the schema.
BACKUP ANY TABLE	Use the Export utility to incrementally export objects from the schema of other users.
DELETE ANY TABLE	Delete rows from tables, table partitions, or views in any schema except SYS.
DROP ANY TABLE	Drop or truncate tables or table partitions in any schema except SYS.
INSERT ANY TABLE	Insert rows into tables and views in any schema except SYS.
LOCK ANY TABLE	Lock tables and views in any schema except SYS.
UPDATE ANY TABLE	Update rows in tables and views in any schema except SYS.
SELECT ANY TABLE	Query tables, views, or snapshots in any schema except SYS.
CREATE TABLESPACE	Create tablespaces.
ALTER TABLESPACE	Alter tablespaces.
DROP TABLESPACE	Drop tablespaces.
MANAGE TABLESPACE	Take tablespaces offline and online and begin and end tablespace backups.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books