docs/provisioning_guide/src/asciidoc/_chapters/enable_security.adoc

////
/**
* @@@ START COPYRIGHT @@@
*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements.  See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership.  The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License.  You may obtain a copy of the License at
*
*   http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied.  See the License for the
* specific language governing permissions and limitations
* under the License.
*
* @@@ END COPYRIGHT @@@
*/
////

[[enable-security]]
= Enable Security

{project-name} supports user authentication with LDAP, integrates with Hadoop's Kerberos environment and
supports authorization through database grant and revoke requests (privileges).

If this is an initial installation, both LDAP and Kerberos can be configured by running the {project-name} installer.
If {project-name} is already installed, then both LDAP and Kerberos can be configured by running the {project-name} 
security installer. 

* If Hadoop has enabled Kerberos, then {project-name} must also enable Kerberos.
* If Kerberos is enabled, then LDAP must be enabled.
* If LDAP is enabled, then database authorization (privilege support) is automatically enabled.
* If Kerberos is not enabled, then enabling LDAP is optional.

[[enable-security-kerberos]]
== Configuring {project-name} for Kerberos
Kerberos is a protocol for authenticating a request for a service or operation.  It uses the notion of a ticket to verify accessibility.  
The ticket is proof of identity encrypted with a secret key for the particular requested service.  Tickets exist for a short time and 
then expire. Therefore, you can use the service as long as your ticket is valid (i.e. not expired).  Hadoop uses Kerberos to provide 
security for its services, as such {project-name} needs to function properly with Hadoop that have Kerberos enabled.  

=== Kerberos configuration file
It is assumed that Kerberos has already been set up on all the nodes by the time Trafodion is installed. 
This section briefly discusses the Kerberos configuration file for reference.

The Kerberos configuration file defaults to /etc/krb5.conf and contains, among other attributes:

```
* log location: location where Kerberos errors and other information are logged
* KDC location: host location where the KDC (Key Distribution Center) is located
* admin server location: host location where the Kerberos admin server is located
* realm: the set of nodes that share a Kerberos database 
* ticket defaults: contains defaults for ticket lifetimes, encoding, and other attributes
```

You need to have access to a Kerberos administrator account to enable Kerberos for Trafodion.  The following is an example request that lists principals defined in the Kerberos database that can be used to test connectivity: 

```
kadmin -p 'kdcadmin/admin' -w 'kdcadmin123' -s 'kdc.server' -q 'listprincs'
* -p (principal): please replace 'kdcadmin/admin' with your admin principal
* -w (password): please replace 'kdadmin123' with the password for the admin principal
* -s (server location): please replace 'kdc.server' with your KDC admin server location
* -q (command): defines the command to run, in this case principals are returned
```
=== Ticket Management
When Kerberos is enabled in {project-name}, the security installation process:

* Adds a Trafodion principal in Kerberos, one per node with the name trafodion/hostname@realm.
* Creates a keytab for each principal and distributes the keytab to each node. The keytab name is the same for all nodes 
and defaults to a value based on the distribution, for example: etc/trafodion/keytabs/trafodion.service.keytab.
* Performs a "kinit" on all nodes in the cluster for the `trafodion` user.
* Adds commands to perform "kinit" and to start the ticket renewal procedure to the `trafodion` .bashrc scripts on each node. 

The ticket renewal service renews tickets up until the maximum number of renewals allowed.  So if your ticket lifetime is 
one day and the number of renewals is seven days, the ticket renewal service automatically renews tickets six times.  Once 
the ticket expires, it must be initialized again to continue running Trafodion.  Connecting to each node as the `trafodion` 
user initializes the ticket if one does not exist.

TBD - A future update will include details on how tickets can be managed at the cluster level.

=== Kerberos installation
The {project-name} installation scripts automatically determine if Kerberos is enabled on the node.  If it is enabled,
then the environment variable SECURE_HADOOP is set to "Y".

The following are questions that will be asked related to Kerberos:

* Enter KDC server address, default is []: – no default
* Enter admin principal (include realm), default is []:  - no default
* Enter password for admin principal:

