Enable Security Features in Trafodion

NOTE: Starting in Trafodion Release 1.1, during the installation of Trafodion, you will be prompted to enable security. If you wish to enable security manually or would like to understand how security in Trafodion works, please continue reading.

If you do not enable security in Trafodion, a client interface to Trafodion may request a username and password, but Trafodion ignores the username and password entered in the client interface, and the session runs as the database root user, DB__ROOT, without restrictions. If you want to restrict users, restrict access only to certain users, or restrict access to an object or operation, you must enable security, which enforces authentication and authorization. You can enable security during installation by answering the installer's prompts or after installation by running the traf_authentication_setup script, which enables both authentication and authorization. For more information, see Running the Authentication Setup Script.

Trafodion does not manage usernames and passwords internally but does support authentication via directory servers that support the OpenLDAP protocol, also known as LDAP servers. You can configure the LDAP servers during installation by answering the installer's prompts, or you can configure the LDAP servers manually after installation. For more information, see Configuring LDAP Servers.

Once authentication and authorization are enabled, Trafodion allows users to be registered in the database and allows privileges on objects to be granted to users and roles (which are granted to users). Trafodion also supports component-level (or system-level) privileges, such as MANAGE_USERS, which can be granted to users and roles. See Managing Users.

Configuring LDAP Servers

To specify the LDAP server(s) to be used for authentication, you need to configure the text file .traf_authentication_config, located (by default) in $MY_SQROOT/sql/scripts. This file is a flat file, organized as a series of attribute/value pairs. Details on all the attributes and values accepted in the authentication configuration file and how to configure alternate locations can be found in .traf_authentication_config details.

A sample template file is located in $MY_SQROOT/sql/scripts/traf_authentication_config.

Attributes and values in the authentication configuration file are separated with a colon immediately following the attribute name. In general white space is ignored, but spaces may be relevant in some values. Attribute names are always case insensitive. Multiple instances of an attribute are specified by repeating the attribute name and providing the new value. For attributes with only one instance, if the attribute is repeated, the last value provided is used.

Example:

Attribute1: valueA
Attribute2: valueB
Attribute1: valueC

If Attribute1 has only one instance, valueC is used, otherwise, valueA and valueC are both added to the list of values for Attribute1.

Attributes are grouped into sections; this is for future enhancements. Attributes are declared in the LOCAL section, unless otherwise specified.

Section names, attribute names, and the general layout of the authentication configuration file are subject to change in future versions of Trafodion and backward compatibility is not guaranteed.

Specification of your directory server(s) requires at a minimum:

1. LDAP Host name(s) 
   One or more names of hosts that support the OpenLDAP protocol must be specified. Trafodion will attempt to connect to all provided host names during the authentication process. The set of usernames and passwords should be identical on all hosts to avoid unpredictable results. The attribute name is LDAPHostName. 
   Example:

   LDAPHostName: ldap.company.com
   

2. LDAP Port number 
   Port number of the LDAP server. Typically this is 389 for servers using no encryption or TLS, and 636 for servers using SSL. The attribute name is LDAPPort. 
   Example:

   LDAPPort: 389
   

3. LDAP unique identifier 
   Attribute(s) used by the directory server that uniquely identifies the username. You may provide one or more unique identifier specifiers. 
   Example:

   UniqueIdentifier: uid=,ou=users,dc=com
   

4. Encryption level 
   A numeric value indicating the encryption scheme used by your LDAP server. Values are:

   0: Encryption not used1: SSL2: TLS

   Example:

   LDAPSSL: 2
   

   If your LDAP server uses TLS you must specify a file containing the certificate used to encrypt the password. By default the Trafodion software looks for this file in $MY_SQROOT/cacerts, but you may specify a fully qualified filename, or set the environment variable CACERTS_DIR to another directory. To specify the file containing the certificate, you set the value of the attribute TLS_CACERTFilename, located in the Defaults section. 
   Examples:

   TLS_CACERTFilename: mycert.pem
   TLS_CACertFilename: /usr/etc/cert.pem
   

