Creating Database Admins Using SQL | Teradata VantageCloud Lake - Creating Database Administrators Using SQL - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905
To create an administrator for VantageCloud Lake, do the following tasks:
After you create the administrative user SYSDBA, do not log on as user DBC to perform subsequent administrative activities except for activities that only user DBC can perform.
  1. Determine what to provision for the administrative profiles, roles and database administrators.

    See User Privileges for commonly-assigned privileges for users.

  2. Log on to VantageCloud Lake.
  3. Go to Editor.
  4. Connect as the database administrator, SYSDBA.
  5. Create the profile for the database administrator using a command similar to the following. Best practice is to start the profile name with P_ to make it easily identifiable as a profile.
    CREATE PROFILE P_profile_name AS profile ;
    The following is an example with profile attributes shown in bold:
    CREATE PROFILE "P_AdminProfile" AS
COMPUTE GROUP = compute_group
SPOOL=spool_space,
TEMPORARY=NULL,
ACCOUNT=('$H-DBC-MANAGER',' account__str2',' account_str3'),
DEFAULT DATABASE="database_name",
PASSWORD =
(EXPIRE=90,MINCHAR=NULL,MAXCHAR=NULL,MAXLOGONATTEMPTS=NULL,
LOCKEDUSEREXPIRE=NULL, REUSE=NULL,DIGITS=NULL,
RESTRICTWORDS=NULL,SPECCHAR=NULL);
  6. Create the database administrator. This procedure uses the administrator name SYSDBA. Use a name that meets your company standards.
    CREATE USER "SYSDBA" FROM "DBC"
AS PERM = number_of_bytes,
PASSWORD = "temp_password",
STARTUP = '',
DEFAULT DATABASE = "database_name",
PROFILE="profile_name_from_previous_step”;
  7. Grant object access privileges to SYSDBA.
    GRANT EXECUTE, SELECT ON "DBC" TO "SYSDBA" WITH GRANT OPTION;
  8. Grant object-level privileges to SYSDBA.
    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 "SYSDBA" TO "SYSDBA" WITH GRANT OPTION;
  9. Grant object-level privileges on DBC tables and views to SYSDBA. The following is an example of the syntax:
    GRANT EXECUTE, SELECT, STATISTICS, SHOW ON "DBC" TO "SYSDBA" WITH GRANT OPTION;
  10. Grant compute group privileges to SYSDBA.
    GRANT CREATE COMPUTE GROUP TO SYSDBA WITH GRANT OPTION;
GRANT DROP COMPUTE GROUP TO SYSDBA WITH GRANT OPTION;
GRANT CREATE COMPUTE PROFILE TO SYSDBA WITH GRANT OPTION;
GRANT DROP COMPUTE PROFILE TO SYSDBA WITH GRANT OPTION;
  11. Grant system-level privileges to SYSDBA. The following is an example of the syntax:
    GRANT MONRESOURCE, MONSESSION, ABORTSESSION, SETSESSRATE, SETRESRATE, REPLCONTROL, CREATE PROFILE, CREATE ROLE, DROP PROFILE, DROP ROLE TO "SYSDBA" WITH GRANT OPTION;


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

GRANT SELECT, INSERT, UPDATE, DELETE ON "Sys_Calendar" TO "SYSDBA" WITH GRANT OPTION;
    Important: If you create administrators outside of the VantageCloud Lake platform, then you must grant those administrators the following privileges to allow them to access the platform:
    GRANT ALL ON user_name to user_name WITH GRANT OPTION
GRANT LOGON ON ALL TO user_name WITH NULL PASSWORD
  12. Grant access on database SYS_Calendar to SYSDBA. 
    GRANT SELECT, EXECUTE ON SYS_CALENDAR TO SYSDBA WITH GRANT OPTION;
  13. Log off as user DBC.
  14. Log on as SYSDBA and change the password.