NOTE: KDC admin password will be saved only in configuration file `db_config.bakYYMMDD_HHMM` in installer folder when installation completed.
You can delete this file for secure perspective.
NOTE: Keytab files are auto detected by installer in CDH/HDP cluster.
NOTE: Installer doesn't support kerberos enabled Apache Hadoop for this release.

[[enable-security-ldap]]
== Configuring LDAP
{project-name} does not manage user names and passwords internally but supports authentication via directory servers using
the OpenLDAP protocol, also known as LDAP servers. You can configure the LDAP servers during installation by answering the {project-name}
Installer's prompts. To configure LDAP after installation run the {project-name} security installer directly.  Installing LDAP also enables database
authorization (privilege support).

Once authentication and authorization are enabled, {project-name} 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). {project-name} also supports component-level (or system-level)
privileges, such as MANAGE_USERS, which can be granted to users and roles. Refer to <<enable-security-manage-users,Manage Users>> below.

NOTE: If you do not enable LDAP in {project-name}, then a client interface to {project-name} may request a user name and password,
but {project-name} ignores the user name 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 to certain users only, or restrict access to an
object or operation, then you must enable security, which enforces authentication and authorization.

[[enable-security-configuring-ldap-servers]]
=== 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 `$TRAF_HOME/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 <<enable-security-traf-authentication-config,.traf_authentication_config>> below.

A sample template file is located in `$TRAF_HOME/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.

```
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.

NOTE: Section names, attribute names, and the general layout of the authentication configuration file are subject to change in future versions
of {project-name} and backward compatibility is not guaranteed.

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

[cols="15%h,55%,30%a",options="header"]
|===
| Setting | Description | Example
| LDAP Host Name(s) | One or more names of hosts that support the OpenLDAP protocol must be specified. {project-name} attempts to connect to all
provided host names during the authentication process. The set of user names and passwords should be identical on all hosts to avoid unpredictable
results. The attribute name is `LDAPHostName`. | `LDAPHostName: ldap.company.com`
| 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`. | `LDAPPort: 389`
| LDAP Unique Identifier | Attribute(s) used by the directory server that uniquely identifies the user name. You may provide one or more unique
identifier specifiers. | `UniqueIdentifier: uid=,ou=users,dc=com`
| Encryption Level | A numeric value indicating the encryption scheme used by your LDAP server. Values are: +
 +
&#8226; 0: Encryption not used +
&#8226; 1: SSL +
&#8226; 2: TLS | `LDAPSSL: 2` +
 +
If your LDAP server uses TLS you must specify a file containing the certificate used to encrypt the password. By default the {project-name} software
looks for this file in `$TRAF_HOME/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. +
 +
*Example* +
```
TLS_CACERTFilename: mycert.pem 
TLS_CACertFilename: /usr/etc/cert.pem
```
| Search username and password | Some LDAP servers require a known user name and password to search the directory of user names. If your environment
has that requirement, provide these "search" values. | `LDAPSearchDN: lookup@company.com` +
`LDAPSearchPwd: Lookup123`
|===

There are additional optional attributes that can be used to customize {project-name} authentication. As mentioned earlier, they are described in
<<enable-security-traf-authentication-config,.traf_authentication_config>> below.

You can test the authentication configuration file for syntactic errors using the `ldapconfigcheck` tool. If you have loaded the {project-name}
environment (`sqenv.sh`), then the tool automatically checks the file at `$TRAF_HOME/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, then the line number with the error is displayed along with the error.
Please refer to <<enable-security-ldapconfigcheck,ldapconfigcheck>> below for more information.

NOTE: The authentication configuration file needs to be propagated to all nodes, but there is a script that does 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 {project-name} environment must be loaded (`sqenv.sh`),
but the {project-name} instance does not need to be running. To test the connection only, you can specify any user name, and a name lookup is 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. Please refer to <<enable-security-ldapcheck,ldapcheck>> for more information.

If you supply a password, `ldapcheck` attempts 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 
prompts for the password with no echo.

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

