Oracle SQL*Plus: The Definitive Guide

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.

Looking at Table Security

As stated earlier, there are two views you need to look at when you want to find out who has access to a particular table. 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 user_a;

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

GRANT UPDATE (employee_name) ON employee TO user_a;

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 this:

SELECT grantee, privilege, grantable
  FROM all_tab_privs
 WHERE table_schema = 'owner_name'
   AND table_name = 'object_name';

This query will give you ...