Overview

Database security aims to prevent users from intentionally or accidentally manipulating the data stored in the database, which could compromise consistency or damage the entire database.

Auditing is a security technology that records the actions of specified users within the database. Administrators can more effectively protect the database by leaving separate logs for specific actions or specific users through the audit function.

Method

1. DB Security Management Principles

Based on the guidelines of the security group, establish DB security operation plans for security and information protection of the DB.

1.1. Mandatory Management Items

1) Verify change or removal of default accounts/passwords

sys/tibero, sysgis/sysgis, syscat/syscat, tibero1/tmax, tibero/tmax

Verification Method

Confirm using one of the following two methods.

• Check if login is possible by entering tbsql ID/PASSWD in the command window

• After executing select * from ALL_USERS; verify default IDs, then attempt to connect with tbsql ID/PASSWD and check if login is possible.

   

2) Apply strengthened DB passwords

Verify application of password usage rules

• At least 8 characters, mix of letters and numbers, no use of the same character 4 or more times consecutively

Verification Method

• If there is no profile, have the person in charge connect to the DBA account and general DB user accounts to check whether simple passwords allow access.
• If managed by profile, confirm that the password verification function defining password usage rules is set in the profile assigned to the user and check the validation rules of that function. (Refer to USER Profile application in section 4.7.3)

3) Prohibit general users from using DBA privileges

• Remove DBA privileges from general DB user accounts
• Verify revocation of DBA Role among general user accounts except DBA accounts

Verification Method

[Example] Query DBA privilege users

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE='DBA';

No general users other than SYS should appear in the results.

1.2. Target Management Items

1) Strengthen DB account management

Delete accounts that are unauthorized, created for testing purposes that have expired, or unnecessarily created among DB accounts. If deletion is difficult, lock the account so it cannot be used.

• Unauthorized accounts: retirees, transferred, on leave, contract terminated
• Expired test accounts: accounts created for application testing whose purpose has ended after the test period
• Unnecessary accounts: default accounts created during package installation but not used

Verification Method

[Example] Query locked users

SQL> SELECT USERNAME, LOCK_DATE FROM DBA_USERS;

2) Apply client IP access restrictions

Control access from IPs other than the Client IPs of the TIBERO_SID linked AP, administrators, developers, and other authorized DB users.

Verification Method

Check the setting "LSNR_INVITED_IP_FILE=$TB_HOME/invite_ip/invited.list" in the Tibero TIP file and verify the access IPs.

[Example] Authorized DB access client IP settings

$ TB_SID.tip

##################### 
##IP Access Control 
#####################

LSNR_INVAITED_IP_FILE=/tibero/DB/tibero7/invite_ip/invited.list

3) Minimize use of Database Links

Unused DB Links should be deleted, and DB Links should be operated minimally.

The purpose of DB LINKS identified by the verification method below should be documented and managed in the operator manual.

Verification Method

[Example] Query PRIVILEGE

SQL> 
SELECT * FROM 
DBA_SYS_PRIVS 
WHERE PRIVILEGE IN ('CREATE DATABASE LINK', 'CREATE PUBLIC DATABASE LINK');

4. Restrict read/write permissions to arbitrary paths or files in the system

• Restrict UTL_FILE package privileges from all users and grant only when necessary.
• Do not allow '*' or '.' in the utl_file_dir path accessible by UTL_FILE.
• Verification method (currently set to PUBLIC).

[Example] Check UTL_FILE package privileges

SQL> REVOKE EXECUTE ON UTL_FILE FROM PUBLIC; -- Grant only to necessary accounts

Revoked.

SQL> SELECT NAME, VALUE FROM V$PARAMETERS WHERE NAME = 'UTL_FILE_DIR'; 
-- If UTL_FILE is allowed, '*' or '.' should not appear in the results

2. Object Security

2.1. Account Encryption

Purpose

• Verify that user passwords are stored using encryption algorithms.

Current Environment

• Tibero stores user passwords using encryption by default.

Environment Setup Method

• New users must be created in compliance with group security regulations.
• Passwords must be at least 8 characters, mix letters and numbers, and prohibit use of the same character 4 or more times in a row or the same characters as the ID.

Operating Procedures

• To check account password encryption: connect to the DB via tbsql and run the query below to verify encryption of each DB user's password.

[Example] Check user password encryption

SQL> SELECT USERNAME, PASSWORD FROM DBA_USERS;

