Managing TimesTen Users

Creating or Identifying a Database User

An instance administrator or a user with the ADMIN privilege can create an internal user, identify an external user, or alter a user. These actions can be performed either through a TimesTen direct connection or over an encrypted client-server connection. (See Overview of TimesTen Users in this guide and CREATE USER and ALTER USER in Oracle TimesTen In-Memory Database SQL Reference.)

To create an internal user, provide the user name and password in the CREATE USER statement. The following example creates the internal user terry with the password secret:

Command> CREATE USER terry IDENTIFIED BY secret;
User created.

To identify an external user, provide the user name in the CREATE USER ... IDENTIFIED EXTERNALLY statement. The following example identifies the external user pat to the TimesTen database:

Command> CREATE USER pat IDENTIFIED EXTERNALLY;
User created.

To change the external user pat to an internal user, perform the following ALTER USER statement:

Command> ALTER USER pat IDENTIFIED BY secret;

To change the internal user pat to an external user, perform the following ALTER USER statement:

Command> ALTER USER pat IDENTIFIED EXTERNALLY;

You can see what users have been created by executing a SELECT statement on the following system views:

SYS.ALL_USERS lists all users of the database that are visible to the current user.
SYS.USER_USERS describes the current user of the database.
SYS.DBA_USERS describes all users of the database. To perform a select statement on this view, you must have the appropriate privileges granted.

For example, to see the current user, perform the following:

Command> SELECT * FROM sys.user_users;
< PAT, 4, OPEN, <NULL>, <NULL>, USERS, TEMP, 2021-02-25 12:00:17.027100, <NULL>, <NULL> >
1 row found.

Note:

You can run a CREATE or ALTER USER … IDENTIFIED BY SQL statement over a client/server connection only when TLS is used. The password is only encrypted when sent over a TLS connection.

See SYS.ALL_USERS, SYS.USER_USERS, and SYS.DBA_USERS in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

Changing the Password of an Internal User

An internal user can alter their password through the IDENTIFIED BY clause of the ALTER USER statement.

A user with the ADMIN privilege can alter the password of any user.

For example, to change the password for internal user TERRY to "12345":

Command> ALTER USER terry IDENTIFIED BY 12345;
User altered.

Providing a Client/Server User and Password

The preferred method of specifying a user name and password is by storing both in an Oracle Wallet. However, you can alternatively provide the user name and password in a client DSN or using connection attributes. Providing credentials in a wallet is more secure than supplying a password in a client DSN or on the connection string.

You first set or change a password through CREATE USER or ALTER USER SQL statements. See Creating or Identifying a Database User.

Once set or changed, you can provide the user and password to the TimesTen server through one of the following methods.

Providing a User Name and Password in an Oracle Wallet

The most secure method to provide credentials when connecting is to store a user's password in an Oracle Wallet. When connecting, you provide the user name and wallet to supply credentials for the connection. Supplying the user name identifies which user's password to retrieve from within the wallet.

There are user-managed and system-managed Oracle Wallets. The system-managed wallets are those that may be created by a user, but are used internally for internal procedures. This section discusses user-managed wallets that are used for connecting to a TimesTen database.

To create a user-managed wallet for providing credentials when connecting:

Create a directory to contain your wallet. For example, you could create a directory such as /wallets in which your user-managed wallet is stored.
The ttUser utility requires a full directory path in which to create a new Oracle Wallet or to identify an existing wallet. The name of a wallet cannot be specified. Thus, the wallet is identified by a unique full directory path. Provide the name of the wallet directory created above and a unique name for a subdirectory under it in which to place a single wallet to the ttUser utility.

Note:

You can store credentials for multiple users within a single Oracle wallet. For example, you could create a wallet in the /wallets/dsn1wallet directory. Multiple users credentials can be added into a wallet identified by /wallets/dsn1wallet.
The credentials from only one user can exist in a wallet. Thus, if you have a single user that has different passwords used to connect to separate DSNs, provide each credential within different wallets. For example, if user Terry has a password to connect to dsn1 and another password to connect to dsn2, then you could add Terry's passwords as appropriate to a wallet in the /wallets/dsn1wallet directory and to a wallet in the /wallets/dsn2wallet directory. Each wallet would have the appropriate passwords to connect to each DSN.

The ttUser utility performs the following:

If the location does not already exist, TimesTen creates the specified subdirectory and the wallet in the wallet directory location specified. The credentials are added to the Oracle Wallet.
If the wallet does exist but the user does not exist in the wallet, the ttUser utility adds the user and password to the wallet.
If the user credentials have already been added to an existing wallet, the password is changed for the user name provided.

The following example shows the user creating the /wallets directory to contain the wallet. The example assumes that /wallets/dsn1wallet does not exist. Thus, the ttUser utility creates the dsn1wallet subdirectory and then creates the Oracle Wallet in the /wallets/dsn1wallet directory. The ttUser utility prompts for the password for the user terry, which is then added to the wallet.

% mkdir /wallets
% ttUser -setPwd -wallet /wallets/dsn1wallet -uid terry
Enter password:

