TERADATA CREATE DATABASE and USER

Teradata CREATE DATABASE

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

Teradata CREATE DATABASE syntax

 Below is the syntax of the Teradata create Database.

CREATE DATABASE database_name 
AS 
[PERMANENT|PERM] = n BYTES, 
SPOOL = n BYTES,
TEMPORARY = n BYTES;

Space limit in Teradata database can be defined as below –

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

A newly created database is owned by an existing database until any objects are created in the database, it will be empty.

Teradata CREATE DATABASE Example

The following example creates a UNIVERSITY database.

CREATE DATABASE UNIVERSITY 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 Teradata database, users in Teradata also can hold different database object like tables, views, macros, and triggers. However, a user can’t hold tables until perm space is assigned to it.

A user can logon into Teradata system using password and access  object within:

• Itself
• Other databases or users for which it has access right.

Teradata CREATE USER Syntax

Following is the syntax of CREATE USER in Teradata.

CREATE USER username 
AS 
[PERMANENT|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 login.

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 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;