Get full access to Oracle Security and 60K+ other titles, with a free 10-day trial of O'Reilly.
There are also live events, courses curated by job role, and more.
Data dictionary views may be grouped into four general categories:
Oracle provides, within the data dictionary, a view that you can access to see the composition of all of the data dictionary views. If you query the DICTIONARY view, you will find the specific names of data dictionary objects you have the privilege to access. You will not see any other objects. The DICTIONARY view is very simple, containing only two columns: table_name and comments. The column “table_name” is somewhat misleading because all of the “table” names are really views, synonyms, or other objects. None of the entities listed are actually tables. Here is a very small sample section of the DICTIONARY view, with minor formatting, so you can see what we are talking about:
SQL> COLUMN table_name FORMAT A20 SQL> COLUMN comments FORMAT A50 WORD SQL> SELECT * FROM DICTIONARY; TABLE_NAME COMMENTS -------------------- -------------------------------------------------- ALL_ALL_TABLES Description of all object and relational tables accessible to the user ALL_ARGUMENTS Arguments in object accessible to the user ALL_CATALOG All tables, views, synonyms, sequences accessible to the user
If you create a user in a version 8.0.4 database and give that user just the ability to connect to the database (the CREATE SESSION privilege), the list of table names available to that user, as obtained from the DICTIONARY view, would total 242, distributed as follows:
As you can see from this list, there is no exact correspondence in terms of numbers among the “ALL,” “DBA,” and “USER” views. For each category of users, separate views are available. For example, USER_TABLES, ALL_TABLES, and DBA_TABLES each stores information about the tables in the database, but at different levels of privilege; despite their names, all of these entities are actually views on the same tables! The view names were selected to convey their general purpose. Here are comments from the data dictionary for these three views:
USER_TABLESDescription of the user’s own tables
Description of all tables accessible to the user
Description of all tables in the database
If you look at the code the Oracle RDBMS uses to enable you to see the three types of TABLES views listed here, you will notice some interesting things:
We’ll explain these internal ($) tables later in this chapter.
So, if the SELECT lists are essentially the same, and the FROM list of tables are essentially the same, what is the difference? The difference is in the WHERE clause, which contains the limiting conditions for the data to be retrieved. You will find many examples of Oracle using the same table with different restrictions in the data dictionary view creation statements.
A view’s definition is stored in the data dictionary and is treated, in most cases, exactly like a table, but a view does not store any data. A view is merely a definition of what, and sometimes how, certain data should be retrieved. There is no distinction made in SQL DML statements between a “table” and a “view.” For practical purposes, these terms are interchangeable. For example, in the following statement:
SELECT * FROM all_tables;
there is no qualifier to identify the object ALL_TABLES as either a view or a table; in fact, ALL_TABLES is a view.
Although you can think of tables and views as being interchangeable, don’t overlook the potential impact on performance of using a view, as discussed in Chapter 3.
The DICTIONARY view is a good example of how row-level security may be implemented. Data returned from the query will be controlled at the row level by the condition clause (WHERE . ) on the view definition. The DICTIONARY view is a three-part union query. We show the text of this view, as it appears in CATALOG.SQL , to give you an idea of how you can implement control over who sees what in your database:
remark VIEW "DICTIONARY" remark Online documentation for data dictionary tables and views. remark This view exists outside of the family schema. remark /* Find the names of public synonyms for views owned by SYS that have names different from the synonym name. This allows the user to see the short-hand synonyms we have created. */ create or replace view DICTIONARY (TABLE_NAME, COMMENTS) as select o.name, c.comment$ from sys.obj$ o, sys.com$ c where o.obj# = c.obj#(+) and c.col# is null and o.owner# = 0 and o.type = 4 and (o.name like 'USER%' or o.name like 'ALL%' or (o.name like 'DBA%' and exists (select null from sys.v$enabledprivs where priv_number = -47 /* SELECT ANY TABLE */) ) ) union all select o.name, c.comment$ from sys.obj$ o, sys.com$ c where o.obj# = c.obj#(+) and o.owner# = 0 and o.name in ('AUDIT_ACTIONS', 'COLUMN_PRIVILEGES', 'DICTIONARY', 'DICT_COLUMNS', 'DUAL', 'GLOBAL_NAME', 'INDEX_HISTOGRAM', 'INDEX_STATS', 'RESOURCE_COST', 'ROLE_ROLE_PRIVS', 'ROLE_SYS_PRIVS', 'ROLE_TAB_PRIVS', 'SESSION_PRIVS', 'SESSION_ROLES', 'TABLE_PRIVILEGES','NLS_SESSION_PARAMETERS', 'NLS_INSTANCE_PARAMETERS', 'NLS_DATABASE_PARAMETERS') and c.col# is null union all select so.name, 'Synonym for ' || sy.name from sys.obj$ ro, sys.syn$ sy, sys.obj$ so where so.type = 5 and ro.linkname is null and so.owner# = 1 and so.obj# = sy.obj# and so.name <> sy.name and sy.owner = 'SYS' and sy.name = ro.name and ro.owner# = 0 and ro.type = 4 and (ro.owner# = userenv('SCHEMAID') or ro.obj# in (select oa.obj# from sys.objauth$ oa where grantee# in (select kzsrorol from x$kzsro)) or exists (select null from v$enabledprivs where priv_number in (-45 /* LOCK ANY TABLE */, -47 /* SELECT ANY TABLE */, -48 /* INSERT ANY TABLE */, -49 /* UPDATE ANY TABLE */, -50 /* DELETE ANY TABLE */) )) /
Let’s take a closer look at the mechanism Oracle uses to control information access. In the first query of this three-part union, the two lines:
and o.owner#=0 and o.type = 4
ensure that only views (type = 4) owned by SYS (owner = 0) will be returned. The rows are further refined by the “and (o.name like `USER%' . ” section so only the familiar data dictionary views will be returned. The nested SELECT ensures that this part of the union query will only return a row if the user has the specific system privilege, SELECT ANY TABLE. You can test this portion of the DICTIONARY view creation statement easily. Create a user (i.e., mary ) and grant the user the CREATE SESSION privilege only. This is the minimum privilege required to access the database. Connect as mary and enter the following command:
SELECT COUNT(*) FROM dictionary WHERE table_name like 'DBA%';
On a Windows 95 Personal Oracle7 system, the count returned was 2. Connected as system to the same database with the DBA role enabled, the count was 93. On a Windows NT system running Oracle 8.0.3, the user mary with only the CREATE SESSION privilege showed no available rows in the DICTIONARY view for tables beginning with a “DBA” suffix, while the system user showed 117 available. From this exercise, you can learn two important points:
When developing a security system, keep the second point in mind so you don’t rely on objects that may change or disappear in later releases of the Oracle software.
The second query of the DICTIONARY view returns descriptions of the specific views listed in the “in (. )” clause, and the third query returns synonyms owned by system . The DICTIONARY view will return to the user executing the query only all object names where the user created the object and has been granted some type of privilege on the object.
On a Windows NT system, the Oracle version 7 CATALOG.SQL file is 234 Kbytes. On the same system, the Oracle8 CATALOG.SQL is 416 Kbytes. The differences in size are predominantly caused by the creation of “GV” global views and the new disaster recovery approach provided in Oracle8. In either version 7 or version 8, virtually every object defined in this script is either a view or a synonym. Additionally, most views are qualified in a manner similar to the DICTIONARY example to limit the rows returned to only those the user has the right to see. In order to accomplish this, the kernel must have two specific pieces of information about the user: the username and the user id. In addition, that information has to be available in the data dictionary tables. Since the username and user id are known from the login process, and since that data was verified against entries in the data dictionary, the kernel has the information available at all times to determine the levels of access that should be made available to each user.
If you want to apply the approach Oracle uses within the data dictionary code, you will need some method of associating the users with data in the application tables. Typically, the information you will need is organizational in nature. A user whose real name is Mary Jane may be in division AB, department 4. If her userid of mjane is stored as part of her record in the employee table, along with her division and department, then you have captured the minimum amount of information your security system would need to limit data access.
Typically, when access to personnel data is allowed, there is a restriction that each user should only be able to see his or her own data unless that user is a department head or division chief. Here is an example of code you could use to implement the necessary restriction on the employee table:
/* return rows for division chiefs. */ SELECT * FROM employee a, employee e WHERE a.division = e.division AND e.job = 'DIVCHIEF' /* return rows for department heads. */ UNION SELECT * FROM employee a, employee e WHERE a.division = e.division AND a.department = e.department AND e.job = 'DEPTHEAD' /* return the employee's own record. */ UNION SELECT * FROM employee a WHERE emp_dbname = user;
The last query will return a duplicate row when either a division chief or department head uses this view. The UNION operator, however, automatically eliminates duplicate rows.
Are there other ways to do this? Absolutely, provided the structure is present in the database to support the approach.
Get Oracle Security now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.