17.10 - How Users and Databases Differ - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

It is often said that the only difference between a database user and a database is that a user has a password, while a database does not. That was once true, but no longer is. While it remains true that databases cannot be assigned a password, users can have many more attributes than databases.

User and database definitions share the following set of attributes in common.
  • A permanent space specification
  • A temporary space specification
  • A spool space specification
  • One or more account strings
  • A BEFORE journal specification
  • An AFTER journal specification
  • A default journal table specification
  • A fallback specification for all tables created within the permanent space for the user

Even in the minimal case, the definition of a database is always a proper subset of the definition of a user, while a user is always a proper superset of a database definition.

A user has all the attributes of a database, and can be defined to have a number of additional attributes such as those contained in the following list. A database cannot have any of the attributes in this list.
  • A password
  • A startup string
  • A default role
  • A default time zone
  • A default date form
  • A default database
  • A default character set
  • A default collation sequence
  • A default user profile assigned using a GRANT request
  • A user definition that can be partially external to the DBMS through remote user management software

When you define a user, you are defining a user in the generic sense of the term, but you are also defining a database that has the same name as the newly defined user. Note that these seemingly separate entities are actually a single database object. An important, but often overlooked, distinction between users and databases is that it is possible to define the database component of a user with a PERM space value of 0 bytes. A user defined in this way is essentially only a user, and has virtually no overlap with the attributes of databases.

Be aware that such pure users are, by default, terminal nodes in their hierarchy because they have no permanent space to pass on to child users or databases.

If you define a user with no permanent space, you must still define some minimum value of SPOOL space (and perhaps of TEMPORARY space if the user will be working with materialized global temporary tables) for the user. These can be inherited from the owner database or user or can be specified explicitly.