You should create the principal security administrator, SECADMIN, to carry out initial setup of database security.
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 Teradata 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.
- Create the security administrator user with a CREATE USER statement. The CREATE USER statement should assign a temporary security administrator password and set the size of the PERMANENT, SPOOL, and TEMPORARY space. For example:
CREATE USER SECADMIN FROM DBC AS PERM = perm_space PASSWORD = temp_password SPOOL = spool_space ACCOUNT = 'account' DEFAULT DATABASE = SECADMIN FALLBACK ;
Syntax Element Description SECADMIN Required. The name of the primary security administrator user. You can specify a different username, but all examples in this chapter refer to the security administrator as SECADMIN. DBC The owner of the space in which you create the SECADMIN user. perm_space Required. The space in bytes that contains all objects that user SECADMIN creates or owns.
Recommendation: Specify approximately 2-3% of available system (DBC) space, in which the security administrator can:
- Create users
- Create databases and tables for containing security-related data
For information on how to determine DBC space, see Creating the Spool Space Reserve.
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.
temp_password Required. 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.
'account' Optional. 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 Database Administration.
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.
- 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 Teradata Database as user DBC.
- Immediately log back onto Teradata Database 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 Database.You can assign other privileges to the security administrator based on system needs.