Creating the Security Administrator User - Advanced SQL Engine - Teradata Database

Security Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2022-02-15
dita:mapPath
ppz1593203596223.ditamap
dita:ditavalPath
wrg1590696035526.ditaval
dita:id
B035-1100
lifecycle
previous
Product Category
Teradata Vantageā„¢

You should create the principal security administrator, SECADMIN, to carry out initial setup of database security.

The following examples use the name SECADMIN, but it is not required.
For sites that require more than one security administrator:
  • After it creates and provisions individual administrative users, SECADMIN can function mainly as a database, owning security administrators and security-related tables. Individual administrators can perform security duties.
  • Use roles to define privileges for specific administrative functions.

Perform the following steps to create the security administrator user and grant the minimum privileges necessary to carry out security administration duties:

  1. Log on to the database as user DBC.
  2. Update ExpirePassword in DBC.SysSecDefaults to a non-zero value so the temporary password assigned to the security administrator user (and all other temporary passwords) expires at first logon, requiring users to create a private password. For example:
    UPDATE DBC.SysSecDefaults SET ExpirePassword = 90 ;
    The actual value of PasswordExpire should conform to your site security policy.

    Subsequently, the password for each user expires each time the increment of days passes.

    See ExpirePassword.

  3. Create the security administrator user with a CREATE USER statement:
    CREATE USER secadmin FROM space_owner AS
    PERM =  perm_space 
    PASSWORD =  temp_password 
    [ SPOOL =  spool_space ]
    [ ACCOUNT = 'account' ]
    [ DEFAULT DATABASE = secadmin ]
    [ FALLBACK ];
    secadmin
    The name of the primary security administrator user (for example, SECADMIN).
    space_owner
    The owner of the space in which you create the user secadmin.
    perm_space
    The space in bytes that contains all objects that user secadmin creates or owns.
    Recommendation: Specify approximately 2-3% of available system (space_owner) space, in which the security administrator can create users and databases and tables for security-related data.
    For information on how to determine secadmin space, see Creating the Spool Space Reserve.
    temp_password
    The temporary password for user secadmin.
    Recommendation: Use any simple password that follows the default system password controls and site policy. The set up in step 2 causes the temporary password to expire at first logon.
    spool_space
    [Optional] Limits the portion of the spool reserve that secadmin can use to execute queries.
    Recommendation: Specify approximately 20% of the spool reserve.
    account
    [Optional] Account strings are an optional method of controlling the granularity of resource accumulations that are reported in the DBC.Acctg table. Account strings allow the collection of CPU and I/O that is reported in DBC.Acctg to be grouped by application time of day, and priority. Variable substitution parameters included in the account string will be resolved at execution time.
    A secondary use of account strings is to direct the classification of a query to a default Timeshare workload in TASM or TIWM. This classification will be effective only in cases where normal classification processes do not match the query to a user-defined workload.
    An account string defines the following characteristics for profile member sessions in the database:
    • Session priority
    • Account ID (to assign charges for system time and resources)
    • Date stamp
    • Time stamp
    Recommendation: Define at least one account string per profile. If necessary, you can add other accounts later using a MODIFY PROFILE statement.
    For more information about accounts, see Teradata Vantageā„¢ - Database Administration, B035-1093.
    DEFAULT DATABASE = secadmin
    [Optional] The user or database that contains the space in which the database stores or searches for new or target objects unless a different database is specified in the transaction SQL.
    Recommendation: Specify SECADMIN, which contains all databases that the administrator needs to access except DBC tables.
    FALLBACK
    [Optional] Directs the system to automatically create a duplicate of each table stored in the database space, to provide backup in the event of a failure.
    Recommendation: The data in secadmin is critical, so you should specify FALLBACK to set up security tables for automatic duplication.

    You can specify other syntax options in the initial CREATE USER statement or later in a MODIFY USER statement.

  4. Submit the following SQL statements to grant user secadmin the basic privileges recommended by Teradata for security administrators.
    The example SQL statements below provide secadmin with the privileges recommended for the duties defined in About Security Administrator Responsibilities. You can assign some or all of these privileges to the database administrator in addition to, or instead of, the security administrator, as required by site security policy.
    GRANT USER ON SECADMIN TO SECADMIN               /* maintain users */ ;
    GRANT ROLE TO SECADMIN                           /* maintain roles */ ;
    GRANT PROFILE TO SECADMIN                        /* maintain profiles */ ;
    GRANT SELECT ON DBC TO SECADMIN                  /* select on dictionary tables */;
    GRANT UPDATE ON DBC.SysSecDefaults TO SECADMIN   /* password characteristics */  ;
    GRANT EXECUTE ON DBC.LogonRule TO SECADMIN       /* logon rules */ ;
    GRANT EXECUTE ON DBC.AccLogRule TO SECADMIN      /* access logging */ ;
    GRANT DELETE ON DBC.AccLogTbl TO SECADMIN        /* delete audit entries */  ;
    GRANT DELETE ON DBC.DeleteAccessLog TO SECADMIN  /* delete audit entries */  ;
    GRANT DELETE ON DBC.EventLog TO SECADMIN         /* delete event log */  ;
  5. Log off as user DBC.
  6. Immediately log back onto Teradata Vantage as user secadmin. Create a private password at the prompt.
  7. Enter the following SQL statement to log the attempt to execute of any BEGIN/END LOGGING or GRANT/REVOKE LOGON statement:
    BEGIN LOGGING WITH TEXT ON EACH ALL ON MACRO DBC.LogonRule, MACRO DBC.AccLogRule ;
  8. Log off Teradata Vantage.
    You can assign other privileges to the security administrator based on system needs.