Skip to Main Content
Oracle SQL*Plus: The Definitive Guide
book

Oracle SQL*Plus: The Definitive Guide

by Jonathan Gennick
March 1999
Intermediate to advanced content levelIntermediate to advanced
528 pages
14h 39m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

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

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

Jonathan Gennick
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl
Oracle SQL

Oracle SQL

Dan Hotka

Publisher Resources

ISBN: 1565925785Supplemental ContentCatalog PageErrata