The following example shows how to add credentials for user terry into multiple wallets to access multiple TimesTen databases. For example, you would store TimesTen credentials for DSN1 (terry, pwd1) and DSN2 (terry, pwd2) in two separate wallets that exist in separate subdirectories under the wallets directory.

$ ttUser -setPwd -wallet /wallets/dsn1wallet -uid terry
Enter password:
$ ttUser -setPwd -wallet /wallets/dsn2wallet -uid terry
Enter password:

See ttUser in the Oracle TimesTen In-Memory Database Reference.

When it's time to authenticate a user to connect to a database, you provide the name of the user and the location of the corresponding wallet by using the UID and PwdWallet connection attributes. The UID connection attribute identifies which user to authenticate using the PwdWallet provided.

connect “dsn=mydb;uid=terry;PwdWallet=/wallets/dsn1wallet”;

For client/server connections, the wallet must exist on the client. See PwdWallet in the Oracle TimesTen In-Memory Database Reference.

You are required to secure and manage all wallets on your client or server. You can move the wallet to the location from which you want to connect. Once you no longer need the user credentials, you can remove these credentials from the wallet with ttUser -removePwd.

If the wallet does not exist or the PwdWallet connection attribute is not specified, then the order of precedence is to look for credentials provided in the connection string and then in the DSN.

Providing a User Name and Password in Connection Attributes

General connection attributes are set by each connection and exist for the duration of the connection. Each concurrent connection can have different values. You can provide the user name and password with the UID, PWD or PWDCrypt general connection attributes.

TimesTen uses the following order of precedence when locating the user name and password for connection authentication:

An Oracle Wallet with the user name and password. See Providing a User Name and Password in an Oracle Wallet.
The UID, PWD or PWDCrypt connection attributes provided in the connection string.
The UID, PWD or PWDCrypt connection attributes provided in the client DSN.

The UID, PWD and PWDCrypt connection attributes are as follows:

UID: Provides the user name to be used for the connection to the database, whether using a direct or client/server connection. To connect as the instance administrator or as an external user, you do not need to provide a user name. When you do not provide a user name, TimesTen assumes that the UID is the user name identified by the operating system.
PWD: Provides the password that corresponds with the specified UID. For internal users, if you do not set the PWD attribute in the odbc.ini file for the specified DSN or in the connection string, TimesTen prompts for the password. For external users, you do not provide the password as it is verified by the operating system.

When you initiate a client/server connection, the password sent for the connection is encrypted by the client/server protocol.
PWDCrypt: As an alternative to PWD, provides an encrypted password that corresponds with the specified UID.

Note:

For more information on the UID, PWD and PWDCrypt general connection attributes, see UID and PWD in the Oracle TimesTen In-Memory Database Reference. See Authentication in TimesTen in the Oracle TimesTen In-Memory Database Security Guide.

Once you have defined the user name and password for a client/server connection, through the UID and PWD connection attributes, you provide these connection attributes to connect to the database.

In the connection string.
In a client DSN in the odbc.ini file.

The following example is a connection request to database1 that provides the user name as Terry and the password as ttpwd in the connection attributes.

% ttIsql "DSN=database1;UID=terry;PWD=ttpwd"

Providing a User Name and Password in a Client DSN

You can specify the user name and password in the client DSN.

On Windows, you provide connection attributes in the Oracle TimesTen Client DSN Setup dialog. In this dialog, you can provide the User ID, Password and PWDCrypt connection attributes. If providing your password on this dialog, use either Password or PWDCrypt connection attributes. See Creating a Client DSN on Windows in the Oracle TimesTen In-Memory Database Operations Guide.

On Linux and UNIX, you provide connection attributes in the odbc.ini file. In the client DSN in the odbc.ini file, you can provide the UID, PWD, PWDCrypt, or PwdWallet connection attributes. To provide your password, use only one of the following connection attributes: Password, PWDCrypt, or PwdWallet.

The following is the syntax for the client DSN in the odbc.ini file:

[ODBC Data Sources]
Client_DSN=TimesTen 22.1 Client Driver

See Creating a DSN on Linux and UNIX for TimesTen Classic in the Oracle TimesTen In-Memory Database Operations Guide.

Providing a User and Password for TimesTen Utilities

You can provide the user name and password in an Oracle Wallet, the connection attributes, or in the odbc.ini file.

If a TimesTen utility takes a connection string, then you can provide the user name and password in an Oracle Wallet. Instead of providing a UID and PWD connection attribute on the command line, provide the PwdWallet connection attribute with the location and name of the wallet.

If the UID connection attribute setting is provided for a TimesTen utility but no PWD attribute setting is provided, either in the connection string or the odbc.ini file, TimesTen prompts for a password.

See UID and PWD in Oracle TimesTen In-Memory Database Reference.

Note:

When you enter a password at the prompt, what you type is not shown.
It is not advisable to specify a value for PWD on the command line.

Dropping a User From the Database

An instance administrator or a user with the ADMIN privilege can use the DROP USER statement to remove an internal or external user from the database. See DROP USER in Oracle TimesTen In-Memory Database SQL Reference.

For example:

Command> DROP USER terry;
User dropped.

Note:

You cannot drop a user who is still connected to the database or before all database objects owned by the user have been deleted.
TimesTen does not support DROP USER CASCADE.