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