Database Security Inspection Checklist

Overview


This document describes how to identify and resolve any potential database vulnerabilities. Many elements of this document are derived directly from previous customer inquiries.

Account Management


This chapter introduces account management practices such as the deletion of unnecessary accounts and periodic password changes.

Account List

All database accounts should be audited periodically to determine if they have the relevant permissions to access the database. Accounts should also be reviewed regularly to determine if current permissions are appropriate. Any offending accounts should be deleted by the DBA or the application staff.

[View a list of existing database users]

iSQL> SELECT user_name FROM SYSTEM_.SYS_USERS_ ;

[Delete a database user account]

iSQL> DROP USER user_name ; — Delete account

iSQL> DROP USER user_name CASCADE ; — Delete account and all associated objects

Vulnerable Passwords

All user passwords should be changed periodically for security reasons. The SYS account that is created during the installation of Altibase is particularly important as it has full authority for database object creation and other database operations. The SYS user’s password is ‘manager’ by default and must be changed after Altibase is installed.

[Connect to the SYS user]

iSQL> CONNECT sys/manager ;

Connect success.

[Modify the SYS user’s password]

iSQL> ALTER USER sys IDENTIFIED BY password1234$;

Alter success.

The passwords for general user accounts can be changed by using the ALTER USER command. However, to change the SYS user’s password the altiPasswd utility must also be invoked for security reasons. Refer to the Utility Manual for more information.

Authority Management


This chapter introduces the principle of authority management, which involves the deletion or modification of database permissions according to user requirements.

[Check a user’s privileges]

iSQL> SELECT a.user_name grantee,c.user_name grantor,REPLACE(d.priv_name, ‘_’, ‘ ‘) priv_name
FROM system_.sys_users_ a,system_.sys_grant_system_ b,system_.sys_users_ c, system_.sys_privileges_ d
WHERE c.user_name<> ‘SYSTEM_’
AND b.grantee_id = a.user_id AND b.grantor_id = c.user_id
AND b.priv_id = d.priv_id ;             — Confirmation of user system authority
iSQL> SELECT DECODE(priv_type, 1, ‘object’, ‘system’) priv_type, priv_name
FROM system_.sys_privileges_
ORDER BY 1;                                       — A priv_type value of system refers to system authority

[Remove a privilege from a user]

iSQL> REVOKE create table FROM user1 ; — Revoke the create table privilege from the user1 user

WITH GRANT OPTION

The WITH GRANT OPTION is a function that allows a user to grant access to an object to other user accounts. Therefore, this privilege must be granted sparingly in order to prevent unauthorized access to the database. Users with this privilege should be audited regularly for security purposes.

[Identify users with the WITH GRANT OPTION privilege]

iSQL>SELECT DISTINCT(a.user_name) grantee, c.user_name grantor, b.obj_type,b.obj_id,d.priv_name, b.with_grant_option
FROM system_.sys_users_ a, system_.sys_grant_object_ b, system_.sys_users_ c, system_.sys_privileges_ d
WHERE a.user_name <> ‘SYSTEM_’
AND b.grantee_id = a.user_idAND B.grantor_id = c.user_idAND b.priv_id = d.priv_id
AND b.with_grant_option = 1;

[Revoke or grant permissions on a table for user1]

iSQL> REVOKE select, delete ON employees FROM user1 ;

iSQL> GRANT select, delete ON employees FROM user1 ;

Environment File Check


