16.10 - Granting Database Privileges to User DBADM - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)
  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 "DBADM" 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 DBADM.
    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 DBADM.
    All Specifies all rights for all objects created in DBADM 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 "DBADM" TO "DBADM" WITH GRANT OPTION;
  4. Grant object-level privileges on DBC tables and views to DBADM. For example:
    GRANT  EXECUTE, SELECT, STATISTICS, SHOW ON "DBC" TO "DBADM" 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 "DBADM" WITH GRANT OPTION;
    GRANT UDTTYPE, UDTMETHOD ON SYSUDTLIB TO DBADM WITH GRANT OPTION;
  6. Grant additional system-level privileges not covered by the GRANT shown in the previous step. For example:
    GRANT CTCONTROL ON "DBADM" TO "DBADM" 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 "DBADM" WITH GRANT OPTION;
    You do not need to execute a GRANT LOGON statement for user DBADM 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 DBADM.
  9. When prompted, create a private password for user DBADM. This client is now configured for use by user DBADM. Conduct all future administrative tasks as user DBADM 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 Data Dictionary
3 User privileges Chapter 5: “Creating Users and Granting Privileges” in Security Administration.
2 through 6 Syntax and options for GRANT (SQL form) and (Role form) SQL Data Control Language
10 Password format rules Chapter 6: “Managing Database Passwords,” in Security Administration