Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle Concepts - Unlimited Amount of Tablespace Privilege

Oracle Tips by Burleson Consulting

UNLIMITED TABLESPACE

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. 

FOR TRIGGERS:

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

FOR TYPES:

Privilege

Description

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.

FOR USERS:

Privilege

Description

CREATE USER

Create users. This privilege also allows the creator to assign quotas on any tablespace, set default and temporary tablespaces, 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 

FOR VIEWS:

Privilege

Description

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 

MISCELLANEOUS  PRIVILEGES:

Privilege

Description

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 distributedtransaction in the local database Induce the failure of adistributed 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

Table 13: Oracle database system privileges

As the DBA, you can access the DBA_SYS_PRIVS view for information on the system privileges granted to users. The format of this view is shown in Table 14.

Column

Definition

grantee

Oracle login name or role that received the privilege

privilege

The system privilege granted to the user or role

admin_option

Indicates YES if the grantee can pass along the privilege and NO if the grantee cannot pass along the system privilege

Table 14: Contents of the DBA_SYS_PRIVS data dictionary view.

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 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. Listing 37 shows the syntax for the GRANT command used to grant a table-level object privilege.

Listing 37: Syntax for the GRANT command used for a table-level or snapshot-level grant.

Here is an example:

GRANT 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. You should note that although it is named DBA_TAB_PRIVS, it also includes information on views and sequences, as well as tables. Table 14 shows the contents of this view.

Column

Definition

grantee

Oracle login name or role that received the privilege

owner

Owner of the table

table_name

Name of the table, view, or sequence

grantor

Oracle login name of the person granting the privilege

privilege

System privilege granted to the user

grantable

Indicates YES if the grantee can pass along the privilege and NO if the grantee cannot pass along the system privilege

Table 14: Contents of the DBA_TAB_PRIVS data dictionary view.

Users can see information on objects where they are the owner, grantor, or grantee by accessing the corresponding user view USER_TAB_PRIVS. Users can see information for all objects where that user or PUBLIC is the grantee with the ALL_TAB_PRIVS view. The ALL_TAB_PRIVS view is slightly different than the USER_TAB_PRIVS or DBA_TAB_PRIVS view. The contents of the ALL_TAB_PRIVS view are shown in Table 15.

Column

Definition

grantee

Oracle login name or role that received the privilege

grantor

Oracle login name of the person granting the privilege

owner

Owner of the table

table_schema

Schema of the object owner

table_name

Name of the table, view, or sequence

privilege

System privilege granted to the user

grantable

Indicates YES if the grantee can pass along the privilege and NO if the grantee cannot pass along the system privilege

Table 15: Contents of the ALL_TAB_PRIVS data dictionary view.

A table owner can grant the following object privileges to other users:

* ALTER

* DELETE

* INDEX

* INSERT

* REFERENCES

* SELECT

* UPDATE

* EXECUTE (for stored functions, procedures and packages)

All grants on objects and revoking of those grants are valid immediately, even if a user is currently logged into the database. The SELECT privilege can only be granted on tables, views, and snapshots. The EXECUTE privilege is used for packages, procedures and functions. Remember that packages, procedures and functions are always executed with the permissions of the owner of that package, procedure or function.

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

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

Users can access the USER_TAB_PRIVS_RECD for information on table privileges where that user is the grantee. The corresponding ALL_TAB_PRIVS_RECD view includes all grants on objects where that user or PUBLIC is the grantee. Table 16 shows the contents of the USER_TAB_PRIVS_RECD view.

Column

Definition

OWNER

Owner of the table

table_name

Name of the table, view, or sequence

grantor

Oracle login name of the person granting the privilege

privilege

System privilege granted to the user

grantable

Indicates YES if the grantee can pass along the privilege and NO if the grantee cannot pass along the object privilege

Table 16: Contents of the USER_TAB_PRIVS_RECD data dictionary view.

Users can access the USER_TAB_PRIVS_MADE 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. Table 17 shows the contents of the USER_TAB_PRIVS_MADE view.

Column

Definition

grantee

Oracle user granted the privilege

table_name

Name of the table, view, or sequence

grantor

Oracle login name of the person granting the privilege

privilege

System privilege granted to the user

grantable

INDICATES YES IF THE GRANTEE CAN PASS ALONG THE PRIVILEGE AND NO IF THE GRANTEE CANNOT PASS ALONG THE OBJECT PRIVILEGE

Table 17: Contents of the USER_TAB_PRIVS_MADE data dictionary view.

 


This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It?s only $19.95 when you buy it directly from the publisher here.

 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.