Granting Database Privileges to User DBADMIN - Advanced SQL Engine - Teradata Database

1. Log on to the client tool of your choice as user DBC.
2. Grant object access privileges to system tables and other objects owned by user DBC. For example:

   GRANT  EXECUTE, SELECT ON "DBC" TO "DBADMIN" WITH GRANT OPTION;

3. Grant object-level database privileges to the database administrator on all objects subsequently created in DBADMIN space. To do that, specify values for the following fields:

   Field	Description
   Database Name	The name of the user or database that owns the objects on which the privileges are being granted, in this case DBADMIN.
   Object Type	The object on which the privileges are given. Includes all databases and all objects created in the database(s).
   To/From User	The name of the user that receives the privileges, in this case DBADMIN.
   All	Specifies all rights for all objects created in DBADMIN space.
   Grant	Specifies that the user is granted the WITH GRANT OPTION privilege, which means that the user can grant all included privileges to other users.

   For example:

   GRANT  EXECUTE, SELECT, INSERT, UPDATE, DELETE, STATISTICS, DUMP, RESTORE, CHECKPOINT, SHOW, EXECUTE PROCEDURE, ALTER PROCEDURE, EXECUTE FUNCTION, ALTER FUNCTION, ALTER EXTERNAL PROCEDURE, CREATE OWNER PROCEDURE, CREATE TABLE, CREATE VIEW, CREATE  MACRO, CREATE DATABASE, CREATE USER, CREATE TRIGGER, CREATE PROCEDURE, CREATE FUNCTION, CREATE EXTERNAL PROCEDURE, CREATE AUTHORIZATION, DROP TABLE, DROP VIEW, DROP MACRO, DROP DATABASE, DROP USER, DROP TRIGGER, DROP PROCEDURE, DROP FUNCTION, DROP AUTHORIZATION ON "DBADMIN" TO "DBADMIN" WITH GRANT OPTION;

4. Grant object-level privileges on DBC tables and views to DBADMIN. For example:

   GRANT  EXECUTE, SELECT, STATISTICS, SHOW ON "DBC" TO "DBADMIN" WITH GRANT OPTION;

5. Grant additional system-level privileges not included with the object-level privileges granted in the previous step:

   GRANT  MONRESOURCE, MONSESSION, ABORTSESSION, SETSESSRATE, SETRESRATE, REPLCONTROL, CREATE PROFILE, CREATE ROLE, DROP PROFILE, DROP ROLE TO "DBADMIN" WITH GRANT OPTION;
   GRANT UDTTYPE, UDTMETHOD ON SYSUDTLIB TO DBADMIN WITH GRANT OPTION;

6. Grant additional system-level privileges not covered by the GRANT shown in the previous step. For example:

   GRANT CTCONTROL ON "DBADMIN" TO "DBADMIN" WITH GRANT OPTION;

7. Grant privileges on Sys_Calendar, which contains tables and views for date-related system functions:

   GRANT SELECT, INSERT, UPDATE, DELETE ON "Sys_Calendar" TO "DBADMIN" WITH GRANT OPTION;

   You do not need to execute a GRANT LOGON statement for user DBADMIN or any other user created in the database. All users automatically have the right to log on to the database from any connected client, unless a revoke logon statement is issued.
8. Log off as user DBC and log back in as user DBADMIN.
9. When prompted, create a private password for user DBADMIN. This client is now configured for use by user DBADMIN. Conduct all future administrative tasks as user DBADMIN or the administrative username used at your site.

Related Information

Reference topics are arranged according to the first step in which the topic appears.