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