4.4 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 [1] | 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 | [1] These views do not have an ALL_ counterpart . | 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. 4.4.1 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. 4.4.2 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. 4.4.3 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. |