5. Search username and password 
   Some LDAP servers require a known username and password to search the directory of usernames. If your environment has that requirement, provide these “search” values. 
   Examples:

   LDAPSearchDN: lookup@company.com
   LDAPSearchPwd: Lookup123
   

There are additional optional attributes that can be used to customize Trafodion authentication. As mentioned earlier, they are described in .traf_authentication_config details.

You can test the authentication configuration file for syntactic errors using the utility ldapconfigcheck. If you have loaded the Trafodion environment (sqenv.sh), then the utility will automatically check the file at$MY_SQROOT/sql/scripts/.traf_authentication_config. If not, you can specify the file to be checked.

Example:

ldapconfigcheck –file myconfigfile
File myconfigfile is valid.

If an error is found, the line number with the error is displayed along with the error. More information on the ldapconfigcheck utility can be found in ldapconfigcheck details.

Note: The authentication configuration file needs to be propagated to all nodes, but there is a script that will do that for you described later. For now, you can test your changes on the local node.

You can test the LDAP connection using the utility ldapcheck. To use this utility the Trafodion environment must be loaded (sqenv.sh), but the Trafodion instance does not need to be running. To test the connection only, you can specify any username, and a name lookup will be performed using the attributes in .traf_authentication_config.

ldapcheck --username=fakename@company.com
User fakename@company.com not found

If ldapcheck reports either that the user was found or the user was not found, the connection was successful. However, if an error is reported, either the configuration file is not setup correctly, or there is a problem either with your LDAP server or the connection to the server. You can get additional error detail by including the --verbose option. To learn more about ldapcheck, see ldapcheck details.

If you supply a password, ldapcheck will attempt to authenticate the specified username and password. The example below shows the password for illustrative purposes, but to avoid typing the password on the command line, leave the password blank (--password=) and the utility will prompt for the password with no echo.

ldapcheck --username=realuser@company.com –-password=StrongPassword
Authentication successful

Generating a Trafodion Server Certificate

Trafodion clients such as trafci will encrypt the password before sending it to Trafodion. A self-signed certificate is used to encrypt the password. The certificate and key should be generated when the sqgen script is invoked. By default, the files server.key and server.crt will be located in $HOME/sqcert. If those files are not present, since Trafodion clients will not send unencrypted passwords, you will need to manually generate those files. To do so, run the script sqcertgen located in $MY_SQROOT/sql/scripts. The script runs openssl to generate the certificate and key.

To run openssl manually, follow the example:

openssl req -x509 -nodes -days 365 -subj '/C=US/ST=California/L=PaloAlto/CN=host.domain.com/O=Some Company/OU=Service Connection' 
-newkey rsa:2048 -keyout server.key -out server.crt

• -x509 --> Generate a self-signed certificate.
• -days <validity of certificate> --> Make the certificate valid for the days specified.
• -newkey rsa:<bytes> --> Generate a new private key of type RSA of length 1024 or 2048 bytes.
• -subj <certificateinfo> --> Specify the information that will be incorporated in the certificate. Each instance in a cluster should have a unique common name(CN).
• -keyout <filename> --> Write the newly generated RSA private key to the file specified.
• -nodes --> It is an optional parameter that specifies NOT to encrypt the private key. If you encrypt the private key, then you must enter the password every time the private key is used by an application.
• -out <filename> --> Write the self-signed certificate to the specified file.

Both the public (server.crt) and private (server.key) files should be placed in the directory $HOME/sqcert. If you do not want to use the HOME directory or if you want to use different names for the private and/or public key files, see Alternate Trafodion Certificate Locations.

Alternate Trafodion Certificate Locations

By default, the private and public key files/certificate used to connect to Trafodion are located in $HOME/sqcert and names server.key and server.crt. If you want to store the files in a different location and/or use different filenames, Trafodion supports environment variables to specific the alternate locations or names.

Trafodion first checks the environment variables SQCERT_PRIVKEY and SQCERT_PUBKEY. If they are set, Trafodion uses the fully qualified filename value of the environment variable.

You can specify either one filename environment variable or both.

If at least one filename environment variable is not set, Trafodion checks the value of the environment variable SQCERT_DIR. If set, the default filename server.key or server.crt is appended to the value of the environment variable SQCERT_DIR.

