Granting Database Privileges to User DBADMIN - Teradata Database

Granting Database Privileges to User DBADMIN

1 Log on to Teradata Administrator as user DBC.

2 Grant object access privileges to system tables and other objects owned by user DBC.

a From the main window, click Tools>Grant/Revoke>Object Rights.

b 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. Select DBC from the Database Name drop down list.
Object Type	The object on which the privileges are given. Select Database from the Object Type drop down list.
To/From User	The name of the user that receives the privileges. Select DBADMIN from the To/From User drop down list.
Normal	Specifies the privileges granted to the user. Check the following boxes: Execute Select
Grant	Specifies that the user is granted the WITH GRANT OPTION privilege so the user can grant the privilege to other users. Check the Grant box.

c Click the Grant button.

The Query window appears and displays the generated GRANT statement:

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

d From the Query window, click the Run Query button .

e Close the Query window.

3 Grant object-level database privileges to the database administrator on all objects subsequently created in DBADMIN space.

a From the main window, click Tools>Grant/Revoke>Object Rights.

b 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. Select DBADMIN from the Database Name drop down list.
Object Type	The object on which the privileges are given. Includes all databases and all objects created in the database(s). Select Database from the Object Type drop down list.
To/From User	The name of the user that receives the privileges. Select DBADMIN from the To/From User drop down list.
All	Specifies all rights for all objects created in DBADMIN space. Check the All box.
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. Check the Grant box.

c Click the Grant button.

The Query window appears and displays the generated GRANT statement.

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;

d From the Query window, click the Run Query button .

e Close the Query window.

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

a From the main window, click Tools>Grant/Revoke>Object Rights.

b 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. Select DBC from the Database Name drop down list.
Object Type	The object on which the privileges are given. Includes all databases and all objects created in the database(s). Select Database from the Object Type drop down list.
To/From User	The name of the user that receives the privileges. Select DBADMIN from the To/From User drop down list.
Access	Specifies privileges for all DBC tables and views. Check the Access box.
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. Check the Grant box.

c Click the Grant button.

The GRANT statement generates and automatically submits the following SQL statements without previewing them.

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

d From the Query window, click the Run Query button .

e Close the Query window.

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

a Deselect the Preview Mode button at the top of the main screen. The subprocedure that follows generates a compound SQL statement that cannot be submitted from Preview Mode.

b From the main window, click Tools>Grant/Revoke>System Rights.

c Specify values for the following fields:

Field	Description
To/From User	The name of the user that receives the privileges. Select DBADMIN from the To/From User drop down list.
All	Specifies all rights for all objects created in DBADMIN space. Check the ALL box.
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. Check the Grant box.

d Click the Grant button.

The GRANT statement generates and automatically submits the following SQL statements without previewing them.

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;

e Close the Grant/Revoke System dialog box.

6 Grant additional system-level privileges not covered by GRANT shown in step 5.

a Click the Query button, the lowest button on the left edge of the main screen to open the Teradata SQL Assistant query window. This allows you to construct and submit an SQL statement without using the GUI interface.

b Enter the following SQL statement:

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

c Click the Run Query button .

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

a Click the Preview Mode button to enable previewing SQL statements before submitting them.

b From the main window, click Tools>Grant/Revoke>Object Rights.

c 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. Select Sys_Calendar from the Database Name drop down list.
Object Type	The object on which the privileges are given. Includes all databases and all objects created in the database(s). Select Database from the Object Type drop down list.
To/From User	The name of the user that receives the privileges. Select DBADMIN from the To/From User list.
Normal	Allows specification of rights for the named objects. Check the Select, Insert, Update, and Delete boxes.
Grant	Specifies that the user is granted the WITH GRANT OPTION privilege, that is the user can grant all included privileges to other users. Check the Grant box.

d Click the Grant button. The Query window appears and displays the generated SQL statement.

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

e From the Query window, click the Run Query button .

f Close the Query window and the Grant/Revoke Object dialog box.

8 Click File>Define Data Source. The ODBC Data Source Administrator box appears.

a On the System DSN tab select the data source, that is, the system name.

b Click on the Configure button. The ODBC Driver Setup for Teradata Database dialog box appears.

c Change the value of the Username parameter to DBADMIN.

d Change the value of the password to the temporary password for DBADMIN specified in step b of “Creating User DBADMIN.”

Note: User DBADMIN will be prompted to change the temporary password to a permanent, private password at first logon.

e Click Yes to store the password, then click OK.

f Click OK to exit the ODBC Data Source Administrator dialog box.

9 Click File>Exit to close Teradata Administrator.

10 Immediately reopen Teradata Administrator, which logs on to Teradata Database as DBADMIN using the temporary password specified in step d.

Note: 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.

11 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

12 Exit Teradata Administrator by entering File>Exit.

Reference Information

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

For step...	Information on...	Is available in...
1	logon to Teradata Administrator	Teradata Administrator User Guide.
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