<<<
[[enable-security-generate-trafodion-certificate]]
=== Generate {project-name} Certificate
{project-name} clients such as `trafci` encrypt the password before sending it to {project-name}. A self-signed certificate is used to encrypt the password.
The certificate and key are generated when the `sqgen` script is invoked. By default, the files `server.key` and `server.crt` are located
in `$HOME/sqcert`. If those files are not present and since {project-name} clients does not send unencrypted passwords, then you need to manually generate
those files. To do so, run the script `sqcertgen` located in `$TRAF_HOME/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
```

[cols="40%l,60%",options="header"]
|===
| Option                           | Description
| -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 is 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, then {project-name} supports environment variables
to specific the alternate locations or names.

* {project-name} first checks the environment variables `SQCERT_PRIVKEY` and `SQCERT_PUBKEY`. If they are set, {project-name} 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, {project-name} checks the value of the environment variable `SQCERT_DIR`. If set,
then 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,
then {project-name} uses the default location (`$HOME/sqcert`) and the default filename.

[[enable-security-traf-authentication-config]]
=== Creating the LDAP configuration file
The `.traf_authentication_config` file is user to enable the Trafodion security features.

==== File Location
By default, the {project-name} authentication configuration file is located in `$TRAF_HOME/sql/scripts/.traf_authentication_config`.
If you want to store the configuration file in a different location and/or use a different filename, then Trafodion supports environment
variables to specify the alternate location/name.

Trafodion firsts checks the environment variable `TRAFAUTH_CONFIGFILE`. If set, the value is used as the fully-qualified Trafodion
authentication configuration file.

If the environment variable is not set, then Trafodion next checks the variable `TRAFAUTH_CONFIGDIR`. If set, the value is prepended to
`.traf_authentication_config` and used as the Trafodion authentication file.

If neither is set, Trafodion defaults to `$TRAF_HOME/sql/scripts/.traf_authentication_config`.

<<<
[[enable-security-template]]
==== Template

```
# To use authentication in Trafodion, this file must be configured
# as described below and placed in $TRAF_HOME/sql/scripts and be named
# .traf_authentication_config.  
#
# NOTE: the format of this configuration file is expected to change in the 
# next release of Trafodion.  Backward compatibility is not guaranteed.
#
SECTION: Defaults
  DefaultSectionName: local
  RefreshTime: 1800
  TLS_CACERTFilename:
SECTION: local 

# If one or more of the LDAPHostName values is a load balancing host, list
# the name(s) here, one name: value pair for each host.
  LoadBalanceHostName: 

# One or more identically configured hosts must be specified here,  
# one name: value pair for each host.
  LDAPHostName:

# Default is port 389, change if using 636 or any other port
  LDAPPort:389

# Must specify one or more unique identifiers, one name: value pair for each
  UniqueIdentifier:

# If the configured LDAP server requires a username and password to 
# to perform name lookup, provide those here.  
  LDAPSearchDN:
  LDAPSearchPwd:
 
# If configured LDAP server requires TLS(1) or SSL (2), update this value
  LDAPSSL:0

# Default timeout values in seconds
  LDAPNetworkTimeout: 30 
  LDAPTimeout: 30 
  LDAPTimeLimit: 30
 
# Default values for retry logic algorithm
  RetryCount: 5 
  RetryDelay: 2 
  PreserveConnection: No
  ExcludeBadHosts: Yes  
  MaxExcludeListSize: 3
```

[[enable-security-configuration-attributes]]
==== Configuration Attributes

[cols="25%,20%,20%l,35%",options="header"]
|===
| Attribute Name    | Purpose                                  | Example Value   | Notes
| `LDAPHostName`    | Host name of the local LDAP server.      | ldap.master.com | If more than one `LDAPHostName` entry is provided,
then Trafodion attempts to connect with each LDAP server before returning an authentication error.
Also see the description related to `RetryCount` and `RetryDelay` entries.

| `LDAPPort`      | Port number of the local LDAP server.    | 345             | Must be numeric value. Related to `LDAPSSL` entry.
Standard port numbers for OpenLDAP are as follows: +
 +
&#8226; Non-secure: 389 +
&#8226; SSL: 636 +
&#8226;  TLS: 389

| `LDAPSearchDN` | If a search user is needed, the search user distinguished name is specified here. | cn=aaabbb, dc=demo, dc=net |
If anonymous search is allowed on the local server, then this attribute does not need to be specified or can be specified with no value (blank).
To date, anonymous search is the normal approach used.

| `LDAPSearchPWD` | Password for the `LDAPSearchDN` value. See that entry for details. | welcome | None.

| `LDAPSSL`      | A numeric value specifying whether the local LDAP server interface is unencrypted or TLS or SSL.
Legal values are 0 for unencrypted, 1 for SSL, and 2 for TLS. For SSL/TLS, see the section below on Encryption Support. | 0 | None.

| `UniqueIdentifier` | The directory attribute that contains the user's unique identifier. | uid=,ou=Users,dc=demo,dc=net |
To account for the multiple forms of `DN` supported by a given LDAP server, specify the `UniqueIdentifier` parameter multiple times
with different values. During a search, each `UniqueIdentifier` is tried in the order it is listed in the configuration file. 

| `LDAPNetworkTimeout` | Specifies the timeout (in seconds) after which the next `LDAPHostName` entry is tried, in case of no response for a connection request.
This parameter is similar to `NETWORK_TIMEOUT` in `ldap_conf(5)`. Default value is 30 seconds. | 20 |
The value must be a positive number or -1. Setting this to -1 results in an infinite timeout.

| `LDAPTimelimit` | Specifies the time to wait when performing a search on the LDAP server for the user name. The number must be a positive integer.
This parameter is similar to `TIMELIMIT` in `ldap_conf(5)`. Default value is 30 seconds. | 15 |
The server may still apply a lower server-side limit on the duration of a search operation.

| `LDAPTimeout` | Specifies a timeout (in seconds) after which calls to synchronous LDAP APIs aborts if no response is received.
This parameter is similar to `TIMEOUT` in `ldap_conf(5)`. Default value is 30 seconds. | 15 |
The value must be a positive number or -1. Setting this to -1 results in an infinite timeout.

| `RetryCount` | Number of attempts to establish a successful LDAP connection. Default is 5 retries before returning an error. | 10 |
When a failed operation is retried, it is attempted with each configured LDAP server, until the operation is successful or the number of configured retries is exceeded.

| `RetryDelay` | Specifies the number of seconds to delay between retries. Default value is 2 seconds. See description of `RetryCount`. | 1 | None.

| `PreserveConnection` | Specifies whether the connection to LDAP server is maintained (YES) or closed (NO) once the operation finishes. Default value is NO. | YES | None.

| `RefreshTime` | Specifies the number of seconds that must have elapsed before the configuration file is reread. Default is 1800 (30 minutes). | 3600 |
If set to zero, the configuration file is never read. The connectivity servers must be restarted for changes to take effect if this value is zero.
This attribute is not specific to either configuration and must be defined in the DEFAULTS section.

| `TLS_CACERTFilename` | Specifies the location of the certificate file for the LDAP server(s). Filename can either be fully qualified or relative to `$CACERTS_DIR`. | cert.pem |
This attribute applies to both configurations. If a configuration does not require a certificate, then this attribute is ignored.
This attribute must be defined in the DEFAULTS section.

| `DefaultSectionName` | Specifies the configuration type that is assigned to a user by the `REGISTER USER` command if no authentication type is specified.
In the initial Trafodion release, only one configuration is supported. | LOCAL | This attribute must be defined in the `DEFAULTS` section.
If the `DefaultSectionName` attribute is specified, then a section by that name (or equivalent) must be defined in `.traf_ldapconfig`.
Legal values are `LOCAL` and `ENTERPRISE`. This syntax is likely to change.
|===


[[enable-security-ldapcheck]]
=== Verifying configuration and users through ldapcheck

==== Usage

```
ldapcheck  [<option>]...
<option> ::= --help|-h            display usage information
             --username=<LDAP-username>
             --password[=<password>]
             --primary            Use first configuration
             --local              Use first configuration
             --enterprise         Use first configuration
             --secondary          Use second configuration
             --remote             Use second configuration
             --cluster            Use second configuration
             --verbose            Display non-zero retry counts
                                  and LDAP errors
```

==== Considerations