USERNAME                       PASSWORD 
------------------------------ ------------------------
SYS                            A6sRMebBoEvNcvxfFJIcVw== 
SYSCAT                         p45Ud4F6Fn7b7/ifeQD3bg== 
SYSGIS                         ZDn9sWH5kDMS9yLxF6UZcg== 
OUTLN                          vNhIe1qdJmGQvgq82nKdUA== 
TIBERO                         +N2TQ1DXn1cB93y5+z83RA== 
TIBERO1                        +N2TQ1DXn1cB93y5+z83RA== 
TEST                           Uyh8rWkIsW1CMqqftNt9aA==

7 rows selected.

3. DB Auditing

3.1. Overview of DB Auditing

Purpose

• Preemptive security records to strengthen post-security

The audit function is divided into two types depending on the audit target.

• Audit of Schema objects
  Records all operations performed on specified Schema objects.
• Audit of system privileges
  Records all operations using specified system privileges.

You can specify users or roles for which to keep audit trails and choose to record audit logs only for successful or failed operations. Also, you can specify to record audit logs once per session or every time an operation is performed.

3.2. Audit Management Methods

Current Environment

• Auditing can be managed dynamically or statically; static auditing is not configured.

Environment Setup Method

• Use the AUDIT or NOAUDIT commands to enable or disable auditing.

Operating Procedures

Setting and Disabling Auditing

1. Auditing Schema objects

To audit objects of schemas owned by other users or directory objects, the AUDIT ANY system privilege must be granted.

Below is an example of setting auditing for a Schema object.

[Example] Set auditing for Schema object

SQL> AUDIT DELETE ON TEST_TABLE BY SESSION WHENEVER SUCCESSFUL;

Audited.

If the above SQL succeeds, all successful DELETE statements on test_table will be recorded in the audit trail.

2. Auditing system privileges

The AUDIT SYSTEM system privilege must be granted to audit system privileges.

Below is an example of setting auditing for a system privilege.

[Example] Set auditing for system privilege

SQL> AUDIT CREATE TABLE BY TIBERO;

Audited.

If the above SQL succeeds, an audit record will be created whenever the user TIBERO attempts to create a table, regardless of success.

3. Disabling auditing

To disable auditing of system privileges, the AUDIT SYSTEM system privilege must be granted. To disable auditing on objects or directories owned by other users, the AUDIT ANY system privilege must be granted.

Below is an example of disabling an existing audit.

[Example] Disable auditing

SQL> NOAUDIT CREATE TABLE BY TIBERO;

Noaudited.

If the above SQL succeeds, the user TIBERO will no longer have audit records created when creating tables.

Audit Records

Audit records (Audit Trail) consist of basic information such as the user who executed the command, the schema object on which the command was executed, execution time, session ID, and the executed SQL statement.

1. Storing audit records

Audit records can be stored inside the database or in OS files according to the AUDIT_TRAIL parameter set in the $TB_SID.tip file. When stored in OS files, the file location and maximum size can be set by the AUDIT_FILE_DEST and AUDIT_FILE_SIZE parameters in the $TB_SID.tip file, respectively.

Below is an example specifying the storage location for audit records.

[Example] TIP settings for storage device

AUDIT_TRAIL=OS 
AUDIT_FILE_DEST=/home/tibero/tibero7/instance/t7/audit 
AUDIT_FILE_SIZE=10M

With this setting, audit records are stored in "/home/tibero/tibero7/instance/t7/audit" with a maximum size of 10MB.

Below is an example setting where SQL statements are also saved.

[Example] TIP file setting (store executed SQL statements)

AUDIT_TRAIL=DB_EXTENDED

This setting stores not only the basic information included in audit records but also the SQL statements executed by users in the database.

2. Viewing audit records

Audit records are stored either in OS files or in the database. If stored in OS files, they are saved as plain text files and can be easily viewed. If stored in the database, audit records can be queried through the following static Views.

[Table] Description of audit-related static views

3. Auditing the SYS user

Commands by the SYS user are audited differently from other users for security reasons. The SYS user is excluded from auditing by default, so the AUDIT or NOAUDIT commands cannot be used to enable or disable auditing for SYS.

To audit SYS user commands, the AUDIT_SYS_OPERATION parameter in the $TB_SID.tip file must be set to Y. This enables auditing of SYS user commands, and all actions performed are recorded in OS files only, not in the database, for security reasons.

Below is an example of enabling auditing for SYS user actions.

[Example] Audit settings for SYS user

AUDIT_SYS_OPERATION=Y 
AUDIT_FILE_DEST=/home/tibero/tibero7/instance/t7/audit 
AUDIT_FILE_SIZE=10M