Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

10.8 Table Security

Information about who has been granted
access to a particular table can be found in two views,
the
all_tab_privs
view and the
all_col_privs
view. These views show you information about privileges granted on
tables you own or privileges you have been granted on tables owned by
other users. Unless you are the DBA or otherwise have access to the
dba_tab_privs_made and
dba_col_privs_made views, you cannot fully see the security
for tables you do not own.

The all_tab_privs view gives you information about
table-level grants. For example, if you issue
the following statement, it will be reflected in
all_tab_privs:

GRANT SELECT, DELETE ON employee TO jeff;

Some privileges, UPDATE and INSERT,
for example, may be restricted to certain columns of a table. For
example, the following grant allows jeff to change
just the employee's name:

GRANT UPDATE (employee_name) ON employee TO jeff;

Grants such as this, which are restricted to certain columns, are
reflected in the all_col_privs view. To get a
complete picture of the privileges you have granted on any particular
table, you need to query both of these views. The query against
all_tab_privs will look something like that in
Example 10-12.

Example 10-12. A query to list privileges granted on a table

SELECT grantee, privilege, grantable
FROM all_tab_privs
WHERE table_schema = 'GENNICK'
AND table_name = 'EMPLOYEE';
GRANTEE PRIVILEGE GRA
------------------------------ ---------------------------------------- ---
JEFF DELETE NO
JEFF SELECT NO This query will give you a list of all privileges that have been
granted without any column restrictions. The
grantable column will tell you whether the
privilege was granted using the WITH GRANT OPTION keywords. Granting
a privilege WITH GRANT OPTION allows the grantee to pass that
privilege on to others.

You will need to know about any column-level privileges that have
been granted. These will be reflected in
all_col_privs, so you must query that as well. The
query in Example 10-13 shows any column-level
privileges that have been granted on employee.

Example 10-13. A query to list privileges granted on columns of a table

COLUMN grantee FORMAT A12
COLUMN privilege FORMAT A12
COLUMN column_name FORMAT A15
SELECT grantee, privilege, column_name, grantable
FROM all_col_privs
WHERE table_schema = 'GENNICK'
AND table_name = 'EMPLOYEE';
GRANTEE PRIVILEGE COLUMN_NAME GRA
------------ ------------ --------------- ---
JEFF UPDATE EMPLOYEE_NAME NO