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:
- Log on to the database as user DBC.
- 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.
- 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.
- temp_password
- The temporary password for user secadmin.
- spool_space
- [Optional] Limits the portion of the spool reserve that secadmin can use to execute queries.
- 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.
- 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.
- 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.
You can specify other syntax options in the initial CREATE USER statement or later in a MODIFY USER statement.
- 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 */ ;
- Log off as user DBC.
- Immediately log back onto Teradata Vantage as user secadmin. Create a private password at the prompt.
- 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 ;
- Log off Teradata Vantage.You can assign other privileges to the security administrator based on system needs.