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

 

 

   
 

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 object_priv_list|ALL PRIVILEGES
[(column_list)] 
ON [schema.]object|DIRECTORY dir_name|JAVA SOURCE|RESOURCE
    [schema.]object
TO user|role|PUBLIC
[WITH GRANT OPTION]

For example:

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