5.6 Connect to Query Server

Query Server supports both Kerberos and password-based database authentication.

• About Connecting to the Query Server

• Copy the Client Wallet for TLS Connections

• Connect to Non-Secure Hadoop Clusters

• Connect to Secure Hadoop Clusters with Kerberos Authentication

• Connect to Secure Hadoop Clusters with Password-Based Database Authentication

• Administrative Connections

5.6.1 About Connecting to the Query Server

Users connect to a pluggable database (PDB) in the Query Server called BDSQLUSR. There are multiple ways to connect, depending on whether the cluster is non-secure or secure.

On non-secure clusters, users can connect using a database username/password. On secure clusters, users can connect to the database using both Kerberos principals and/or regular database users/passwords.

In both secure and non-secure clusters, database users must be created using the DBMS_BDSQS_ADMIN PL/SQL package. Furthermore, in unsecure clusters, the Query Server has a single pre-configured database user, called bdsql, whose password is set during the Query Server installation.

By default, the Query Server is configured to use TLS connections. In order to connect with TLS, you must first copy the client wallet from the Query Server to the client nodes. If TLS is disabled, the client wallet is not needed.

5.6.2 Copy the Client Wallet for TLS Connections

After installation, the Query Server is configured by default for TLS connections. The Query Server also has the client wallet needed by the client to connect using TLS. You need to copy the wallet to the client node, do some file modifications and set an environment variable before the client node is able to use TLS connections to the Query Server. If TLS is disabled, the client credentials are not needed.

To configure the client wallet for TLS connections:

1. Enable TLS, if not already enabled:
   1. Login to Cloudera Manager by using your login credentials
   2. In the list of available services, click the Big Data SQL link to display the Big Data SQL details page.
   3. From the Status Summary section, click the Big Data SQL Query Server link to display the Big Data SQL Query Server details page.
   4. From the Actions drop-down list, select Enable TLS.
2. Copy the following client files from the Query Server, located in directory /opt/oracle/bigdatasql/bdsqs/wallets/client, to the client node:
   • cwallet.sso
   • sqlnet.ora
   • tnsnames.ora
3. On the client node, edit the sqlnet.ora file by setting the wallet location to the folder containing the client wallet.
4. On the client side set TNS_ADMIN to point to the wallet/sqlnet.ora/tnsnames.ora location.

5.6.3 Connect to Non-Secure Hadoop Clusters

By default, the Query Server comes with a single pre-configured user called bdsql, whose password is set during Query Server installation.

1. Users can use the bdsql user to connect to the BDSQLUSR PDB as follows:

   > sqlplus BDSQL/<bdsql_password>@BDSQLUSR 

   Note:

   Substitute <bdsql_password> in the above command with the actual BDSQL password that the administrator specified during the Oracle Big Data SQL installation.

2. Obtain the client wallet for TLS-enabled connections. For instructions, see Copy the Client Wallet for TLS Connections.

3. You can create additional database users using the DBMS_BDSQS_ADMIN package. See DBMS_BDSQS_ADMIN PL/SQL Package.

   For example, to create users bdsql_user1, bdsql_user2, bdsql_user3 you can connect as SYS and use:

   > alter session set container=BDSQLUSR; 
   > exec DBMS_BDSQS_ADMIN.ADD_DATABASE_USERS('bdsql_user1, bdsql_user2, bdsql_user3')

   See Administrative Connections.

4. After creating the users, you must set their passwords and unlock them, for example:

   > alter user bdsql_user1 identified by "<user_password>" account unlock;

   Note:

   The DBMS_BDSQS_ADMIN package is the only supported way of creating/dropping database users on the Query Server. You should not create database users using the CREATE USER statement.

Note:

The bdsql user is disabled on secure (Kerberos) clusters.

5.6.3.1 Change the BDSQL User Password

The bdsql user's password is set during installation. After installation, you can change the password of the bdsql user using ALTER USER.

Use ALTER USER as follows to change the password:

> sqlplus bdsql/<old_password>@BDSQLUSR
> alter user bdsql identified by "<new_password>"

Note:

Substitute <new_password> with the new password. The new password must conform to the required Oracle secure password guidelines. See Choosing a Secure Password for information about choosing your new password.

5.6.4 Connect to Secure Hadoop Clusters with Kerberos Authentication

On secure clusters, you can connect using externally identified database users corresponding to Kerberos principals. Before connecting to the Query Server, you must authenticate with Kerberos using kinit.

To connect to Secure Hadoop Clusters with Kerberos Authentication:

1. Obtain the Kerberos ticket.
   For example, in order to connect using principal [e-mail], you can use:

   > kinit [e-mail]
   > sqlplus /@BDSQLUSR

2. Obtain the client wallet for TLS-enabled connections. For instructions, see Copy the Client Wallet for TLS Connections.
3. Add Kerberos principals as externally identified database users.

   Externally identified users can be created one of the following two ways:

   • When installing the Query Server on a secure cluster, the Big Data SQL installer can collect all principals from the cluster's Key Distribution Center (KDC). For each principal, an externally-identified database user will automatically be created on the Query Server. This install-time behavior is controlled by the syncPrincipals parameter in the bds-config.json configuration file. The same operation can be invoked after installation by running the following command on the installer node:

     jaguar sync_principals

   • You can also create externally identified users manually, using the DBMS_BDSQS_ADMIN PL/SQL package.

     For example, in order to create database users for principals [e-mail], [e-mail], and [e-mail], you can invoke (when connected as SYS):

     > alter session set container=BDSQLUSR;
     > exec DBMS_BDSQS_ADMIN.ADD_KERBEROS_PRINCIPALS(
       '[e-mail],
        [e-mail],
        [e-mail]'); 
     

Note:

The DBMS_BDSQS_ADMIN package is the only supported way of creating/dropping externally identified users on the Query Server. You should not create externally identified users using the CREATE USER statement.

5.6.5 Connect to Secure Hadoop Clusters with Password-Based Database Authentication

Besides using a Kerberos principal, you can also connect to the Query Server on a secure cluster as a database user with a password.

1. Obtain the client wallet for TLS-enabled connections. For instructions, see Copy the Client Wallet for TLS Connections..

2. Connect to the Query Server using the client username and password.

   For example, in order to connect as user bdsql_user1 you can use:

   > connect bdsql_user1/"<password>"@BDSQLUSR

5.6.6 Administrative Connections

The Query Server only supports local administrative connections with OS authentication.

To connect as SYS, from the Query Server node as OS user oracle, use:

> sqlplus / as sysdba 

In order to use the DBMS_BDSQS_ADMIN package first switch to the BDSQLUSR PDB by running:

> alter session set container=BDSQLUSR; 

Note:

Altering the SYS/SYSTEM user passwords is not supported on the Query Server.