Granting Database Privileges to User DBADMIN - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantage™
  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.

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™ - Analytics Database 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™ - Analytics Database Security Administration, B035-1100