Oracle Security

Views Used for Security

Although the data dictionary views provide a wide range of information about the state of the database, the views shown in Table 4.1 are particularly important for providing security information. We’ve also listed the tables on which these views are built. We’ll examine each of the views listed in this table later in this chapter.

Table 4-1. Data Dictionary Views for Security

View Name	Type of Information Available	Tables on Which View Is Built
DBA_PROFILES[a]	Profiles and their associated resource and time limits	profile$, profname$, resouce_map, obj$
DBA_ROLES1	All roles that exist in the database	user$
DBA_ROLE_PRIVS1	Roles granted to users and other roles	user$, sysauth$, defrole$
DBA_SYS_PRIVS1	System privileges granted to users and roles	user$, sysauth$, system_privilege_map
DBA_TAB_PRIVS	Privileges like SELECT, INSERT, UPDATE, etc. that each user or role has per object	user$, objauth$, obj$, table_privilege_map
DBA_USERS	Who has an account for the database; also, which profile is assigned to the user	user$, ts$, profname$, profile$, user_astatus_map
ROLE_ROLE_PRIVS	Roles granted to roles	user$, sysauth$
ROLE_SYS_PRIVS	System privileges granted to roles	user$, system_privilege_map, sysauth$
ROLE_TAB_PRIVS	Table privileges granted to roles	user$, table_privilege_map, objauth$, obj$, col$
USER_ROLE_PRIVS	Roles granted to the current user	user$, sysauth$, defrole$, and x$kzdos
[a] These views do not have an ALL_ counterpart.

Note

The view composition shown here is for an Oracle8 database. The view composition will vary from version to version of Oracle. We recommend you check for changes in the composition of the views when you install a new Oracle version to determine what has changed.

Note that several of these views concern roles; roles were described briefly in Chapter 3.

Tables Used to Build the Views

Within the data dictionary, the ten views listed in Table 4.1 are used more heavily for security purposes. This section contains a description of each of the tables on which the views are built. Notice that most of the tables listed have names that end with $. Most of the views in the data dictionary are based on $ tables. There are comments within the SQL.BSQ file for most of the $ tables.

From a security standpoint, only thirteen of these tables are of interest. Note that not all of the tables listed have a $ in their names. However, they are shown because they’re used to build the views we’ll be examining next. The tables of interest are listed with some of the documentation as found in SQL.BSQ, or with documentation we’ve supplied. One of the views that uses the $ tables also uses a table named x$kzdos. (x$ tables are actually memory structures that are not documented by Oracle and only exist when the database is running.)

user$

Users. Identifies users by name, type, and number.

col$

Columns. References obj$.obj#.

defrole$

Default roles. Two columns: user#, role#.

obj$

Objects. Identifies objects by name, type, and owner number.

objauth$

Table authorization. Grants, grantee, grantor, and options.

sysauth$

System authorization. System privileges, grantee, options.

ts$

Tablespaces. Identifies tablespaces by number, name, and owner.

profile$

Crosswalk between profiles and resource privileges.

profname$

Maps profile names to a number.

resource_map

Description table for resources. Maps resource name to number.

system_privilege_map

Maps a system privilege to a number. Two columns.

table_privilege_map

Maps table auditing privileges to a number. Two columns.

User_astatus_map

User account tracking of password and account status. Two columns.

You’ll begin to understand the real meaning of “normalization” if you spend some time reviewing these tables and the views that incorporate them. Remember, this data dictionary is the same for a notebook installation and for a terabyte database in an international bank. The access has to be fast — and it is.

Views and Auditing

In addition to determining what privileges a user has within the database, the data dictionary also provides a minimal auditing capability. The information is limited in scope and is not available unless the DBA modifies the Oracle initialization file (INIT.ORA) to include the AUDIT_TRAIL parameter. The default for this parameter is “NONE.” When the parameter is set to “DB,” auditing is enabled and the results of audited actions are written to a table, SYS.AUD$, and maintained in the database.

Note that these are all “action” audits — that is, you may capture the fact that a user has logged on, has deleted from a table, or has updated a table, but the specifics about which row was affected are not trapped. To get this information, you must write custom code to put PL/SQL triggers on each table to be monitored. A sample application is provided in Chapter 10, showing examples of how to create and use triggers to capture row-level information.

A Closer Look at the Views for Security

Keep in mind that a user given the DBA role (or comparable privileges in another role) may access and possibly modify any object within the database. This section discusses all the views that are particularly relevant to security issues. Some of these views can only be seen by users with DBA privileges; you’ll recognize these views by their prefix DBA_.

In the section Section 4.2, early in this chapter, we explained that some of the views have equivalents with names like ALL_ and USER_. In those cases, while the explanation of the functionality will be consistent, the actual number of columns and rows returned by each privilege level of user will vary. For example, suppose that you have been granted the DBA role with its associated privileges. When you describe the view DBA_USERS, ALL_USERS, and USER_USERS, you will see a different number of columns and a different amount of column information for each view:

SQL> DESCRIBE dba_users
 Name                            Null?    Type
 ------------------------------- -------- ----
 USERNAME                        NOT NULL VARCHAR2(30)
 USER_ID                         NOT NULL NUMBER
 PASSWORD                                 VARCHAR2(30)
 ACCOUNT_STATUS                  NOT NULL VARCHAR2(32)
 LOCK_DATE                                DATE
 EXPIRY_DATE                              DATE
 DEFAULT_TABLESPACE              NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE            NOT NULL VARCHAR2(30)
 CREATED                         NOT NULL DATE
 PROFILE                         NOT NULL VARCHAR2(30)
 EXTERNAL_NAME                            VARCHAR2(4000)

SQL> DESCRIBE all_users
 Name                            Null?    Type
 ------------------------------- -------- ----
 USERNAME                        NOT NULL VARCHAR2(30)
 USER_ID                         NOT NULL NUMBER
 CREATED                         NOT NULL DATE

SQL> DESCRIBE user_users
 Name                            Null?    Type
 ------------------------------- -------- ----
 USERNAME                        NOT NULL VARCHAR2(30)
 USER_ID                         NOT NULL NUMBER
 ACCOUNT_STATUS                  NOT NULL VARCHAR2(32)
 LOCK_DATE                                DATE
 EXPIRY_DATE                              DATE
 DEFAULT_TABLESPACE              NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE            NOT NULL VARCHAR2(30)
 CREATED                         NOT NULL DATE
 EXTERNAL_NAME                            VARCHAR2(4000)

If you are a DBA, take a minute to really look at the descriptions of the _USER views here, because you will probably use these views frequently. For example, as a DBA, you will use the DBA_USERS view to check on the composition of a user account that you want to duplicate for another user. Among other things, you will use this view to determine the default tablespace and temporary tablespace assigned to each user.