Teradata CREATE DATABASE and USER

Teradata CREATE DATABASE

A Teradata database is a repository for various kinds of database objects like Tables (requires perm space), Views (uses no perm space), Procedures (requires perm space), functions, macros, etc.

This is similar to the SCHEMA in other relational databases.

Required Privileges

To create a database, one must have the CREATE DATABASE privilege on the immediate owner database.

Teradata CREATE DATABASE syntax

The syntax of the Teradata Create Database is as follows.

CREATE DATABASE database_name [FROM database_name]
AS
PERM = n BYTES,
SPOOL = n BYTES,
TEMPORARY = n BYTES,
[ACCOUNT = account_string,]
[DEFAULT MAP = map_name,]
[NO FALLBACK PROTECTION,]
[NO|DUAL BEFORE JOURNAL,]
[NO|DUAL|LOCAL|NOT LOCAL AFTER JOURNAL,]
[DEFAULT JOURNAL TABLE = database_name.table_name];

Space limit in the Teradata database can be defined as below –

Perm Space – Max amount of space available for Tables and other objects.
Spool Space – Max amount of space available for query processing.
Temp Space – Used for creating a temporary table.

A newly created database remains empty until any database objects are created in it.

Teradata CREATE DATABASE Example

The following example creates a STORE database from the SYSDBA database.

CREATE DATABASE TUTORIALSBOOK FROM SYSDBA
AS
PERM = 5368709120, -- 5GB
SPOOL = 5368709120, -- 5GB
TEMPORARY = 2147483648; -- 2GB
NO FALLBACK
NO BEFORE JOURNAL
NO AFTER JOURNAL;

Teradata CREATE USER

In Teradata, users and databases are exactly the same except that the user is assigned a password.

Same as the Teradata database, users in Teradata also can hold different database objects like tables, views, macros, and triggers. However, a user can’t hold tables until perm space is assigned to them.

A user can logon into the Teradata system using a password and access objects within:

Itself
Other databases or users to which it has access rights.

Teradata CREATE USER Syntax

The syntax of CREATE USER in Teradata is as follows.

CREATE USER username 
AS 
PERM = n BYTES, 
PASSWORD = password,
TEMPORARY = n BYTES, 
SPOOL = n BYTES;

Here,

Everything is the same as DATABASE creation except the PASSWORD parameter where you have to provide a password for the user to log in.

Teradata CREATE USER Example

CREATE USER SYSDBA
AS
PERM = 5368709120,
PASSWORD = password1
SPOOL= 5368709120,
TEMPORARY = 2147483648;

In the above example, we have created a user SYSDBA with PERM space 5368709120 or 5GB, SPOOL space 5368709120 or 5GB, and TEMPORARY space 2147483648 or 2GB.

A newly created user also remains empty until any objects are created in it.

The next step is to provide all the privileges to the SYSDBA user to perform operations on the SYSDBA user. This step should be performed after logging into the dbc user.

GRANT ALL ON SYSDBA to SYSDBA WITH GRANT OPTION;

Teradata CREATE DATABASE and USER