BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Unlimited Tablespace

Oracle Tips by Burleson

Use an unlimited amount of any tablespace. This privilege overrides any specific quotas assigned. If you revoke this privilege from a user, the user’s schema objects remain, but further tablespace allocation is denied unless authorized by specific tablespace quotas. You cannot grant this system privilege to roles.

Class/Privilege	Description
CREATE TRIGGER	Create a database trigger in grantee’s schema.
CREATE ANY TRIGGER	Create database triggers in any schema except SYS.
ALTER ANY TRIGGER	Enable, disable, or compile database triggers in any schema except SYS.
DROP ANY TRIGGER	Drop database triggers in any schema except SYS.
ADMINISTER DATABASE TRIGGER	Create a trigger on DATABASE. (You must also have the CREATE TRIGGER or CREATE ANY TRIGGER privilege.)
CREATE TYPE	Create object types and object type bodies in grantee’s schema.
CREATE ANY TYPE	Create object types and object type bodies in any schema except SYS.
ALTER ANY TYPE	Alter object types in any schema except SYS.
DROP ANY TYPE	Drop object types and object type bodies in any schema except SYS.
EXECUTE ANY TYPE	Use and reference object types and collection types in any schema except SYS, and invoke methods of an object type in any schema if you make the grant to a specific user. If you grant EXECUTE ANY TYPE to a role, users holding the enabled role will not be able to invoke methods of an object type in any schema.
CREATE USER	Create users. This privilege also allows the creator to assign quotas on any tablespace, set default and temporary tablespaces, and assign a profile as part of a CREATE USER statement.
ALTER USER	Alter any user. This privilege authorizes the grantee to change another user’s password or authentication method, assign quotas on any tablespace, set default and temporary tablespaces, and assign a profile and default roles.
BECOME USER	Become another user. (Required by any user performing a full database import.)
DROP USER	Drop users.
CREATE VIEW	Create views in grantee’s schema.
CREATE ANY VIEW	Create views in any schema except SYS.
DROP ANY VIEW	Drop views in any schema except SYS.
ANALYZE ANY	Analyze any table, cluster, or index in any schema except SYS.
AUDIT ANY	Audit any object in any schema except SYS using AUDIT schema_objects statements.
COMMENT ANY TABLE	Comment on any table, view, or column in any schema except SYS.
FORCE ANY TRANSACTION	Force the commit or rollback of any in-doubt distributed transaction in the local database. Induce the failure of a distributed transaction.
FORCE TRANSACTION	Force the commit or rollback of grantee’s in-doubt distributed transactions in the local database.
GRANT ANY PRIVILEGE	Grant any system privilege.
SYSDBA	Perform STARTUP and SHUTDOWN operations, ALTER DATABASE; OPEN, MOUNT, BACK UP, or change character set; CREATE DATABASE; ARCHIVELOG and RECOVERY. Includes the RESTRICTED SESSION privilege.
SYSOPER	Perform STARTUP and SHUTDOWN operations; ALTER DATABASE OPEN/MOUNT/BACKUP; ARCHIVELOG and RECOVERY. Includes the RESTRICTED SESSION privilege.

As the DBA, you can access the DBA_SYS_PRIVS view for information on the system privileges granted to users. Users can see information related to them by accessing the corresponding user view: USER_SYS_PRIVS.

Object Privileges

Object privileges define a user’s rights on existing database objects. All grants on objects take effect immediately.

To grant an object privilege, you must either be the owner of the object, have been granted WITH GRANT OPTION on that object for that privilege, or have the system privilege GRANT ANY PRIVILEGE. You can also grant access to all users by granting the privilege to PUBLIC, as shown here:

GRANT insert, update, select ON bob.emp TO derek;

As the DBA, you can access the DBA_TAB_PRIVS view for information on the object privileges granted to users. Note that, although named DBA_TAB_ PRIVS, this view also includes information on views and sequences, as well as tables. By accessing the corresponding user view, USER_TAB_PRIVS, users can see information on objects for which they are the owner, grantor, or grantee,. A user can see information for all objects for which that user or PUBLIC is the grantee with the ALL_TAB_ PRIVS view. The ALL_TAB_PRIVS view is slightly different from the USER_TAB_ PRIVS and DBA_TAB_PRIVS.

An object owner can grant the following object privileges to other users:

ALTER

DELETE

INDEX

INSERT

REFERENCES

SELECT

UPDATE

READ

EXECUTE

Grants on objects, and revocation of those grants, are valid immediately, even if a user is currently logged in to the database. The SELECT privilege can be granted only on tables, views, sequences, and snapshots. In fact, the only object-level grants on a sequence are ALTER and SELECT. The EXECUTE privilege is used for libraries, UDTs, operators, indextypes, procedures, and functions (and Java objects). Remember that procedures and functions are always executed with the permissions of the owner of that procedure or function, unless the AUTHID clause specifies AS EXECUTOR. Java objects are treated as a procedure for the purpose of grants.

By granting other users INSERT, UPDATE, DELETE, and SELECT privileges on your table, you allow them to perform the respective action on the table. By granting the ALTER privilege, you can allow another user to modify the structure of your table or sequence or create a trigger on your table. By granting users the INDEX privilege, you can allow them to create indexes on your table. INDEX grants are applicable only to tables.

The REFERENCES privilege differs from the other privileges in that is does not actually grant the right to change the table or data contained in the table; rather, it allows users to create foreign-key constraints that reference your table.

The READ privilege is only applicable to DIRECTORY objects; it cannot be granted on any other object.

Users can access the USER_TAB_PRIVS_RECD view for information on table privileges for which that user is the grantee. The corresponding ALL_TAB_PRIVS_RECD view includes all grants on objects for which that user or PUBLIC is the grantee.

Users can access the USER_TAB_PRIVS_MADE view for information on table privileges that they have granted to others. The corresponding ALL_TAB_PRIVS_MADE view includes information on all the grants that user has made, as well as grants by others on that user’s objects.

Column Privileges

Only INSERT, UPDATE, and REFERENCES privileges can be granted at the column level. When granting INSERT at the column level, you must include all the NOT NULL columns in the row.

GRANT object_priv|ALL [PRIVILEGES](column_list)
ON [schema.]object TO user_list|role|PUBLIC
[WITH GRANT OPTION]

For example:

GRANT update (emp_id,emp_name)
ON admin.emp
TO scott WITH GRANT OPTION;

As the DBA, you can access the DBA_COL_PRIVS view for information on the object privileges granted to users.

Users can access the USER_COL_PRIVS_RECD view for information on column privileges that have been granted to them. The ALL_COL_PRIVS_RECD view includes information on all column privileges that have been granted to them or to PUBLIC.

Users can access the USER_COL_PRIVS_MADE view for information on column privileges that they have granted to others. The corresponding ALL_COL_PRIVS_MADE view includes information on all columns for which the user is the owner or the grantor.

Users can access information on all columns for which they are the grantor, grantee, or owner, or for which access has been granted to PUBLIC with the corresponding ALL_TAB_PRIVS_MADE and ALL_TAB_PRIVS_RECD views

Revoking Grants

When system privileges are passed to others using the WITH ADMIN OPTION, revoking the system privileges from the original user will not cascade. The system privileges granted to others must be revoked directly. In contrast, when object privileges are passed on to others using the WITH GRANT OPTION, they are revoked when the grantor’s privileges are revoked.

Note: It is important to remember that only object privileges will cascade when revoked; system privileges will not.

When the WITH ADMIN OPTION or WITH GRANT OPTION has been included in a grant to another user, it cannot be revoked directly. You must revoke the privilege and then issue another grant without the WITH ADMIN OPTION or WITH GRANT OPTION.

The command-line syntax for revoking a system privilege is:

REVOKE system_priv_list
FROM user_list|PUBLIC;

For example:

REVOKE create table
FROM admin_dba;

In order to revoke an object privilege, you must either be the owner of the object, have granted that privilege to that user with the WITH GRANT OPTION, or have the GRANT ANY PRIVILEGE system privilege.

You can revoke object and system privileges with Server Manager or at the command line in SQL*Plus. The command-line syntax for revoking an object privilege is:

REVOKE object_priv_list|ALL PRIVILEGES
ON [schema.]object FROM user_list|role_list|PUBLIC
[CASCADE CONSTRAINTS];

For example:

REVOKE select
ON scott.emp
FROM admin_user;

When the object privilege REFERENCES has been granted, you must specify CASCADE CONSTRAINTS in order to drop the foreign-key constraints that were created.

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