Table of Contents
The purpose of database security is to prevent unauthorized users from manipulating data in a database either intentionally or accidentally, which could result in damaging the consistency of or the corruption of the entire database.
This chapter describes methods to effectively manage user accounts, privileges, and roles, and methods to audit the contents of a database for database security in Tibero.
A user account is required to access data in Tibero, and each account is secured with a password. A password is set when a user account is created, and can be modified later. In Tibero, passwords are saved in a data dictionary in an encrypted form.
In Tibero, each user has one schema, and the schema's name is the same as that of the user.
In order to represent a schema object, specify it as shown below:
UserAccount.SchemaObject
Connecting to the database with the user account 'tibero' and accessing the schema object SYSTEM_PRIVILEGES which belongs to the SYS user is shown below:
$ tbsql tibero/tmax tbSQL 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Connected to Tibero. SQL> SELECT * FROM SYS.SYSTEM_PRIVILEGES; ...
Specifying a schema object without a user account is shown below:
SELECT * FROM V$DATABASE;
If a user account is omitted in an SQL statement, the following actions occur:
The user's schema objects are searched for in V$DATABASE.
If the current database user is tibero, tibero.V$DATABASE will be searched.
If a schema object named V$DATABASE (synonym) is not found, Tibero will search common synonyms, which are the synonyms owned by public users. Internally, PUBLIC.V$DATABASE is searched.
Public users can only own synonyms. When searching for synonyms owned by public users, the PUBLIC user account cannot be specified before a schema object. For example, if the view V$DATABASE which is owned by a PUBLIC user is searched, specify only V$DATABASE, not PUBLIC.V$DATABASE.
If a schema object named PUBLIC.V$DATABASE is also not found, or if the user does not have the privilege to access it, an error will occur.
To create, modify, or remove a user, a user who has the DBA privilege should connect to Tibero.
Tibero provides a user named SYS by default. The SYS user is created during the Tibero installation process and has the DBA privilege.
The following is an example of connecting to Tibero with the SYS user with the default password, 'tibero':
$ tbsql SYS/tibero tbSQL 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Connected to Tibero. SQL>
Because the SYS user is given the DBA role, If possible, using another account is recommended.
User accounts should be created with appropriate privileges according to the database security policy.
Creating a user account is shown below:
SQL> CREATE USER Steve ... (1)... IDENTIFIED BY dsjeoj134 ...(2) ... DEFAULT TABLESPACE USR; ... (3) ...
(1) Create a user named Steve using the CREATE USER statement.
(2) Specify the password as 'dsjeoj134'. The password should always be specified when using CREATE USER.
(3) Specify the default tablespace as USERS.
Creating multiple users according to the database security policy is shown below:
SQL> CREATE USER Peter IDENTIFIED BY abcd; User 'PETER' created. SQL> CREATE USER John IDENTIFIED BY asdf; User 'JOHN' created. SQL> CREATE USER Smith IDENTIFIED BY aaaa; User 'SMITH' created. SQL> CREATE USER Susan IDENTIFIED BY bbbb; User 'SUSAN' created.
If a tablespace is not specified as shown above, the system tablespace will be used by default. New users have no privileges and cannot connect to the database. To connect to the database, the users must be granted the system privilege to use the CREATE SESSION command or be granted a role that includes the privilege.
Granting privileges to users is shown below:
SQL> GRANT CONNECT TO Peter; Granted. SQL> GRANT RESOURCE TO John; Granted. SQL> GRANT CONNECT TO Smith; Granted. SQL> GRANT DBA TO Susan; Granted.
For more details, refer to “5.2. Privileges”.
Modifying a user's password or tablespace is shown below:
ALTER USER Peter ... (1) ... IDENTIFIED BY abcdef ... (2) ... DEFAULT TABLESPACE SYSTEM; ... (3) ...
(1) Modify the information of a user named Peter using the ALTER USER statement.
(2) Change the password to 'abcdef'.
(3) Change the tablespace to the SYSTEM tablespace.
Removing a user is shown below:
DROP USER user_name CASCADE;
| Item | Description |
|---|---|
| DROP USER user_name | Removes a user named user_name. |
| CASCADE | All schema objects of the user are removed before the user itself is removed. If CASCADE is not used, the user can only be removed if the user does not have any schema objects. All views, synonyms, procedures, and functions that refer to the schema objects of the removed user become INVALID. If another user with the same name is created later, the new user does not inherit anything from the previous user with the same name. |
Removing a user named John is shown below:
DROP USER John CASCADE;
Tibero provides the static views shown below to support viewing user data, which is used by both the DBA and general users.
| Static View | Description |
|---|---|
| ALL_USERS | Basic information about all users within the database. |
| DBA_USERS | Detailed information about all users within the database. |
| USER_USERS | Information about the current user. |
For more details on static views, refer to Tibero Reference Guide.
A user account can be locked to prevent them from connecting to the database. The user account can be unlocked.
SQL> ALTER USER Peter ACCOUNT LOCK; User 'PETER' altered.
If a locked account tries to connect, the following message will be displayed and the connection will fail:
SQL> conn peter/abcd; TBR-17006: Account is locked. No longer connected to server.
To unlock a user account, execute the following statement:
SQL> ALTER USER Peter ACCOUNT UNLOCK; User 'PETER' altered.
Even if a user account is locked due to an expired password or for exceeding the password error limit, the account can be unlocked with this method.
A user can be created according to database security policy or operating system authentication policy.
Creating a user account according to operating system authentication is shown below.
SQL> CREATE USER OSA$Steve ... (1) ... IDENTIFIED externally ... (2) ...
(1) Create 'Steve', an OS user, using the CREATE USER statement with a "OSA$" prefix. This prefix shows the user that the OS authentication policy was used. The value can be changed in OS_AUTH_PREFIX, and the default value is OSA$.
(2) The password of OSA$Steve is not separately managed within the database. If the OS user 'Steve' exists, the database does not separately authenticate it because the user was authenticated by the host. (This method is not recommended for an OS with security vulnerabilities.)
The OS-authenticated user connects to the server as shown below.
$ tbsql / tbSQL 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Connected to Tibero. SQL>
Remote users who are OS authenticated cannot connect to the server due to security reasons.
A privilege is required for a user to access a particular schema object of the database. If granted the privilege, users can execute SQL commands that affect the object.
Granting a privilege to a user is shown below:
SQL> conn Peter/abcdef ... (1) ... Connected. SQL> CREATE TABLE EMPLOYEE (ID NUMBER, EMPLOYEE_NAME VARCHAR(20), ADDRESS VARCHAR(50)); ... (2) ... Created. SQL> GRANT SELECT ON EMPLOYEE TO Smith; ... (3) ... Granted.
(1) Connect to the database with the user 'Peter'.
(2) Create a table named EMPLOYEE with a total of three columns (ID, EMPLOYEE_NAME, ADDRESS) using the CREATE TABLE statement.
(3) Grant 'Smith' the SELECT privilege for the EMPLOYEE table created by 'Peter'.
Now, Smith can execute SELECT queries for Peter's EMPLOYEE table.
The public user can also be granted privileges, and doing so has the same effect as granting the privilege to every user. Roles, which are groups of privileges, can be created to effectively manage privileges. If there are many users who should have the same privileges, use a role to reduce the amount of times GRANT must be used. Roles, like privileges, can be granted to all users by granting them to the public user.
There are two types of privileges provided by Tibero:
Privileges to query and update a particular object.
Privileges to execute particular tasks in the database.
A schema object privilege controls access to schema objects such as tables, views, sequences, and synonyms. The privilege is granted to other users with the GRANT statement, and the data is recorded in the data dictionary.
Granting a schema object privilege using the WITH GRANT OPTION clause is shown below:
SQL> GRANT SELECT, UPDATE (EMPLOYEE_NAME, ADDRESS) ON EMPLOYEE
TO Smith WITH GRANT OPTION;
If a privilege is granted to a user using WITH GRANT OPTION, that user can grant the given privilege to other users.
To successfully execute the command above, the current user, Peter, should have appropriate privileges, such as the SELECT and UPDATE privileges, on the EMPLOYEE schema object as well as the privilege to use the WITH GRANT OPTION clause.
After successfully executing the above command, the user Smith will have the SELECT and UPDATE privileges on EMPLOYEE. However, the privilege to update EMPLOYEE is limited to only two columns (EMPLOYEE_NAME, ADDRESS). Smith can grant the same privileges to other users.
Granting the same privileges to the users Susan and John is shown below:
SQL> GRANT ALL ON EMPLOYEE TO Susan WITH GRANT OPTION; Granted. SQL> GRANT SELECT, DELETE ON EMPLOYEE TO John WITH GRANT OPTION; Granted.
Use the REVOKE command to revoke the schema object privileges of another user. This command can revoke a part of or all schema object privileges of a user. A DBA can revoke any user's privilege, even if the privilege was not granted by the DBA.
Revoking Peter's DELETE privilege on the EMPLOYEE table and revoking all of John' privileges are shown below:
REVOKE DELETE ON EMPLOYEE FROM Peter; REVOKE ALL ON EMPLOYEE FROM John;
If a privilege granted with the WITH GRANT OPTION clause is revoked for a user, privileges that user granted to others will also be revoked.
An example of Smith granting all privileges on Peter.EMPLOYEE that were given by Peter to Susan is shown below:
SQL> conn Smith/abcd Connected. SQL> GRANT ALL ON Peter.EMPLOYEE TO Susan; Granted.
If Peter revokes the privileges on EMPLOYEE which he granted to Smith, the privileges which Smith granted to Susan will also be revoked:
SQL> conn Peter/abcdef Connected SQL> REVOKE ALL ON EMPLOYEE FROM Smith;
A system privilege is required to use the system commands needed to manage a database. System privileges are owned by the SYS user, and they can be granted to other users.
The following is a list of system privileges:
System privileges can also be granted to other users using the WITH ADMIN OPTION clause. Before granting a privilege to other users, the user must first own the privilege.
The SYS user, who has all system privileges, granting the GRANT SELECT ANY TABLE privilege to Susan with the WITH ADMIN OPTION clause is shown below:
SQL> conn SYS/tibero Connected to Tibero. SQL> GRANT SELECT ANY TABLE TO Susan WITH ADMIN OPTION; Granted.
Susan now has the privilege to grant the system privilege that the SYS user gave her to other users. In other words, the privilege to query all tables within a database can be granted to other users by Susan.
Unlike the WITH GRANT OPTION clause, any privileges granted with WITH ADMIN OPTION are not started in series.
Susan granting Peter a privilege that was given by the SYS user is shown below:
SQL> conn Susan/abcd Connected to Tibero. SQL> GRANT SELECT ANY TABLE TO Peter; Granted.
If the system privilege granted to Susan is revoked as in the example below, the system privilege that Susan gave to Peter remains unchanged.
SQL> conn SYS/tibero Connected to Tibero. SQL> REVOKE SELECT ANY TABLE FROM Susan;
Tibero provides the static views shown below to support viewing privilege data, which can be used by both the DBA and general users.
For more details on static views, refer to Tibero Reference Guide.
The following describes the parameters for setting additional privileges.
| Parameter | Description |
|---|---|
| USE_TRUNCATE_PRIVILEGE | To execute TRUNCATE, the TRUNCATE ANY TABLE system privilege or the TRUNCATE schema object privilege can be used. To use these privileges, the USE_TRUNCATE_PRIVILEGE parameter must be set to 'Y'.
|
| GRANT ALL | When executing GRANT ALL, the ALL privilege scope differs depending on the USE_TRUNCATE _PRIVILEGE parameter.
|
| REVOKE ALL | For REVOKE ALL, system privileges and schema object privileges work differently. For a system privilege, the revoking scope differs depending on the USE_TRUNCATE_PRIVILEGE parameter, like GRANT ALL.
For a schema object privilege, the TRUNCATE schema object privilege is revoked regardless of the USE_TRUNCATE_PRIVILEGE parameter. |
A database user password management policy can be specified.
For example, the policy can require that the passwords for user 1, user 2, and user 3 will expire after 90 days, passwords for user 4 and user 5 will expire after 30 days, and previous passwords cannot be reused. To accomplish this, a profile for which a password expires after 90 days as well as a profile for which a password expires after 30 days and cannot be reused should be created. The first profile is for user 1, user 2, and user 3, and the second profile is for user 4 and user 5.
This kind of password policy can be used by creating various policies and assigning a specific profile to each user.
Creating a profile is shown below:
SQL> CREATE PROFILE prof LIMIT
failed_login_attempts 3
password_lock_time 1/1440
password_life_time 90
password_reuse_time unlimited
password_reuse_max 10
password_grace_time 10
password_verify_function verify_function;
Profile 'PROF' created.
When creating or changing a profile, detailed parameters can be specified.
For a detailed description of each parameter, refer to Tibero SQL Reference Guide.
The following table describes each parameter. The default value of each parameter is determined by the 'DEFAULT' profile that is created with the database, and it can be viewed using the DBA_PROFILES view.
Changing a profile is shown below:
SQL> ALTER PROFILE prof LIMIT
password_lock_time 1
password_reuse_time 30;
Profile 'PROF' altered.
In the above example, an account is locked for one day if the number of password errors for a profile named PROF exceeds a limit. Once a password has been used, it cannot be used again for 30 days.
Deleting a profile is shown below:
SQL> DROP PROFILE prof CASCADE; Profile 'PROF' dropped.
To delete a profile that is already assigned to a user, the cascade option must be used. When the cascade option is used, the profiles of all users who are assigned the profile are deleted. User information is not deleted.
Specifying a profile when a user account is created is shown below:
SQL> CREATE USER Peter IDENTIFIED BY abcd PROFILE prof; User 'PETER' created.
Changing from the profile prof1 to the default profile, which is created with the database, is shown below:
SQL> ALTER USER Peter PROFILE default; User 'PETER' altered.
The following shows how profile related information is displayed.
SQL> select * from dba_profiles; PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT --------- ------------------------- ------------- --------------- DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL_VERIFY_FUNCTION DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED DEFAULT LOGIN_PERIOD PASSWORD UNLIMITED 16 rows selected.
The following shows the profile information applied to each user. The user can observe that the T_PROF profile is assigned to the user PETER.
SQL> select username, profile from dba_users; USERNAME PROFILE ---------- ---------- USER1 PETER T_PROF OUTLN SYSGIS SYSCAT SYS 6 rows selected.
VERIFY_FUNCTION can be used for password security.
The following describes errors that can occur when using the default VERIFY_FUNCTION.
-20001: Password same as user.
A password must be different from the user name.
-20002: Password length less than 4.
A password length must be greater than or equal to 4.
-20003: Password too simple.
A password must not be expectable. The following words cannot be used: 'welcome', 'database', 'account', 'user', 'password', 'tibero', 'computer', and 'abcd'.
-20004: Password should contain at least one digit, one character and one punctuation.
A password must contain at least one digit, one character, and one special character.
-20005: Password should differ by at least 3characters.
A new password must have at least three different characters from the previous password.
Appropriate privileges to access schema objects are required for a user to execute a database application.
For instance, in an application in which 30 users access 20 tables, the DBA has to grant privileges a total of 600 times. This task requires a lot of time.
Therefore, if the DBA defines a group of privileges in advance to effectively manage them, the DBA does not need to perform the time-consuming task, and the management of privileges is simplified.
A role is a group of privileges that can be granted to a user as one unit. In order to create, modify or grant a role, the appropriate privilege is required.
The following connects to the database with the SYS user and grants Peter the CREATE ROLE, ALTER ANY ROLE, and GRANT ANY ROLE privileges.
SQL> conn SYS/tibero Connected to Tibero. SQL> GRANT CREATE ROLE, ALTER ANY ROLE, GRANT ANY ROLE TO Peter; Granted.
Creating roles is shown below:
SQL> CREATE ROLE APP_USER; Role 'APP_USER' created. SQL> GRANT CREATE SESSION TO APP_USER; Granted. SQL> CREATE ROLE CLERK; Role 'CLERK' created. SQL> GRANT SELECT, INSERT ON Peter.EMPLOYEE TO CLERK; Granted. SQL> GRANT SELECT, INSERT ON Peter.TIME_CARDS TO CLERK; Granted. SQL> GRANT SELECT, INSERT ON Peter.DEPARTMENT TO CLERK; Granted.
Descriptions of the roles created above are shown below:
| Role | Description |
|---|---|
| APP_USER | A role is created with the name APP_USER using the CREATE ROLE command. The CREATE SESSION system privilege is granted to the APP_USER role. Users who are granted the APP_USER role can connect to the database. |
| CLERK | A role is created with the name CLERK using the CREATE ROLE command. Several privileges on different tables are granted to the CLERK role. Users who are granted the CLERK role can:
|
A role can be granted to another role.
Granting the role APP_USER to the role CLERK is shown below:
SQL> GRANT APP_USER TO CLERK; Granted.
After successfully executing the command above, users who were granted the role CLERK will have the role APP_USER as well, which means they have all the privileges in APP_USER.
The role grantees can grant the role to other users again if the role is granted with the WITH ADMIN OPTION clause.
SQL> GRANT CLERK TO Susan WITH ADMIN OPTION; Granted. SQL> GRANT CLERK TO Peter; Granted.
After successfully executing the command above, Susan can grant the role to other users, and also revoke it. However, Peter can only use the role CLERK, and is unable to grant or revoke the role.
The REVOKE command is used to revoke a role from another user. The DBA can revoke any user's roles, even if the DBA did not grant them.
Revoking the role APP_USER from the user Peter and from the role CLERK is shown below:
REVOKE APP_USER FROM Peter; REVOKE APP_USER FROM CLERK;
The usage of REVOKE is the same for both users and roles.
Tibero provides some predefined roles by combining frequently used system privileges:
A role granted to a user can be dynamically turned on or off within a session using the SET ROLE command.
For example, if a user has the CLERK, RESOURCE, and APP_USER roles, then the user can use one of the commands below to turn necessary roles on or off:
SET ROLE CLERK, RESOURCE; /* Turns on the CLERK and RESOURCE roles */ SET ROLE ALL EXCEPT CLERK; /* Turns on all roles except CLERK */ SET ROLE ALL; /* Turns on all roles */ SET ROLE NONE; /* Turns off all roles */
Activating and inactivating roles is very useful for effectively controlling user privileges. To grant particular privileges to a user when executing an application, use the SET ROLE command to activate the role of the privileges at the start of the program and to inactivate the role before exiting the program.
The role that is turned on when a user first connects to a session is called the user's default role. The default role of a new user is set to ALL. In other words, all roles given to the user will be activated by default.
A user's default roles can be changed using the ALTER USER command, and the usage is similar to the SET ROLE command.
Altering the default role is shown below:
ALTER USER Park DEFAULT ROLE CLERK, RESOURCE; ALTER USER Park DEFAULT ROLE ALL EXCEPT CLERK; ALTER USER Park DEFAULT ROLE ALL; ALTER USER Park DEFAULT ROLE NONE;
Tibero provides the static views shown below to support viewing role data, which can be used by both the DBA and general users.
For more information about static views, refer to Tibero Reference Guide.
Network Access Control (NAC) is a network security technology that blocks and controls network access by unauthorized users. With NAC, Tibero efficiently protects enterprise IT resources.
Tibero provides the following two NAC policies, depending on the range of network security:
Blocks or allows network access by all clients.
IP-Based Network Access Control
Blocks or allows network access by clients who have certain IP addresses.
Full network access control blocks or allows TCP/IP network access by all clients.
The following statement allows network access by all clients. Use the following command when Tibero server first starts:
ALTER SYSTEM LISTENER REMOTE ON;
The following statement blocks network access by all clients.
ALTER SYSTEM LISTENER REMOTE OFF;
The above command blocks network access by external clients, but still allows network access by clients on the local host if the IP in the tbdsn.tbr file of the local host is set to 'localhost'. Access by clients that are already connected is unaffected.
IP Address Based Network Access Control blocks and allows network access by clients based on IP addresses that are specified in an initialization parameter.
LSNR_INVITED_IP
Allows network access by clients that have certain IP addresses, but blocks network access by other clients.
An IP address is in the format of 'IP address/the number of bits of a subnet mask'.
To specify multiple IP addresses, use a semicolon (;) as a delimiter.
If the number of bits of a subnet mask is 32, it can be omitted.
Example: 192.168.2.0/24
In the above example, 192.168.1.1 has the same meaning as 192.168.1.1/32. The number of bits of the subnet mask is 24, so the subnet mask is 255.255.255.0 and all clients that have IP addresses of 192.168.2.xxx are allowed access.
The following specification allows network access by
clients using the initialization parameter
LSNR_INVITED_IP.
<$TB_SID.tip>
LSNR_INVITED_IP=192.168.1.1;192.168.2.0/24;192.1.0.0/16
Can be up to 255 characters. If you want to set an IP
address that exceeds 255 characters, use
LSNR_INVITED_IP_FILE.
LSNR_INVITED_IP_FILE
Sets IP addresses that are allowed to access a network in a specific file and sets the file's absolute path in the tip file.
An IP address is in the format of 'IP address/the number of bits of a subnet mask'.
To specify multiple IP addresses, set one IP address in a single line.
The maximum file size is 8 MB.
The following specification allows network access by
clients using the initialization parameter
LSNR_INVITED_IP_FILE.
</home/tibero/invited_ip.txt>
192.168.1.1 192.168.2.0/24 192.1.0.0/16
<$TB_SID.tip>
LSNR_INVITED_IP_FILE=/home/tibero/invited_ip.txt
LSNR_DENIED_IP
Blocks network access by clients that have certain IP addresses, but allows network access by other clients.
Used in the same way as in the initialization parameter
LSNR_INVITED_IP.
The following specification blocks network access by
clients using the initialization parameter
LSNR_DENIED_IP:
<$TB_SID.tip>
LSNR_DENIED_IP=192.168.1.1;192.168.2.0/24;192.1.0.0/16
LSNR_DENIED_IP_FILE
Sets IP addresses that are not allowed to access a network in a specific file and sets the file's absolute path in the tip file.
Used in the same way as in the initialization parameter
LSNR_INVITED_IP_FILE.
LSNR_INVITED_IP and
LSND_DENIED_IP parameters above have the following
characteristics.
If both LSNR_INVITED_IP and
LSNR_DENIED_IP are specified in the $TB_SID.tip file,
LSNR_DENIED_IP is ignored. That is, only access
by clients that have IP addresses set in
LSNR_INVITED_IP is allowed.
If neither LSNR_INVITED_IP nor
LSNR_DENIED_IP is specified in the $TB_SID.tip file, network access by all
clients is allowed.
Access from the loopback address (127.0.0.1) is always allowed
regardless of LSNR_INVITED_IP and
LSNR_DENIED_IP.
To modify the configuration of
LSNR_INVITED_IP or
LSNR_DENIED_IP while Tibero server is operating, modify the
configuration of LSNR_INVITED_IP or
LSNR_DENIED_IP in the $TB_SID.tip file. Save the file and run
the following command.
alter system listener parameter reload;
The command above reads the specification in
LSNR_INVITED_IP or
LSNR_DENIED_IP of $TB_SID.tip and applies any
changes.
Checking the contents of a listener's trace log file is recommended to verify that the corresponding initialization parameter has been applied properly.
To add a database connection port other than LISTENER_PORT, use the following command.
alter system listener add port 8799;
To delete an added listener port, use the following command.
alter system listener delete port 8799;
A semicolon (;) must be placed at the end of the EXTRA_LISTENER_PORTS parameter.
<$TB_SID.tip>
EXTRA_LISTENER_PORTS=8799;8800;
Dynamic addition and deletion of listener ports on Windows OS is planned to be supported in the future.
Auditing is a security technology that records the actions of users in a database. An administrator can write logs for a specific action or user with the auditing function to efficiently protect a database.
The two types of auditing depend on the target:
Auditing Schema Objects
All actions for specified schema objects can be recorded.
Auditing System Privilege
All actions that use specified system privileges can be recorded.
When leaving an audit trail, specific users or roles can be specified. An audit trail can also be left for successful and failed actions, only once for each session, or whenever an action is executed.
To enable auditing, use the AUDIT command. To disable auditing, use the NOAUDIT command.
To audit schema objects or directory objects that belong to another user, the system privilege AUDIT ANY is required.
Enabling auditing for schema objects is shown below:
SQL> AUDIT delete ON t BY SESSION WHENEVER SUCCESSFUL;
Audited.
After executing the above SQL statement, an audit trail is recorded when a delete statement for table 't' is successfully executed.
To audit system privileges, the system privilege AUDIT SYSTEM is required.
Enabling auditing of system privileges is shown below:
SQL> AUDIT create table BY tibero;
Audited.
After executing the SQL statement above, an audit trail is recorded any time the user tibero attempts to create a table.
For more information about system privileges, refer to “5.2. Privileges”.
To disable auditing for system privileges, the system privilege AUDIT SYSTEM is required.
To audit schema objects or directory objects that belong to another user, the system privilege AUDIT ANY is required.
Disabling auditing that was previously enabled is shown below:
SQL> NOAUDIT create table BY tibero; Noaudited.
After executing the above SQL statement, an audit trail is no longer recorded when the user tibero creates a table.
The SYS user cannot be a target of auditing. For more information about auditing the SYS user, refer to “5.6.3. Auditing SYS User”.
Audit trails consist of basic information such as the user who executed a command, the schema object for which the command is executed, the execution time, the session ID, and the executed SQL statement.
Audit trails can be saved in a database or in an OS file according to the parameter AUDIT_TRAIL specified in TB_UP_SID.tip. When saving audit trails in an OS file, the location and the maximum size can be specified with the parameters AUDIT_FILE_DEST and AUDIT_FILE_SIZE in $TB_UP_SID.tip, respectively.
Specifying the location for audit trails is shown below:
<$TB_SID.tip>
AUDIT_TRAIL=DB_EXTENDED
The above statement saves not only basic information but also the SQL statement that the user executed to audit trails in the database.
<$TB_SID.tip>
AUDIT_TRAIL=OS
AUDIT_FILE_DEST=/home/tibero/audit/audit_trail.log
AUDIT_FILE_SIZE=10M
In the above example, audit records are stored in "/home/tibero/audit/audit_trail.log" up to 10 MB.
The following is a list of stored audit record items.
| Item | Description |
|---|---|
| SERIAL_NO | The concept of a secondary key to identify a specific session. The number of sessions that can be active at the same time is limited, and the session ID identifies these active sessions. However, session IDs have a limit in identifying sessions. For example, if a session performs a task, closes, and then reopens to perform another task, the session ID simply doesn't identify the two tasks. So this requires additional information called serial number (SERIAL_NO). This number is incremented when reopening or reusing a session and appended to the session ID to identify the specific session. |
| AUD_NO | Sequential number of audit entries logged since the session was opened. |
| STMT_ID | Internal PPID of the physical plan generated by parsing the statement that recorded the audit. |
| CLIENT_ID | Client name connected to Tibero |
| PRIV_NO | Privilege number required to execute the statement. Internally, negative numbers indicate system privileges (ANY series such as SELECT_ANY_TABLE), and positive numbers indicate object privileges (select on t1). |
| ACTION | Column to indicate whether the statement succeeded.
|
| USGMT_ID | Undo segment ID to record undo of the current transaction. |
| SLOTNO | Value to identify the slots in undo segments. When a transaction starts, one slot must be allocated from the undo segments. Therefore, undo segments and slot numbers are used to identify active transactions. |
| WRAPNO | SERIAL_NO is additional information for sessions, and WRAPNO is additional information for transactions. This value is increased when the transaction is committed and another transaction reuses the slot. Therefore, the information, USGMT_ID, SLOTNO, and WRAPNO, are used to identify a single transaction including transactions performed in the past. |
If AUDIT_TRAIL is OS, the audit is recorded in log format. If it is DB or DB_EXTENDED, it is inserted into SYS._DD_AUD table and shown as a view.
1. For more information about specifying $TB_SID.tip, refer to Tibero Reference Guide.
2. An audit trail for the SYS user cannot be saved in the database. For more information about auditing the SYS user, refer to “5.6.3. Auditing SYS User”.
Audit trails are saved in an OS file or in a database. When audit trails are saved in an OS file, they can be viewed easily because the OS file is a basic text file. When audit trails are saved in a database, they can be viewed with the following static views:
| Static View | Description |
|---|---|
| DBA_AUDIT_TRAIL | All audit trails that are saved in a database. |
| USER_AUDIT_TRAIL | Audit trails of the current user that are saved in a database. |
1. For more information about static views, refer to Tibero Reference Guide.
For security reasons, auditing the SYS user's commands differs from auditing general user commands. Because the SYS user is excluded from targets of auditing, auditing the SYS user cannot be enabled or disabled with the AUDIT or NOAUDIT command.
To audit the commands of the SYS user, the parameter AUDIT_SYS_OPERATION in $TB_SID.tip must be set to Y. For security reasons, when auditing commands of the SYS user is enabled, all actions are recorded in an OS file instead of the database.
The following parameters configure the audit actions of the SYS user:
<$TB_SID.tip>
AUDIT_SYS_OPERATIONS=Y
AUDIT_FILE_DEST=/home/tibero/audit/audit_trail.log
AUDIT_FILE_SIZE=10M
The above configuration saves up to 10 megabytes of actions of the
SYS user in "/home/tibero/audit/audit_trail.log".