Oracle SQL*Plus: The Definitive Guide, 2nd Edition

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