Files such as the Altibase properties file, log anchor files and logfiles are mandatory for Altibase`s operation. If any of these files are improperly modified, Altibase may fail or behave abnormally. Therefore, access privileges should be set to make sure that these files can only be modified by authorized users.

altibase.properties File Permissions

The altibase.properties file is used to configure various database properties. Therefore, any inappropriate changes to this file may have an adverse effect on the database. File permissions should be set to either 600 or 400 for security purposes.

[Verify the altibase.properties file’s current file permissions]

$ ls –l $ALTIBASE_HOME/conf/altibase.properties

-rw-r–r– 1 altibase altibase 27652 2014-09-26 12:52 /data/altibase/altibase_home/conf/altibase.properties

[Modify the altibase.properties file’s permissions]

$ find $ALTIBASE_HOME -name altibase.properties -exec chmod 600 {} \;

$ ls –l $ALTIBASE_HOME/conf/altibase.properties

-rw——- 1 altibase altibase 27652 2014-09-26 12:52 /data/altibase/altibase_home/conf/altibase.properties

Loganchor, Logfile, and Datafile File Permissions

File permissions to critical files such as the loganchor files, logfiles and datafiles must be restricted in order to prevent database downtime. Unexpected behavior may occur if any of these files are improperly modified or deleted.

[Verify file permissions]

$ ls -l $ALTIBASE_HOME/logs/loganchor*

-rw-r—– 1 altibase altibase 14156 2014-09-26 12:53 /data/altibase/altibase_home/logs/loganchor0

-rw-r—– 1 altibase altibase 14156 2014-09-26 12:53 /data/altibase/altibase_home/logs/loganchor1

-rw-r—– 1 altibase altibase 14156 2014-09-26 12:53 /data/altibase/altibase_home/logs/loganchor2

[Set appropriate file permission levels]

$ find $ALTIBASE_HOME -name altibase.properties -exec chmod 600 {} \;

$ ls -l $ALTIBASE_HOME/logs/loganchor*

-rw——- 1 altibase altibase 14156 2014-09-26 12:53 /data/altibase/altibase_home/logs/loganchor0

-rw——- 1 altibase altibase 14156 2014-09-26 12:53 /data/altibase/altibase_home/logs/loganchor1

-rw——- 1 altibase altibase 14156 2014-09-26 12:53 /data/altibase/altibase_home/logs/loganchor2

Shell History File Inspection

In addition, it is important to note that the history of shell commands may persist in the .sh_history file. Therefore, it is possible for users with access to that file to obtain user access credentials if they are entered directly as command line arguments. Users should refrain from using usernames and passwords as options when invoking iSQL for this reason.

[Obtain user credentials by checking commandline arguments in the .sh_history file]

$ isql -u sys -p manager -s 127.0.0.1 -port 31109

$ grep isql ~/.sh_history                          — Can confirm account and password

[Invoke iSQL without using command line arguments]

$ isql

Write Server Name (default:127.0.0.1) :

Write UserID : sys                             — Account input

Write Password :                             — Password input

ISQL_CONNECTION = TCP, SERVER = 127.0.0.1, PORT_NO = 20300

iSQL>

DBMS Security Setup


This section introduces the use of public synonyms and user account access control methods to improve database security.

Public Synonyms

Public Synonym is an object that is automatically created for convenience purposes. These synonyms should not be dropped because they are used frequently for the purpose such as querying the dual table or running print statements. However, these synonyms can be dropped via the DROP clause if the user is certain that they will not be used.

[Identify existing public synonyms]

iSQL> SELECT object_owner_name, synonym_name
FROM system_.sys_synonyms_
WHERE object_owner_name = ‘SYSTEM_’;

[Drop unnecessary public synonym]

iSQL> DROP PUBLIC SYNONYM synonym_name ;

Account Lock Policy Setup

Account locking policies enable enhanced database security by enforcing rules such as account lockouts, password change intervals and password complexity requirements.

[Verify a user’s account policy fields]

iSQL>SELECT user_name, failed_login_attempts, password_lock_time FROM system_.sys_users_;

— FAILED_LOGIN_ATTEMPTS: The account is locked if the number of failed login attempts exceeds this value

— PASSWORD_LOCK_TIME: The period of time that the account is locked

— If 0, lock is not set

The FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME properties can be added to the $ALTIBASE_HOME/conf/altibase.properties file. After setting these properties to the desired value, they will be applied to all user accounts.  This method requires a database restart in order for the policies to take effect.

[Setup using database properties]

iSQL> SELECT name, value1 FROM v$property WHERE name IN (‘failed_login_attempts’, ‘password_lock_time’);

The account lock policy can be configured at the time of user creation using the CREATE USER clause. A database restart is not required, and this method can be used to set user-specific locking policies.

[Setup during account creation]

iSQL> CREATE USER user1 IDENTIFIED BY user1 LIMIT (failed_login_attempts 3, password_lock_time 3);

The ALTER USER statement can also be used to configure account lock policies.

[Setup using the ALTER USER statement]

iSQL> ALTER USER user1 LIMIT (failed_login_attempts5, password_lock_time 5);

Configuring Password Complexity

Short or simple passwords represent a serious security risk because they can be easily guessed or brute forced. Therefore, Altibase provides the functionality to enforce password complexity rules in order to force user’s to adopt secure passwords. A user callback function is utilized for this purpose.

[Verify the current password verification function]

iSQL>SELECT user_name, password_verify_function FROM system_.sys_users_;

— PASSWORD_VERIFY_FUNCTION: Password complexity degree. NULL means not set.

[Configure a password verification function for a user]

iSQL>CREATE USER user1 IDENTIFIED BY user1 LIMIT (password_verify_functionverify_func) ;

[Create a password verification callback function]

CREATE OR REPLACE FUNCTION pwd_verify_function

( username varchar(20), password varchar(20))

RETURN varchar(100)

AS
result       varchar(100);
pwdLength     integer;
isDigit       boolean;

isChar       boolean;

isPunction   boolean;

digitArray   varchar(20);

punctionArray varchar(25);

charArray     varchar(52);

BEGIN

digitArray    := ‘0123456789’;

charArray     := ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’;

punctionArray :=’!”#$%&()“*+,-/:;<=>?_’;

— Check if the password is same as the username

IF LOWER(password) = LOWER(username) THEN

result := ‘Password same as or similar to user’;

RETURN result;

END IF;

— Check for the minimum length of the password

IF LENGTH(password) < 4 THEN

result := ‘Password length less than 4’;

RETURN result;

END IF;

— Check if the password is too simple.

IF LOWER(password) IN (‘welcome’, ‘database’, ‘account’, ‘user’, ‘password’, ‘altibase’, ‘computer’, ‘abcd’) THEN

result := ‘Password too simple’;

RETURN result;

END IF;

— Check if the password contains at least one letter, one digit and one

— punctuation mark.

— 1. Check for the digit

isDigit := FALSE;

pwdLength := length(password);

FOR i IN 1…10 LOOP

FOR j IN 1…pwdLength LOOP

IF substr(password,j,1) = substr(digitArray,i,1) THEN

isDigit := TRUE;

GOTO findchar;

END IF;

END LOOP;

END LOOP;

IF isDigit = FALSE THEN

result := ‘Password should contain at least one digit, one character and one punctuation mark’;

RETURN result;

END IF;

— 2. Check for the character

<>

isChar := FALSE;

FOR i IN 1…length(charArray) LOOP

FOR j IN 1…pwdLength LOOP

IF substr(password,j,1) = substr(charArray,i,1) THEN

isChar := TRUE;

–GOTO findpunct;

END IF;

END LOOP;

END LOOP;

IF isChar = FALSE THEN

result := ‘Password should contain at least one digit, one character and one punctuation mark’;

RETURN result;

END IF;

— 3. Check for the punctuation mark

<>

isPunction := FALSE;

FOR i IN 1…length(punctionArray) LOOP

FOR j IN 1…pwdLength LOOP

IF substr(password,j,1) = substr(punctionArray,i,1) THEN

isPunction := TRUE;

GOTO endsearch;

END IF;

END LOOP;

END LOOP;

IF isPunction = FALSE THEN

result := ‘Password should contain at least one digit, one character and one punctuation mark’;

RETURN result;

END IF;

<>

result := ‘TRUE’;

RETURN result;

END;

/

Configuring Password Change Intervals

Password change intervals can be configured in order to force users to update their passwords at predefined time intervals. Frequent password changes are critical for security purposes.

[Verify the current password change interval]

iSQL>SELECT user_name, password_life_time FROM system_.sys_users_;

 — password_life_time: Unit is day, if 0, not set.

Add the PASSWORD_LIFE_TIME property to the $ALTIBASE_HOME/conf/altibase.properties file and restart the database.

[Setup using database properties]

iSQL>SELECT name, value1 FROM v$property WHERE name = ‘PASSWORD_LIFE_TIME’;

The password change interval can be configured during account creation via the LIMIT clause.

[Setup during user creation]

iSQL> CREATE USER user1 IDENTIFIED BY user1 LIMIT (password_life_time 5);

The ALTER USER statement can also be used to add or modify a password change interval.

[Setup using the ALTER USER statement]

iSQL> ALTER USER user1 LIMIT (password_life_time7);

Server Port Modification

Altibase`s server port is set to 20300 by default. This port can be modified if desired.

[Verify ALTIBASE HDB’s current server port number]

iSQL> SELECT name, value1 FROM v$property WHERE name = ‘PORT_NO’ ;

[Modify ALTIBASE HDB’s server port number]

Update the value for the PORT_NO property in the $ALTIBASE_HOME/conf/altibase.properties file and then restart the database.

Session IDLE_TIMEOUT

The IDLE_TIMEOUT setting can be changed at the session level. This is true even if the property is set at the server level.

[Verify the IDLE_TIMEOUT property value]

iSQL>SELECT name, value1 FROM v$property WHERE name = ‘IDLE_TIMEOUT’;  — Confirm the timeout value

iSQL>SELECT db_username, idle_time_limit, comm_name, client_app_info, client_pid FROM v$session;

[Modify the IDLE_TIMEOUT property value]

iSQL>ALTER SESSION SET idle_timeout = 60;  — Alter the current session’s timeout value.

iSQL>ALTER SYSTEM SET idle_timeout = 60;    — Alter the timeout value for all sessions.

Audit Configuration for Statements and Objects

Altibase’s auditing functionality can be used to audit database statements and access history. The AUDIT clause can be used to audit specific content. Please refer to the section on Database Auditing in the Administrator’s Manual or the Data Control section in the SQL Reference for more information.

