One aspect of TimesTen access control is authentication of each database user through the use of passwords. This chapter discusses users and passwords in TimesTen, covering these topics:
Note:
Examples in this chapter use the TimesTenttIsql utility, indicated by the Command> prompt.To protect access to a TimesTen database, users must be created with appropriate passwords.
There are these types of users in TimesTen:
Administrative users: The instance administrator is the user who created the TimesTen instance. The instance administrator must be a member of the TimesTen users group and has full privileges within the instance. For additional information, see "Instance administrator" and "Understanding the TimesTen users group" in Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide.
Other uses can have administrative capabilities by being granted the ADMIN privilege. This can be granted by the instance administrator or by another user with ADMIN privilege.
TimesTen system users: The system users SYSTEM (for internal use), SYS (a schema for system objects), and TTREP (for replication) are created during TimesTen installation, for internal use only.
Internal users: An internal user and associated password are defined within a TimesTen database. The user must authenticate with the specified password for access to that database. You can create an internal user with the CREATE USER statement.
External users: An external user is created within the operating system but must be a member of the TimesTen users group. External users are assumed to have been authenticated by the operating system upon login, so there is no stored password within the database. TimesTen uses the operating system credentials of the external user to enable connection to TimesTen as that user. An external user must be identified to the database through the CREATE USER ... IDENTIFIED EXTERNALLY statement.
An external user cannot be used for TimesTen Client/Server unless the client and server are on the same host.
Notes:
For additional information, see "Understanding the TimesTen users group" in Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide and "CREATE USER" in Oracle TimesTen In-Memory Database SQL Reference.
When an external user connects from a Linux or UNIX system, TimesTen converts the user name to upper case, rendering it case-insensitive.
This section discusses TimesTen features for managing database users, covering the following:
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 only through a TimesTen direct connection. (See "CREATE USER" and "ALTER USER" in Oracle TimesTen In-Memory Database SQL Reference for details about these statements.)
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, 2020-02-25 12:00:17.027100, <NULL>, <NULL> > 1 row found.
For details on these views, see "SYS.ALL_USERS", "SYS.USER_USERS", and "SYS.DBA_USERS" in the Oracle TimesTen In-Memory Database System Tables and Views Reference.
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.
Once you have defined the user name and password for a TimesTen Client/Server connection, through the UID and PWD connection attributes, you can specify them in the following ways in order to connect to the database:
In a client DSN in the odbc.ini file.
In the connection string.
For additional information, see "UID and PWD" in Oracle TimesTen In-Memory Database Reference.
Note:
Client/Server supports setting or changing a password (throughCREATE USER, ALTER USER, or the ttCacheUidPwdSet built-in procedure) only for connections using TLS.If the UID connection attribute setting is specified for a TimesTen utility but no PWD attribute setting is specified, either in the connection string or the odbc.ini file, TimesTen prompts for a password.
For additional information, see "UID and PWD" in Oracle TimesTen In-Memory Database Reference.
Notes:
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.
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 information about this statement.
For example:
Command> DROP USER terry; User dropped.
Notes:
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.
To use TimesTen Cache, you must create the following users on the Oracle Database:
A cache administration user who creates, owns, and maintains Oracle Database objects that store information used to manage the cache environment for a TimesTen database and enforce predefined behaviors of particular cache group types.
One or more schema users who own the Oracle Database tables to be cached in a TimesTen database. These may be existing users or new users.
To use TimesTen Cache, you must create the following users on the TimesTen database:
A cache manager user who performs cache group operations. The TimesTen cache manager user must have the same user name as one of the Oracle Database users created for cache who can access the cached Oracle Database tables. This Oracle Database user, identified as the "companion" Oracle Database user, can be the cache administration user, a schema user, or some other existing user. For ease of use, it is preferred to have the Oracle Database cache administration user be the companion user for the TimesTen cache manager user. The password of the cache manager user can be different from the password of the companion Oracle Database user.
One or more cache table users who own the cache tables. You must create a TimesTen cache table user with the same user name as each Oracle Database schema user who owns or will own Oracle Database tables to be cached in the TimesTen database. The password of a cache table user can be different from the password of the Oracle Database schema user with the same name.
The owner and name of a TimesTen cache table is the same as the owner and name of the corresponding cached Oracle Database table.
One of the prerequisites to setting up your cache environment is informing the TimesTen database of the cache administration user name and password in the Oracle database.
Start the ttIsql utility and connect to the cache1 DSN (for example) as the cache manager user, and set:
The UID connection attribute to specify the cache manager user name.
The PWD connection attribute to specify the cache manager user password.
The OraclePWD connection attribute to specify the password of the companion Oracle database user.
This example uses ttIsql to connect, where cacheuser is the cache manager user with password timesten. In this example, the cache administration user, whose password is oracle, is the companion user to the cache manager user, so that password is provided.
% ttIsql "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Use the ttCacheUidPwdSet built-in procedure (only once) to inform the TimesTen database of the cache administration user name and password in the Oracle database.
The cache administration user name is cacheuser and its password is oracle.
Command> call ttCacheUidPwdSet('cacheuser','oracle');
For full details on creating users for TimesTen Cache, see "Setting up the Oracle Database and TimesTen Classic systems" and "Setting Up a Caching Infrastructure" in Oracle TimesTen Application-Tier Database Cache User's Guide.
For information about required privileges for the cache administration user, cache manager user, and cache users, see "Privileges for cache groups".
This section provides an overview of password management features and mechanisms in TimesTen that increase the level of security that can be implemented for authentication, covering these topics:
This section provides an overview of password management features to enhance the security of your TimesTen database:
You can limit how long a user can continue to use the same password before it expires, as well as a grace period after that period of time. During the grace period, the password is still allowed and recognized, but with a warning.
While limiting password lifetimes enhances system security, allowing users to frequently reuse previous passwords diminishes the effectiveness. When a user is changing their password, you can specify:
A minimum period of time that must pass before a previous password can be reused.
The number of password changes that must occur before a previous password can be reused.
Both of these must be satisfied before a password can be reused. For example, if PASSWORD_REUSE_TIME is 30 and PASSWORD_REUSE_MAX is 10, the user can reuse a password after 30 days if it is not one of the last 10 passwords used.
If one or the other is set to unlimited, a password can never be reused, but if both are set to unlimited, there are no limits on how often a password can be reused.
Hackers may try to access TimesTen by repeatedly guessing passwords until one works. You can limit the number of failed attempts that are allowed and how long the account is locked after this maximum number is reached.
TimesTen employs profiles to specify settings of the password management parameters for the features described in the preceding section: PASSWORD_LIFE_TIME, PASSWORD_GRACE_TIME, PASSWORD_REUSE_TIME, PASSWORD_REUSE_MAX, FAILED_LOGIN_ATTEMPTS, and PASSWORD_LOCK_TIME.
The same profile can be used for multiple users, and there is a default profile. A user who is not assigned a profile will use the default profile. Also, a setting of DEFAULT for any parameter in a profile will result in use of the value from the default profile.
The CREATE PROFILE SQL statement creates a profile. Specify PROFILE in a CREATE USER statement to assign an existing profile to a user.
See "CREATE PROFILE" or "ALTER PROFILE" in Oracle TimesTen In-Memory Database SQL Reference for details about the password management parameters. See "CREATE USER" or "ALTER USER" for information about specifying a profile when creating or altering a user.