Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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 software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter