Using authentication with AlwaysOn SQL

AlwaysOn SQL can be configured to use DSE authentication.

When DSE authentication is enabled, modify the hive-site.xml configuration file to enable JDBC authentication. DSE supports configurations for password authentication and Kerberos authentication. The hive-site.xml file has sections with preconfigured settings to use no authentication (the default), password authentication, or Kerberos authentication. Uncomment the preferred authentication mechanism, then restart AlwaysOn SQL.

Note: DSE supports multiple authentication mechanisms, but AlwaysOn SQL only supports one mechanism per datacenter.

AlwaysOn SQL supports DSE proxy authentication. The user who executes the queries is the user who authenticated using JDBC. If AlwaysOn SQL was started by user Amy, and then Bob begins a JDBC session, the queries are executed by Amy on behalf of Bob. Amy must have permissions to execute these queries on behalf of Bob.

To enable authentication in AlwaysOn SQL alwayson_sql_options, follow these steps.

hive-site.xml

For use with Spark, the default location of the hive-site.xml file is:

Package installations	/etc/dse/spark/hive-site.xml
Tarball installations	`installation_location`/resources/spark/conf/hive-site.xml

Procedure

Create the auth_user role specified in AlwaysOn SQL options and grant the following permissions to the role.

CREATE ROLE alwayson_sql WITH LOGIN=true; // role name matches auth_user 

// Required if scheme_permissions true
GRANT EXECUTE ON ALL AUTHENTICATION SCHEMES TO alwayson_sql; 

// Spark RPC settings
GRANT ALL PERMISSIONS ON REMOTE OBJECT DseResourceManager TO alwayson_sql;
GRANT ALL PERMISSIONS ON REMOTE OBJECT DseClientTool TO alwayson_sql;
GRANT ALL PERMISSIONS ON REMOTE OBJECT AlwaysOnSqlRoutingRPC to alwayson_sql;
GRANT ALL PERMISSIONS ON REMOTE OBJECT AlwaysOnSqlNonRoutingRPC to alwayson_sql;
  
// Spark and DSE required table access
GRANT SELECT ON system.size_estimates TO alwayson_sql;
GRANT SELECT, MODIFY ON "HiveMetaStore".sparkmetastore TO alwayson_sql;
GRANT SELECT, MODIFY ON dse_analytics.alwayson_sql_cache_table TO alwayson_sql; 
GRANT SELECT, MODIFY ON dse_analytics.alwayson_sql_info TO alwayson_sql;

// Permissions to create and change applications  
GRANT CREATE, DESCRIBE ON ANY WORKPOOL TO alwayson_sql;
GRANT MODIFY, DESCRIBE ON ANY SUBMISSION TO alwayson_sql;

See for more details.

Create the user role.
For internal authentication:
```
CREATE ROLE 'user_name' 
WITH LOGIN = true;
```
If you use Kerberos, set up a role that matches the full Kerberos principal name for each user.
```
CREATE ROLE 'user_name/example.com@EXAMPLE.COM' 
WITH LOGIN = true;
```

Grant permissions to access keyspaces and tables to the user role.

For internal roles:

GRANT SELECT ON KEYSPACE keyspace_name 
TO  'user_name';

For Kerberos roles:

GRANT SELECT ON KEYSPACE keyspace_name 
TO  'user_name/example.com@EXAMPLE.COM';

Allow the AlwaysOn SQL role (auth_user) to execute commands with the user role.

For internal roles:

GRANT PROXY.EXECUTE 
ON ROLE 'user_name' 
TO alwayson_sql;

For Kerberos roles:

GRANT PROXY.EXECUTE 
ON ROLE 'user_name/example.com@EXAMPLE.COM' 
TO alwayson_sql;

Open the hive-site.xml configuration file in an editor.

Uncomment and modify the authentication mechanism used in hive-site.xml.

If password authentication is used, enable password authentication in DSE.
If Kerberos authentication is to be used, Kerberos does not need to be enabled in DSE. AlwaysOn SQL must have its own service principal and keytab.
The user must have login permissions in DSE in order to login through JDBC to AlwaysOn SQL.

This example shows how to enable Kerberos authentication. Modify the Kerberos domain and path to the keytab file.

  <!-- Start of: configuration for authenticating JDBC users with Kerberos -->
    <property>
      <name>hive.server2.enable.doAs</name>
      <value>true</value>
    </property>

    <property>
      <name>hive.server2.authentication</name>
      <value>KERBEROS</value>
    </property>

    <property>
      <name>hive.server2.authentication.kerberos.principal</name>
      <value>hiveserver2/_HOST@KERBEROS DOMAIN</value>
    </property>

    <property>
      <name>hive.server2.authentication.kerberos.keytab</name>
      <value>path to hiveserver2.keytab</value>
    </property>
  <!-- End of: configuration for authenticating JDBC users with Kerberos -->

Modify the owner of the /spark and /tmp/hive directories in DSEFS so the new role can write to the log and temp files.

dse fs 'chown -R -u alwayson_sql -g alwayson_sql /spark'

dse fs 'chown -R -u alwayson_sql -g alwayson_sql /tmp/hive'

Restart AlwaysOn SQL.
```
dse client-tool alwayson-sql restart
```