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.

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.

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.

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.

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

Need an Oracle Health Check? Do you have bad performance after an upgrade?   Need to certify that your database follows best practices? BC Oracle performance gurus can quickly certify every aspect of your Oracle database and provide a complete verification that your database is fully optimized.