If the filename environment variable is not set and the directory environment variable is not set, Trafodion uses the default location ($HOME/sqcert) and the default filename.

Running the Authentication Setup Script

The final step to enable security is to change the value of the environment variable TRAFODION_ENABLE_AUTHENTICATION from NO to YES and turn on authorization. This is achieved by invoking the traf_authentication_setupscript, which is located in $MY_SQROOT/sql/scripts.

The syntax is:

Usage: traf_authentication_setup [options]

Options:
    --file <loc>  Optional location of OpenLDAP configuration file
    --help        Prints this message
    --off         Disables authentication and authorization                               
    --on          Enables authentication and authorization
    --setup       Enables authentication                             
    --status      Returns status of authentication enablement

If a filename is provided by specifying the --file option, that file is copied to $MY_SQROOT/sql/scripts/.traf_authentication_config. Users working in their own private environment can refer to a site-specific configuration file from a central location.

When specifying --on, the script invokes ldapconfigcheck to verify the configuration file is syntactically correct. It also invokes ldapcheck to verify that a connection can be made to an LDAP server. If both checks pass, the script sets the environment variable TRAFODION_ENABLE_AUTHENTICATION to YES in the file $MY_SQROOT/sqenvcom.sh, and propagates sqenvcom.sh and .traf_authentication_config to all nodes in the cluster. The last step is to enable authorization by creating privilege-related metadata tables and set up default permissions with a call to the database. The list of privilege-related metadata tables, users, roles, and component privileges are logged in$MY_SQROOT/logs/authEnable.log. Specifying --on requires a valid .traf_authentication_config file to exist and the Trafodion metadata initialized.

If Trafodion metadata is not initialized, then the --setup option can be specified. This option enables authentication but does not call the database to create privilege-related metadata tables. Later, when Trafodion metadata is initialized, privilege-related metadata tables and default permissions are automatically created.

If --off is specified, the script sets the environment variable TRAFODION_ENABLE_AUTHENTICATION to NO in the file $MY_SQROOT/sqenvcom.sh and propagates the file to all the nodes in the cluster. The last step is to disable authorization by removing any privilege-related metadata and permissions with a call to the database. The results of this operation is logged in $MY_SQROOT/logs/authEnable.log.

The --status option reports the value of the environment variable TRAFODION_ENABLE_AUTHENTICATION in the file $MY_SQROOT/sqenvcom.sh on the current node and reports the status of security features in the database.

INFO: Start of security (authentication and authorization) script Wed Mar 25 15:12:50 PDT 2xxx.

INFO:  *** Trafodion security (authentication and authorization) status *** 
   Authentication is ENABLED
   Authorization (grant/revoke) is ENABLED

INFO: End of security (authorization and authentication) script Wed Mar 25 15:12:54 PDT 2xxx.

IMPORTANT: Any time the environment file (sqenvcom.sh) is changed (and propagated to all nodes), Database Connectivity Services (DCS) must be restarted to pick up the new value. If the configuration file is changed, it will be re-read in 30 minutes (by default), but you can have changes take effect immediately by restarting DCS.

To restart DCS, run the scripts stop-dcs.sh and start-dcs.sh, located in $MY_SQROOT/dcs-<x>.<y>.<z>/bin.

Managing Users

Users are registered in the Trafodion database and are used to enforce authorization. If security is disabled, any user can register any user at any time. However, once security is enabled, user administration is considered a secure operation, and registration of users is restricted to DB__ROOT or any user granted the MANAGE_USERS component privilege. To initially register a user, connect to Trafodion with the external user mapped to DB__ROOT (also known as the Trafodion ID).

When security is enabled, the DB__ROOT user is registered as the "TRAFODION" external username. It is recommended that the DB__ROOT user be mapped to the external username that will be used to connect for root operations. To do this, start a sqlci session and perform the ALTER USER command, for example:

ALTER USER DB__ROOT SET EXTERNAL NAME trafodion_rootuser_in_ldap;

To learn more about how to register users, grant object and component privileges, and manage users and roles, please see the Trafodion SQL Reference Manual (pdf, 3.98 MB)