Teradata RDBMS Database Administration - NCR

The user account string enables you to summarize resource usage by accountID. The system table DBC.Acctg tracks CPU and I/O resources expended by a session. The I/O resource tracks the number of AMP to DSU read and write operations generated by a given user or account, and charges them to the current account associated with a session.

Use the DBC.AMPUsage view to access aggregations of DBC.Acctg contents.

Capacity Planning

To plan for the resources needed to accommodate growth, you must know how the current workload is affecting the system. To assess the effect of the current workload, you can collect and analyze information about resource utilization.

Collecting and analyzing information about resource utilization is one component of analyzing data. Another component is the collection of historical usage statistics. The accounting feature can be used to determine the activity on current workloads, which assists you in anticipating future needs.

Resource Management

System accounting, as part of system management and in conjunction with the Index Wizard and other query analysis tools, can help you identify potential problem areas, such as unbalanced row distribution or inefficient join indexes.

Also, you may need to control who gets specific resources. You use the Priority Scheduler to manage user account priorities to maintain efficient operation of the Teradata RDBMS while providing equitable service to multiple client utilities and users.

Privileges control the types of activities you can perform during a session. Privileges (also called "rights") are maintained in the Data Dictionary.

The dictionary table DBC.AccessRights contains information about privileges implicitly or explicitly granted to users, to PUBLIC (all users), and to roles. The dictionary table DBC.RoleGrants contains information about roles granted to users and other roles.

Rows are inserted into or removed from these tables by:

• CREATE and DROP statements

• GRANT and REVOKE statements

Caution: The GIVE command affects ownership and space, it is not reflected in the DBC.AccessRights table. GIVE should be used only with caution.

Privileges can be granted automatically, explicitly, or implicitly (also see "Creator versus Owner" on page 1-14), or they can be inherited. The recipient of an implicit privilege can be the owner or the creator of an object, or both, while the recipient of an explicitly granted privilege can be a user or a role, and a nested role can inherit privileges. The following table summarizes how privileges are bestowed, the recipients, and the resulting system table updates:

Type of Grant Type of Recipients System Table Activity
Implicit Owner Rows are not inserted in DBC.AccessRights because ownership privileges cannot be refused or revoked.
Automatic Creator Rows are inserted in DBC.AccessRights as a by-product of a CREATE statement.
Explicit User or Role Rows are inserted in DBC.AccessRights as a by-product of a GRANT statement.
Inherited Nested role Rows are inserted in DBC.RoleGrants as a by-product of a GRANT (ROLE to ROLE) statement. Note: A role is considered first-level nested when it is the grantee of another role.

Ownership Privileges

Ownership privileges are granted implicitly to owners of the space that is allocated when a database or user is created. An owner has the implicit privilege to explicitly GRANT access rights to other users on any owned object.

6 - 10

Teradata RDBMS Database AdministrationChapter 6: Controlling Access

Controlling Inherited and Group Privileges

The following conditions apply to ownership privileges:

• Rows are not required in DBC.AccessRights in order for the owner to grant privileges, including the WITH GRANT OPTION, on owned entities.

• Ownership privileges cannot be explicitly revoked.

• Ownership privileges cannot be taken away unless ownership is transferred or the owned entity is deleted or dropped. |

Automatic versus Explicitly Granted Privileges

The terms automatic and explicit refer to the way the privileges are granted and how they are inserted into the DBC.AccessRights table (also see "Explicit, Automatic, and Implicit Privileges" on page 1-15).