Download (direct link):
There are two versions of most system views: restricted (indicated by the suffix X appended to the viewname) and non-restricted (no X appended to the view name). For example, DiskSpaceX, TableSizeX, and SessionInfoX are the names of restricted views. You can load either version or both versions.
In some cases, separate viewnames are defined for restricted and non-restricted views; for example, UserRoleRights and AllRoleRights. Both views are loaded.
4 - 10 Teradata RDBMS Database Administration
Chapter 4: Using Data Dictionary Tables and Views
Dictionary Views and Tables for the Administrator
Returns from Non-Restricted Views
An unqualified select on a non-restricted view returns all rows from the underlying tables, which can overflow user spool space. Also, unless you explicitly revoke access to it, the view lets any user access all the information.
Returns from Restricted Views
X views have the same columns as non-X views, but the definition includes a WHERE clause which limits access on the underlying tables to only those rows associated with the requesting user, such as objects the user owns, is associated with, or has been granted privileges on. For example, if UserA submits:
SELECT * FROM DBC.ProfilelnfoX ;
the response is the name and parameter settings only for the profile assigned to UserA.
This makes the response meaningful, limits its row size, and protects user privacy.
Restricted views typically run three different tests before returning information from data dictionary tables to a user. Each test focuses on the user and his or her current privileges. Thus, it can take longer to receive a response when selecting from a restricted view.
Explicitly Granted Privileges
By default, the SELECT privilege is granted to PUBLIC (all users) on most views in both the restricted and non-restricted versions.
The PUBLIC keyword allows all users to retrieve view information via the SELECT statement, and it may not be revoked selectively via the EXCEPT WHERE clause.
Some views are applicable only to users who have a need to see specialized information, such as a database administrator, the security administrator, or an NCR field service representative. Access to these views is limited to only to the applicable user. For example, only a user with DBC or SystemFE privileges can access the DBC.DBQLRules view.
You can revoke from PUBLIC to remove one or more access privileges from all users. Also, you can use GRANT and REVOKE to grant or revoke one or more privileges on any view to or from any particular user.
Administration Views and Tables
You should have granted access to everything in the database hierarchy to your special database administrator user, such as DBAdmin (see "Administrative User" on page 1-7). If you did so, you can access all rows of every view when you log on with your administrator name and password.
4 - 10
Teradata RDBMS Database AdministrationChapter 4: Using Data Dictionary Tables and Views
Dictionary Views and Tables for the Administrator
Views and tables that might be of particular interest to you as the database administrator include the following:
This DD object ... Provides information about .
All_RI_Children view all tables, fully qualified and in child-parent order. (It is similar to the RI_Child_Tables view but returns the names instead of internal IDs of the databases, tables, and columns.) The All_RI_Children view is designed for use in a SELECT statement with a WHERE clause to narrow the selection criteria. You can control who has access to internal ID numbers by limiting the access to the RI_Child_Tables view while allowing more (or all) users to access names via this view. For more information on using views to control data access, see "Limiting Data Access with Views" on page 6-39.
AllRights view all users who have been explicitly and automatically granted privileges, and the objects on which the privileges were granted. Included are the name and authority of each granting user, plus an AllnessFlag indicator. • The AllnessFlag, Y or N, indicates whether this privilege was granted to all subordinate users or all users owned by the grantee. • The AccessRight field contains the privilege code; for example, the code AS stands for ABORT SESSION, DG for DROP TRIGGER, PC for CREATE PROCEDURE, and so forth.
All_RI_Parents view all tables, fully qualified and in parent-child order. (It is similar to the RI_Parent_Tables view but returns the names instead of internal IDs of databases, tables, and columns.) The All_RI_Parents view is designed for use in a SELECT statement with a WHERE clause to narrow the selection criteria. You can control who has access to internal ID numbers by limiting the access to the RI_Parent_Tables view while allowing more (or all) users to access names via this view. For more information on using views to control data access, see "Limiting Data Access with Views" on page 6-39.