Introduction

TimesTen XE SQL Profiles

SQL profiles in TimesTen XE let you define the policies for how user passwords are created, reused, and validated. The syntax and semantics for SQL profiles are the same in both TimesTen XE and TimesTen. This blog provides background information about SQL Profiles and includes a complete walk-through of examples showing you how to use SQL profiles.
 

Syntax for the SQL Profile Statements

TimesTen XE supports the CREATE PROFILE,ALTER PROFILE, and DROP PROFILE SQL statements.

Here is the syntax:
 

CREATE PROFILE

create_profile::=

CREATE PROFILE syntax


password_parameters::=

 

PASSWORD PARAMETERS syntax

 

ALTER PROFILE

alter_profile::=

ALTER PROFILE syntax


password_parameters::=

 

PASSWORD PARAMETERS syntax

 

DROP PROFILE

drop_profile::=

DROP PROFILE syntax


Note: For details about the syntax and semantics for SQL profiles, see CREATE PROFILE in the Oracle TimesTen In-Memory Database SQL Reference.

 

DEFAULT Profile

The TimesTen XE distribution includes the DEFAULT SQL profile. TimesTen XE assigns the DEFAULT profile to any user who does not have a SQL profile. You can query the SYS.DBA_PROFILES system view to view the password limits for the DEFAULT profile.
 

Command> SELECT resource_name, limit from SYS.DBA_PROFILES WHERE profile='DEFAULT' AND resource_type='PASSWORD';
< FAILED_LOGIN_ATTEMPTS, 10 >
< PASSWORD_LIFE_TIME, UNLIMITED >
< PASSWORD_REUSE_TIME, UNLIMITED >
< PASSWORD_REUSE_MAX, UNLIMITED >
< PASSWORD_COMPLEXITY_CHECKER, NULL >
< PASSWORD_LOCK_TIME, .0034 >
< PASSWORD_GRACE_TIME, UNLIMITED >
7 rows found.

 

SYS.DBA_PROFILES System View

TimesTen XE provides the SYS.DBA_PROFILES system view. This view describes the SQL profiles and the values of the password parameters for each of the profiles.
 

Command> describe SYS.DBA_PROFILES;

View SYS.DBA_PROFILES:
  Columns:
    PROFILE            VARCHAR2 (30) INLINE NOT NULL
    RESOURCE_NAME      VARCHAR2 (32) INLINE NOT NULL
    RESOURCE_TYPE      VARCHAR2 (8)  INLINE NOT NULL
    LIMIT              VARCHAR2 (40) INLINE

1 view found.

 

Password Complexity Checker Functions

TimesTen XE provides three password complexity checker functions. Specify one of these three functions in the PASSWORD_COMPLEXITY_CHECKER clause of the CREATE or ALTER PROFILE statement. These functions enforce varying degrees of complexity for a user password. This ensures users of TimesTen XE databases create strong, secure, and complex passwords. Having strong passwords protect against intruders who try to access a TimesTen XE database by guessing passwords.
 

Password hacker or intruder


The following table describes the three password complexity checker functions:

password complexity checker functions


Task 1: Create a User Without a Profile

Let’s create a user without a SQL profile. TimesTen XE assigns the DEFAULT profile to this user. Then, let’s verify that the user has the DEFAULT profile. Finally, let’s review the password limits for the DEFAULT profile. Note: TimesTen XE lets you modify the password limits in the DEFAULT profile.
 

1. Create the testuser1 user and grant CONNECT privileges to this testuser1 user.

Command> CREATE USER testuser1 IDENTIFIED BY test123;

User created.

Command> GRANT CONNECT TO testuser1;


TimesTen XE assigns the DEFAULT profile to testuser1. The password is test123.
 

2. Use the SYS.DBA_USERS system view to verify that the testuser1 user is assigned the DEFAULT profile.

Command> SELECT profile FROM SYS.DBA_USERS WHERE username='TESTUSER1';
<DEFAULT >
1 row found.



3. Use the SYS.DBA_PROFILES system view to review the password limits for the DEFAULT profile.

Command> SELECT resource_name,limit from SYS.DBA_PROFILES WHERE
profile='DEFAULT' AND resource_type='PASSWORD';
< FAILED_LOGIN_ATTEMPTS, 10 >
< PASSWORD_LIFE_TIME, UNLIMITED >
< PASSWORD_REUSE_TIME, UNLIMITED >
< PASSWORD_REUSE_MAX, UNLIMITED >
< PASSWORD_COMPLEXITY_CHECKER, NULL >
< PASSWORD_LOCK_TIME, .0034 >
< PASSWORD_GRACE_TIME, UNLIMITED >
7 rows found.

 

Task 2: Create a Profile for the User

Let’s create a profile for the testuser1 user, setting specific password limits. Then, let’s alter the testuser1 account and assign the newly created profile to the testuser1 user.
 

1. Create the profile1 profile and specify specific limits for the password parameters.

Command> CREATE PROFILE profile1 LIMIT
FAILED_LOGIN_ATTEMPTS 2
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 3
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 10
PASSWORD_COMPLEXITY_CHECKER TT_STRONG_VERIFY_FUNCTION;

Profile created.


The profile1 profile specifies the TT_STRONG_VERIFY_FUNCTION for the PASSWORD_COMPLEXITY_CHECKER clause. Users who are assigned this profile must ensure their passwords are strong enough to meet the TT_STRONG_VERIFY_FUNCTION requirements.
 

