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

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
  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.

Reference Information

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

Step Topic Resource for Further Information
2 System tables and views Teradata Vantage™ - Data Dictionary, B035-1092
3 User privileges “Creating Users and Granting Privileges” in Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100.
2 through 6 Syntax and options for GRANT (SQL form) and (Role form) Teradata Vantage™ - SQL Data Control Language, B035-1149
10 Password format rules “Managing Database Passwords,” in Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100