* Aliases for primary include enterprise and local. Aliases for secondary include cluster and remote. If no configuration is specified, primary is assumed.
* The equals sign is required when supplying a value to username or password.
* To be prompted for a password value with no echo, specify the password argument but omit the equals sign and value.
* Passwords that contain special characters may need to be escaped if the password is specified on the command line or within a script file.
* If the password keyword is not specified, only the username is checked. The tool can therefore be used to test the LDAP configuration and connection
to the configured LDAP server(s) without knowing a valid username or password.

[[enable-security-ldapconfigcheck]]
=== Verifying contents of configuration file through ldapconfigcheck
This page describes the `ldapconfigcheck` tool, which validates the syntactic correctness of a Trafodion authentication configuration file. Trafodion does not need to be running to run the tool.

[[enable-security-ldapconfigcheck-considerations]]
==== Considerations
If the configuration filename is not specified, then the tool  looks for a file using environment variables. Those environment variables and the search order are:

1. `TRAFAUTH_CONFIGFILE`
+
A fully qualified name is expected.

2. `TRAFAUTH_CONFIGDIR`
+
Filename `.traf_authentication_config/` is appended to the specified directory

3. `TRAF_HOME`
+
`/sql/scripts/.traf_authentication_config` is appended to the value of `TRAF_HOME`.
    
<<<
[[enable-security-ldapconfigcheck-errors]]
==== Errors
One of the following is output when the tool is run. Only the first error encountered is reported.

[cols="15%l,85%",options="header"]
|===
| Code   | Text
| 0      | File `filename` is valid.
| 1      | File `filename` not found.
| 2      | File: `filename` +
 +
Invalid attribute name on line *line-number*.
| 3      | File: `filename` +
 +
Missing required value on line *line-number*.
| 4      | File: `filename` +
 +
Value out of range on line *line-number*.
| 5      | File: `filename` +
 +
Open of traf_authentication_config file failed.
| 6      | File: `filename` +
 +
Read of traf_authentication_config file failed.
| 7      | No file provided. Either specify a file parameter or verify environment variables.
| 8      | TLS was requested in at least one section, but `TLS_CACERTFilename` was not provided.
| 9      | Missing host name in at least one section. +
 +
Each LDAP connection configuration section must provide at least one host name.
| 10     | Missing unique identifier in at least one section. +
 +
Each LDAP connection configuration section must provide at least one unique identifier. 
| 11     | At least one LDAP connection configuration section must be specified.
| 12     | Internal error parsing `.traf_authentication_config`.
|===

[[enable-security-manage-users]]
== Manage Users
Kerberos is enabled for installations that require a secure Hadoop environment.  LDAP is enabled to enforce authentication for any 
user connecting to {project-name}.  The {project-name} database enforces privileges on the database, database schemas, database 
objects (table, views, etc) and database operations.  Privileges are enforced when authorization is enabled.  When LDAP or Kerberos 
is enabled, authorization is automatically enabled.  

To determine the status of authentication and authorization, bring up sqlci and perform "env;". 

```
>>env;
----------------------------------
Current Environment
----------------------------------
AUTHENTICATION     enabled
AUTHORIZATION      enabled
CURRENT DIRECTORY  /.../trafodion/install/installer
LIST_COUNT         4294967295
LOG FILE
MESSAGEFILE        /.../trafodion/core/sqf/export/ ...
MESSAGEFILE LANG   US English
MESSAGEFILE VRSN   {2016-06-14 22:27 LINUX:host/user} 
SQL CATALOG        TRAFODION
SQL SCHEMA         SCH
SQL USER CONNECTED user not connected
SQL USER DB NAME   SQLUSER1
SQL USER ID        33367
TERMINAL CHARSET   ISO88591
TRANSACTION ID
TRANSACTION STATE  not in progress
WARNINGS           on
```

Once authorization is enabled, there is one predefined database user called DB__ROOT associated with your specified LDAP username.
Please connect to the database and this user and register users that will perform database admin management. The database
admin can then connect and setup required users, roles, and privileges.

TBD - A future update should include a pointer to the security best practices guide.

To learn more about how to register users, grant object and component privileges, and manage users and roles, please see the
{docs-url}/sql_reference/index.html[Trafodion SQL Reference Manual].