2. Use the ALTER USER statement to assign the profile1 SQL profile to the testuser1 user.

Command> ALTER USER TESTUSER1 PROFILE profile1;

User altered.



3. Use the ALTER USER statement to change the testuser1 password.

Command> ALTER USER testuser1 IDENTIFIED BY test1234;
15186: Password complexity check for the specified password failed
15188: TT-20001: Password length less than 9
The command failed.


The operation fails. The password does not meet the TT_STRONG_VERIFY_FUNCTION requirements. The password must contain at least nine characters. Of these nine characters, there must be at least two uppercase characters, at least two lowercase characters, at least two digits, and at least two special characters.


4. Use the ALTER USER statement to change the testuser1 password for a second time.

Command> ALTER USER testuser1 IDENTIFIED BY TesT1234##;

User altered.


The operation succeeds. The password meets the TT_STRONG_VERIFY_FUNCTION requirements. Make a note of the password. You need it in the next task.

 

Task 3: Test the Limits of the SQL Profile

Recall that the profile1 profile specified a limit of 2 for the FAILED_LOGIN_ATTEMPTS password parameter. Let’s test to see if TimesTen XE correctly enforces this limit.


1. As the testuser1 user, attempt to connect to the database1 database. On each attempt, specify an incorrect password.

$ ttIsql -connstr "DSN=database1;UID=testuser1;PWD=test123";

Copyright (c) 1996, 2022, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


connect "DSN=database1;UID=testuser1;PWD=********";
7001: User authentication failed
The command failed.
Done.


The first attempt fails.

 

$ ttIsql -connstr "DSN=database1;UID=testuser1;PWD=test123test123";

Copyright (c) 1996, 2022, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


connect "DSN=database1;UID=testuser1;PWD=********";
7001: User authentication failed
The command failed.
Done.


The second attempt fails.

 

$ ttIsql -connstr "DSN=database1;UID=testuser1;PWD=test1234test1234";

Copyright (c) 1996, 2022, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


connect "DSN=database1;UID=testuser1;PWD=********";
15179: the account is locked
The command failed.
Done.


The third attempt fails. The FAILED_LOGIN_ATTEMPTS parameter in the profile1 profile is set to 2. This limit was exceeded when you attempted to connect to the database for the third time. TimesTen XE correctly locks the testuser1 user account.


2. As a user with ADMIN privileges, use the ALTER USER statement to unlock the testuser1 user account. Then, exit from ttIsql.

Command> ALTER USER testuser1 ACCOUNT UNLOCK;

User altered.

Command> exit
Disconnecting...
Done.


TimesTen unlocks the testuser1 user account. Alternatively, as the testuser1 user, you could have connected to the database with the correct password after one day. This is because the ACCOUNT_LOCK_TIME parameter in the profile1 profile is set to 1.


3. As the testuser1, attempt to connect to the database1 database with the correct password. Then, exit from ttIsql.

$ ttIsql -connstr "DSN=database1;UID=testuser1;PWD=TesT1234##";
 

Copyright (c) 1996, 2022, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

 

connect "DSN=database1;UID=testuser1;PWD=********";
Connection successful: DSN=database1;UID=testuser1;DataStore=/scratch/dblocation/database1;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;LogBufMB=1024;PermSize=500;TempSize=300;
(Default setting AutoCommit=1)

Command> exit
Disconnecting...
Done.


The connection succeeds.

 

Task 4: Clean Up

Let’s experiment with dropping the profile1 profile. To drop a profile that has a user assigned to it, you must use the DROP PROFILE statement with the CASCADE keyword.


1. Use the SYS.DBA_USERS system view to review the users who are assigned the profile1 profile.

Command> SELECT username FROM SYS.DBA_PROFILES WHERE profile = 'PROFILE1';
<TESTUSER1>
1 row found.



2. Drop the profile1 profile.

Command> DROP PROFILE profile1;
15178: Profile PROFILE1 has users assigned, cannot drop without CASCADE
The command failed.


TimesTen XE does not drop the profile if there are users assigned to it.


3. To force TimesTen XE to drop the profile, use the DROP PROFILE statement with the CASCADE keyword.

Command> DROP PROFILE profile1 CASCADE;

Profile dropped.


TimesTen XE drops the profile1 profile and assigns the DEFAULT profile to the testuser1 user.


4. Verify that the testuser1 user has the DEFAULT profile.

Command> SELECT profile FROM SYS.DBA_USERS WHERE username='TESTUSER1';
<DEFAULT>
1 row found.



5. Optional: Drop the testuser1 user. Then, exit from ttIsql.

Command> DROP USER testuser1;

User dropped.

Command> exit
Disconnecting...
Done.

 

Summary

SQL profiles place restrictions and limits on the passwords of TimesTen XE database users, providing an increased level of security when accessing TimesTen XE databases. Continue learning about the tools to manage, troubleshoot, and control SQL profiles in TimesTen XE databases with Part 2 of this blog series.

 

Learn More about TimesTen XE SQL Profiles

 

Learn More About TimesTen XE

 

Additional TimesTen XE Blogs

 


Disclaimer: These are my personal thoughts and do not represent Oracle’s official viewpoint in any way, shape, or form.