Download (direct link):
• All databases in a system and their individual tables
Then Update Space updates the CurrentPermSpace, CurrentTempSpace, and CurrentSpoolSpace columns in the DBC.DataBasesSpace table for each table and for the containing database as a whole.
Update DBC Utility
Update DBC recalculates the maximum allowed values for permanent, temporary, and spool space and uses those values to update the DBC.DBase system table and the DBC.DataBasesSpace system table, as summarized below.
Note: Values in DBC.DBase are global values. Values in DBC.DataBasesSpace are local AMP values. The calculation is the global value divided by the number of AMPs in the system.
The Update DBC utility recalculates the . In system table .
PermSpace, SpoolSpace, and TempSpace values for system DBC.DBase (global) user/database DBC.
• The PermSpace value in DBase for user DBC is the total available storage space minus the PermSpace for all other databases.
• The SpoolSpace and TempSpace values in DBC.DBase for user DBC are the total available storage space.
For databases other than DBC, the PermSpace, SpoolSpace, and TempSpace values in the DBASE table are the maximums declared when the database is defined.
MaxPermSpace, MaxSpoolSpace, and MaxTempSpace DBC.DataBasesSpace
values for each database in the system based on the (AMP)
PermSpace, SpoolSpace, and TempSpace values in DBC.DBase for that database.
4 - 18
Teradata RDBMS Database Administration 4 - 15Chapter 5:
Setting Up Users, Profiles, Accounts and Accounting
This chapter discusses how to set up and administer accounts on your Teradata RDBMS. This chapter contains the following:
• Creating users and databases
• Establishing logon security, including SSO on Windows 2000
• Implementing user roles and profiles |
• Modifying the definition of an existing user
• Defining account IDs, Priority Scheduler performance groups, and system I date and time substitution variables in user and profile definitions |
• Dynamically changing the priority of a session account
• Using account statistics
• Clearing account values
For more details on defining and monitoring accounts and accounting, see: |
• Teradata RDBMS Database Design
• Teradata RDBMS SQL Reference, Volume 4
• Teradata RDBMS Data Dictionary
Teradata RDBMS Database Administration
3 - 1Chapter 5: Setting Up Users, Profiles, Accounts and Accounting
Implementing Roles and Profiles
Creating Users and Databases
As the database administrator, you create and manage users, databases, profiles, roles, tables, macros, and views.
You also grant access privileges to roles and users on these and other objects. To perform these tasks, you need to:
• Determine database information content
• Determine whether to implement group controls by implementing roles or profiles, or both
• Determine available permanent, temporary, and spool space
• Resolve ownership issues
• Define authorization checks and validation procedures
• Audit LOGON, GRANT, REVOKE, session, and other account and access-related activity
Creating users, databases, and profiles involves allocation of permanent, spool, and temporary space.
Note: Space allocation is critical. Before attempting to create a database, user, or profile, review "Space Considerations" on page 3-1.
Users versus Databases
A database or user is a uniquely named permanent space that can store tables, indexes, procedures, triggers, and other databases and users. (Views, macros, roles, and profiles do not consume space; their definitions are stored in the data dictionary.) Each database and each user also can contain one permanent journal.
A user performs actions; a database is passive. Users have passwords and startup strings; databases do not. Users can log on to the Teradata RDBMS, establish sessions, and submit SQL statements; databases cannot. Access privileges are associated with each object.
Creator privileges are associated only with a user, because only a user can log on and submit a CREATE statement.
Ownership privileges are associated with either a database or a user, because each can hold an object and an object is owned by the named space in which it resides.
For more details on privileges, see "Creator versus Owner" on page 1-14 and "Access Privileges" on page 6-2.
5 - 10
Teradata RDBMS Database AdministrationChapter 5: Setting Up Users, Profiles, Accounts and Accounting
Implementing Roles and Profiles
CREATE DATABASE Statement
The CREATE DATABASE statement enables you to uniquely name a repository and allocate permanent, temporary, and spool space. The permanent space allocation is taken from, and cannot exceed, the current permanent space limit of the parent of the new database.
If you do not specify a database or user in the optional FROM clause of the CREATE DATABASE statement, the default is the immediate owner in the hierarchy.
Also, you can assign account ID to a database, which is the account to be charged for the allocated space. If you do not specify an account ID in the CREATE or MODIFY DATABASE statement, the default is the account of the immediate owner database or user.