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

Oracle Tips by Burleson Consulting

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.

Also see how to grant select on specific column example.

Here is an example:

GRANT  update (emp_name)
ON  edwin.emp
TO  joan;

As the DBA, you can access the DBA_COL_PRIVS view for information on the column-level object privileges granted to users. Table 18 shows the contents of the DBA_COL_PRIVS view.

Column

Definition

grantee

Oracle login name or role which received the privilege

owner

Owner of the table

table_name

Name of the table

column_name

Name of the column

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 18: Contents of the DBA_COL_PRIVS data dictionary view.

Users can access the USER_COL_PRIVS_RECD for information on column-level object privileges that have been granted to them. The ALL_COL_PRIVS_RECD includes information on all column privileges that have been granted to them or to PUBLIC. The format of the USER_TAB_PRIVS_RECD view is shown in Table 19.

Column

Definition

owner

Owner of the table

table_name

Name of the table, view, or sequence

column_name

Name of the column

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 column-level object privilege

Table 19: USER TAB_PRIVS_RECD data dictionary view.

Users can access the USER_COL_PRIVS_MADE for information on column privileges that they have granted to others. The corresponding ALL_COL_PRIVS_MADE includes information on all columns where the user is the owner or the grantor. The contents of the USER_COL_PRIVS_MADE view are shown in Table 20.

Column

Definition

grantee

Oracle user granted the privilege

table_name

Name of the table

column_name

Name of the column

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 column-level object privilege

Table 20: USER_COL_PRIVS_MADE data dictionary view.

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

View Grants

Views can have the SELECT, INSERT, UPDATE and DELETE grants issued against them. In order to perform SELECT, INSERT, UPDATE or DELETE operations against views (where it is allowed) you must grant the privileges for the underlying tables to the users you wish to have these privileges.

The information on grants made to views is located in the same views as for tables.

Other Grants

The only allowed grant for sequences is SELECT. For procedures, functions, packages, libraries and user defined types you may only grant EXECUTE privileges. The only allowed grant for a directory is READ, it is the only object which has a READ grant.

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, the object privileges are revoked when the grantor's privileges are revoked.

It is important to note 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, the privilege 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 seen in Listing 37

Listing  37: Syntax for revoking a system privilege.

In this format roles are counted the same as system privileges and are also grouped with users.

Here are some examples:

REVOKE   create table
FROM   judy;

REVOKE create table
FROM developer_role;

REVOKE dba
FROM monitor_role;

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 OBJECT PRIVILEGE system privilege.  The docs note:

To revoke an object privilege, you must fulfill one of the following conditions:

- You previously granted the object privilege to the user or role.

- You possess the GRANT ANY OBJECT PRIVILEGE system privilege that enables you to grant and revoke privileges on behalf of the object owner.

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 seen in Listing 38.

Listing 38 Syntax for revoking an object privilege.

Here is an example:

REVOKE  select
ON  mike.emp
FROM  stan;

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

 


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.