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