Security class

When registering users, a System Administrator can set up a security class that specifies:

  • How often a password must be changed.

  • The length and style of password. (Note that passwords are encrypted.)

  • How many passwords must be used before the same one can be re-used.

  • How many consecutive login failures can occur before a user is suspended.

  • What period must elapse before an inactive user ID is suspended.

  • What period must elapse before an inactive session is forcibly terminated.

A System Administrator may set up several different classes of user, depending on their requirements and the sensitivity of data held on Kognitio. When you are registered as a recognized user on Kognitio, your System Administrator or DBA allocates you to a specific security class.

Note that IPE_ALLLOGIN must be in RAM for some security class restrictions to function (e.g. how long an account stays active if not used).

Establishing a security class

Details of the security classes are held in the system table IPE_SEC_CLASS, located in the SYS schema.

The System Administrator can specify the security classes to implement on your system, and then allocate each user ID to a given class. At system installation only the single DEFAULT class exists.

Setting up a new security class involves inserting the required values in the table IPE_SEC_CLASS, which you can do using the CREATE SEC_CLASS and ALTER SEC_CLASS commands.

After setting up the security class(es), the System Administrator can go on to allocate users to them. This is done using the CREATE USER and ALTER USER statements.

IPE_SEC_CLASS

The SQL command CREATE SEC_CLASS outlines how to create secrutiy classes. The following table lists the columns in IPE_SEC_CLASS, and explains what they mean.

Column Name

Data Type

Description

NAME

CHAR(32)

Security class name - UPPER CASE.

SEC_CLASS

INT

Unique security class ID.

PASSWD_MIN_LENGTH

INT

Minimum number of characters allowed in a password (0 - 32).

PASSWD_STYLE

INT

The required style of passwords

0 = no character restrictions.

1 = at least one alpha and one numeric character required.

2 = password is case sensitive.

3= combination of 1 and 2.

PASSWD_REPEAT_PRD

INT

Number of different passwords required before

a repeat is allowed.

PASSWD_CHANGE_PRD

INTERVAL

Number of days allowed before a password change is required. Can be NULL.

LOGIN_FAIL_MAX

INT

Consecutive login failures allowed before authorization is revoked. Can be NULL.

USER_INACTIVE_MAX

INTERVAL

Number of days a login can remain unused before revocation. Can be NULL.

SESS_INACTIVE_MAX

INTERVAL

Hours / minutes a session may remain inactive (no queries) before forced log off occurs. Can be NULL.

Note: Users are not warned that a session is to be terminated, an authorization removed or a password is about to expire. (This is due to the synchronous nature of the client/server interface.)

Examples

Example 1 - Setting up a security class

To set up the security class, JPW_SEC, with these characteristics:

  • Set the minimum number of characters permitted in a password to 5.

  • Set a password style specifying at least one alpha and one numeric character (1).

  • Set the number of times the password must be changed before a repeat is allowed to 5.

  • Ignore the number of days allowed before a password change is required.

  • Set the number of login failures permitted before access is revoked, that is, a user can fail this many times before they are effectively “locked out” to 7.

  • Ignore the number of days a login can remain unused before being revoked.

  • Ignore how long a session may remain inactive (no queries) before being disconnected.

do the following:

As SYS use SQL to set up a query to confirm that the name and id is unique by examining the contents of IPE_SEC_CLASS:

SELECT * FROM ipe_sec_class

Use CREATE SEC_CLASS to create the new security class:

CREATE SEC_CLASS JPW_SEC(
SEC_CLASS = 2,
PASSWD_MIN_LENGTH = 5,
PASSWD_STYLE = 1,
PASSWD_REPEAT_PRD = 5,
LOGIN_FAIL_MAX = 7)

Any incorrect logins, for example, using a password that is too short or which contains the wrong mix of characters, will result in an error message.

Example 2 – Updating a security class

If you want to update the security class so that users must change their passwords within seven days or be refused access to the system, you need to use ALTER SEC_CLASS as follows:

ALTER SEC_CLASS JPW_SEC (
PASSWD_CHANGE_PRD_DAYS = 7)

The updates take effect immediately and any user logging on must abide by the rules specified.

Example 3 – Creating a new user and allocating a security class

Create a new user, johnsmith, and allocate them to the JPW_SEC security class:

CREATE USER johnsmith PASSWORD johnsmith
SEC_CLASS JPW_SEC
SCHEMA johnsmith
DEFAULT SCHEMA johnsmith

Note: The name of the security class MUST be in UPPER CASE.

Example 4 – Altering a security class for a user

Change johnsmith’s security class to GEN_USER:

ALTER USER johnsmith SET SEC_CLASS GEN_USER

Identifying passwords that have expired or are about to expire

Use the following query to identify passwords that have expired or are close to expiry (within a week of expiry, in this example):

SELECT u.name AS username,
    passwd_change_prd AS prd,
    passwd_change_prd - (CURRENT_TIMESTAMP- PTIME) DAY(5) AS rem,
    s.name AS classname
FROM ipe_user u, ipe_sec_class s
WHERE u.sec_class = s.sec_class AND
passwd_change_prd IS NOT NULL AND
passwd_change_prd - (CURRENT_TIMESTAMP- PTIME)DAY(5) <
    INTERVAL '8' DAY
ORDER BY 2

Note: Due to the nature of the ODBC based client/server communication, the server cannot warn users that a password is due to expire.

Similar queries can be developed to find users who are barred due to submitting too many incorrect passwords.