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.
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 a list of all privileges that have been granted without any column restrictions. The GRANTABLE column will tell you whether or not 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 also need to know about any column-level privileges that have been granted. These will only be reflected in ALL_COL_PRIVS, so you must query that as well. This query looks similar to the previous query, except that it also returns the column names:
SELECT grantee, privilege, column_name, grantable FROM all_col_privs WHERE table_schema = 'owner_name' AND table_name = 'object_name';
The SHOW_SECURITY script you are about to see unions together the two queries we saw in the previous section.
The following script shows you the grants that have been made on any table you own. You can also see grants made on tables owned by other users, so long as those grants apply to you. The two queries you saw previously have been unioned together, and the columns have been concatenated together to produce readable output. The table name and grantee are shown in the header, which changes each time the grantee changes.
--DESCRIPTION
--This script displays information about security on a table.
--
--USAGE
-- @SHOW_SECURITY [owner.]table_name
--
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET PAGESIZE 9999
SET HEADING OFF
--Dissect the input argument, and get the owner name and
--table name into two separate substitution variables.
--The owner name defaults to the current user.
SET TERMOUT OFF
DEFINE s_owner_name = ' '
DEFINE s_synonym_name = ' '
COLUMN owner_name NOPRINT NEW_VALUE s_owner_name
COLUMN table_name NOPRINT NEW_VALUE s_table_name
SELECT
DECODE(INSTR('&&1','.'),
0,USER, /*Default to current user.*/
UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))) owner_name,
DECODE(INSTR('&&1','.'),
0,UPPER('&&1'), /*Only the table name was passed in.*/
UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))) table_name
FROM dual;
SET TERMOUT ON
COLUMN grantee NOPRINT NEW_VALUE s_grantee
BREAK ON grantee SKIP PAGE
TTITLE LEFT 'PRIVILEGES GRANTED TO ' s_grantee -
' ON ' s_owner_name '.' s_table_name
--Execute a query to show privileges granted at the table level.
SELECT grantee,
' ' || privilege ||
DECODE(grantable, 'YES', ' with grant option', '') privilege
FROM all_tab_privs
WHERE table_schema = '&&s_owner_name'
AND table_name = '&&s_table_name'
UNION
SELECT grantee,
' ' || privilege || ' of column ' || column_name
|| DECODE(grantable, 'YES', ' with grant option', '') privilege
FROM all_col_privs
WHERE table_schema = '&&s_owner_name'
AND table_name = '&&s_table_name'
ORDER BY grantee, privilege;
--Reset everything back to its default.
CLEAR COLUMNS
CLEAR BREAK
UNDEFINE s_owner_name
UNDEFINE s_table_name
SET VERIFY ON
SET FEEDBACK ON
SET HEADING ON
SET PAGESIZE 24The following example shows the results of running SHOW_SECURITY against the EMPLOYEE table, after first granting some access to other users:
SQL> @show_security employee
PRIVILEGES GRANTED TO PUBLIC ON JEFF.EMPLOYEE
SELECT
UPDATE of column EMPLOYEE_TERMINATION_DATE
PRIVILEGES GRANTED TO USER_A ON JEFF.EMPLOYEE
DELETE
INSERT of column EMPLOYEE_BILLING_RATE with grant option
INSERT of column EMPLOYEE_HIRE_DATE with grant option
INSERT of column EMPLOYEE_ID with grant option
INSERT of column EMPLOYEE_NAME with grant option
SELECT
PRIVILEGES GRANTED TO USER_B ON JEFF.EMPLOYEE
UPDATE of column EMPLOYEE_NAME
UPDATE of column EMPLOYEE_TERMINATION_DATE
PRIVILEGES GRANTED TO USER_C ON JEFF.EMPLOYEE
INSERT
INSERT of column EMPLOYEE_ID
SELECT
UPDATE