6.6 Connect to Query Server
Query Server supports both Kerberos and password-based database authentication.
6.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.
6.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:
- Enable TLS, if not already enabled:
- Login to Cloudera Manager by using your login credentials
- In the list of available services, click the Big Data SQL link to display the Big Data SQL details page.
- From the Status Summary section, click the Big Data SQL Query Server link to display the Big Data SQL Query Server details page.
- From the Actions drop-down list, select Enable TLS.
- 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
- On the client node, edit the
sqlnet.ora
file by setting the wallet location to the folder containing the client wallet. - On the client side set
TNS_ADMIN
to point to thewallet/sqlnet.ora/tnsnames.ora
location.
6.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.
-
Users can use the
bdsql
user to connect to theBDSQLUSR
PDB as follows:> sqlplus BDSQL/<bdsql_password>@BDSQLUSR
Note:
Substitute<bdsql_password>
in the above command with the actualBDSQL
password that the administrator specified during the Oracle Big Data SQL installation. -
Obtain the client wallet for TLS-enabled connections. For instructions, see Copy the Client Wallet for TLS Connections.
-
You can create additional database users using the
DBMS_BDSQS_ADMIN
package. See DBMS_BDSQS_ADMIN PL/SQL Package.For example, to create usersbdsql_user1
,bdsql_user2
,bdsql_user3
you can connect asSYS
and use:> alter session set container=BDSQLUSR; > exec DBMS_BDSQS_ADMIN.ADD_DATABASE_USERS('bdsql_user1, bdsql_user2, bdsql_user3')
-
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:
TheDBMS_BDSQS_ADMIN
package is the only supported way of creating/dropping database users on the Query Server. You should not create database users using theCREATE USER
statement.
Note:
Thebdsql
user is disabled on secure (Kerberos)
clusters.
6.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
.
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.
6.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.
- Obtain the Kerberos ticket.
For example, in order to connect using principal bdsql_user@MYCLUSTER.COM, you can use:
> kinit bdsql_user@MYCLUSTER.COM > sqlplus /@BDSQLUSR
- Obtain the client wallet for TLS-enabled connections. For instructions, see Copy the Client Wallet for TLS Connections.
- 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 thebds-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 principalsbdsql_user1@MYCLUSTER.COM
,bdsql_user2@MYCLUSTER.COM
, andbdsql_user3@MYCLUSTER.COM
, you can invoke (when connected asSYS
):> alter session set container=BDSQLUSR; > exec DBMS_BDSQS_ADMIN.ADD_KERBEROS_PRINCIPALS( 'bdsql_user1@MYCLUSTER.COM, bdsql_user2@MYCLUSTER.COM, bdsql_user3@MYCLUSTER.COM');
-
Note:
TheDBMS_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.
6.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.
-
Obtain the client wallet for TLS-enabled connections. For instructions, see Copy the Client Wallet for TLS Connections..
-
Connect to the Query Server using the client username and password.
For example, in order to connect as userbdsql_user1
you can use:> connect bdsql_user1/"<password>"@BDSQLUSR
6.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
DBMS_BDSQS_ADMIN
package first switch to the
BDSQLUSR PDB
by
running:> alter session set container=BDSQLUSR;
Note:
Altering theSYS/SYSTEM
user passwords is not supported on the Query
Server.