[Verify currently audited objects]

iSQL> SET vertical on ;

iSQL> SELECT * FROM system_.sys_audit_opts_;       — Confirm currently audited objects

USER_NAME : ALTITEST
OBJECT_NAME : AUD1
OBJECT_TYPE : TABLE
SELECT_OP : -/-
INSERT_OP : A/A
UPDATE_OP : -/-
DELETE_OP : -/-
MOVE_OP : -/-
MERGE_OP : -/-
ENQUEUE_OP : -/-
DEQUEUE_OP : -/-
LOCK_TABLE_OP : -/-
EXECUTE_OP : -/-
COMMIT_OP : -/-
ROLLBACK_OP : -/-
SAVEPOINT_OP : -/-
CONNECT_OP : -/-
DISCONNECT_OP : -/-
ALTER_SESSION_OP : -/-
ALTER_SYSTEM_OP : -/-
DDL_OP : -/-

1 row selected.

iSQL> SELECT * FROM system_.sys_audit_;         — Confirm that auditing is currently active

IS_STARTED    START_TIME     STOP_TIME      RELOAD_TIME

—————————————————————————————-

1                 23-JUN-2014      23-JUN-2014

1 row selected.

Limit of DB access from remote

Altibase can restrict or allow to access according to the address after inspecting IP packet for DB access.By using this method, you can restrict access from risky IP and strengthen security with this function.

[Check method]

You can confirm ACCESS_LIST property setup value from $ALTIBAE_HOME/conf/altibase.properties.

[Update the ACCESS_LIST property]

The ACCESS_LIST property can be set in the $ALTIBASE_HOME/conf/altibase.properties file. The database must be restarted for the changes to take effect.

Restricting Remote SYSDBA Access

Altibase can be configured to prevent SYSDBA logins from remote servers.

[Verify whether remote SYSDBA access is enabled]

iSQL>SELECT name, value1 FROM v$property WHERE name = ‘REMOTE_SYSDBA_ENABLE’;

[Disable remote SYSDBA access]

iSQL>ALTER SYSTEM SET remote_sysdba_enable = 0;

Security Patches


This chapter introduces methods for patching Altibase to the latest available version.

Applying Security Patches

The user should regularly check the client support portal at “support.altibase.com” to ensure that they are running the latest available version of Altibase. Altibase regularly releases patches that may contain security or stability updates.

Copyright ⓒ 2000~2015 ALTIBASE Corporation. All Rights Reserved.

These documents are for informational purposes only. These information contained herein is not warranted to be error-free and is subject to change without notice. Decisions pertaining to ALTIBASE HDB’s product characteristics, features and development roadmap are at the sole discretion of Altibase. Altibase may own related patents, trademarks, copyright or other intellectual property rights of products and/or features discussed in this document.