用户管理和数据库安全

A schema is a group of schema objects that includes tables, views and indexes.

表示模式对象

　　UserAccount.SchemaObject

1 创建/修改/移除用户（Creating,Modifying,and Removing Users）

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>

创建用户（create users）
　　CREATE USER steve IDENTIFIED BY dsjeoj123 DEFAULT TABLESPACE usr;

修改用户
　　ALTER USER steve IDENTIFIED BY abcdef DEFAULT TABLESPACE system;
移除用户
　　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. 在删除用户本身之前，删除用户的所有模式对象。如果不使用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.

在删除用户本身之前，删除用户的所有模式对象。如果不使用CASCADE，则只能在用户没有任何模式对象时删除该用户。

引用被删除用户的架构对象的所有视图、同义词、过程和函数都将无效。如果稍后创建了另一个具有相同名称的用户，则新用户不会继承具有相同名称的前一个用户的任何内容。

Removing a user named John is shown below:

DROP USER John CASCADE;

查看用户数据

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.

Locking and Unlocking User Accounts

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.

Creating an OS Authenticated User

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>

2 Privileges（权限）

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.

Schema Object Privileges

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.

Schema Object Privilege	Description
SELECT	Privilege to query rows in a table.
INSERT	Privilege to insert a row in a table.
UPDATE	Privilege to update rows in a table.
DELETE	Privilege to delete a row from a table.
ALTER	Privilege to modify a schema object.
INDEX	Privilege to create an index for a table.
REFERENCES	Privilege to create a reference constraint for a table.
TRUNCATE	Privilege to perform TRUNCATE on a table. To use this privilege, the USE_TRUNCATE_PRIVILEGE parameter must be set to 'Y'.

授予模式对象权限 (Granting a Schema Object Privilege)
GRANT SELECT, UPDATE(EMPLOYEE_NAME, ADDRESS) ON EMPLOYEE
　　　　　　TO smith WITH GRANT OPTION;

SQL> GRANT ALL ON EMPLOYEE TO Susan WITH GRANT OPTION;

Granted.

SQL> GRANT SELECT, DELETE ON EMPLOYEE TO John WITH GRANT OPTION;
Granted.

回收模式对象权限 （Revoking a Schema Object Privilege）
　　REVOKE DELETE ON EMPLOYEE FROM Peter;
　　REVOKE ALL ON EMPLOYEE FROM John;

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;

系统权限 （System Privileges）
系统权限列表：

System Privilege	Description
ALTER SYSTEM	Privilege to execute the ALTER SYSTEM command.
CREATE SESSION	Privilege to create a session in a database for logging in.
CREATE USER	Privilege to create a user.
ALTER USER	Privilege to modify user information.
DROP USER	Privilege to delete a user.
CREATE TABLESPACE	Privilege to create a tablespace.
ALTER TABLESPACE	Privilege to modify a tablespace.
DROP TABLESPACE	Privilege to delete a tablespace.
SELECT ANY DICTIONARY	Privilege to view DICTIONARY. If this privilege is granted, SYS, SYSCAT, and SYSGIS objects can be viewed.
CREATE TABLE	Privilege to create a table in the user's own schema.
CREATE ANY TABLE	Privilege to create a table in any schema.
ALTER ANY TABLE	Privilege to modify a table in any schema.
DROP ANY TABLE	Privilege to delete a table in any schema.
COMMENT ANY TABLE	Privilege to add a comment on a table in any schema.
SELECT ANY TABLE	Privilege to query a table in any schema.
INSERT ANY TABLE	Privilege to insert a row to a table in any schema.
UPDATE ANY TABLE	Privilege to update a row in a table in any schema.
DELETE ANY TABLE	Privilege to delete a row in a table in any schema.
TRUNCATE ANY TABLE	Privilege to perform TRUNCATE on a table in a schema. To use this privilege, the USE_TRUNCATE_PRIVILEGE parameter must be set to 'Y'.
CREATE ANY INDEX	Privilege to create an index in a table in any schema.
ALTER ANY INDEX	Privilege to modify an index in a table in any schema.
DROP ANY INDEX	Privilege to delete an index in a table in any schema.
CREATE SYNONYM	Privilege to create a synonym in the user's own schema.
CREATE ANY SYNONYM	Privilege to create a synonym in any schema.
DROP ANY SYNONYM	Privilege to delete a synonym in any schema.
SYSDBA	Privilege to execute the SHUTDOWN, ALTER DATABASE, CREATE DATABASE, ARCHIVELOG, and RECOVERY commands.
CREATE PUBLIC SYNONYM	Privilege to create a synonym in the public schema.
DROP PUBLIC SYNONYM	Privilege to delete a synonym in the public schema.
CREATE VIEW	Privilege to create a view in the user's own schema.
CREATE ANY VIEW	Privilege to create a view in any schema.
DROP ANY VIEW	Privilege to delete a view in any schema.
CREATE SEQUENCE	Privilege to create a sequence in the user's own schema.
CREATE ANY SEQUENCE	Privilege to create a sequence in any schema.
ALTER ANY SEQUENCE	Privilege to modify a sequence in any schema.
DROP ANY SEQUENCE	Privilege to delete a sequence in any schema.
SELECT ANY SEQUENCE	Privilege to query a sequence in any schema.
CREATE ROLE	Privilege to create a role.
DROP ANY ROLE	Privilege to delete a role.
GRANT ANY ROLE	Privilege to grant any role.
ALTER ANY ROLE	Privilege to modify a role.
ALTER DATABASE	Privilege to modify a database.
CREATE PROCEDURE	Privilege to create a procedure in the user's own schema.
CREATE ANY PROCEDURE	Privilege to create a procedure in any schema.
ALTER ANY PROCEDURE	Privilege to modify a procedure in any schema.
DROP ANY PROCEDURE	Privilege to delete a procedure in any schema.
EXECUTE ANY PROCEDURE	Privilege to execute a procedure in any schema.
CREATE TRIGGER	Privilege to create a trigger in the user's own schema.
CREATE ANY TRIGGER	Privilege to create a trigger in any schema.
ALTER ANY TRIGGER	Privilege to modify a trigger in any schema.
DROP ANY TRIGGER	Privilege to delete a trigger in any schema.
GRANT ANY OBJECT PRIVILEGE	Privilege to have all schema object privileges.
GRANT ANY PRIVILEGE	Privilege to grant all privileges.

a 授予系统权限(Revoking System Privileges)

SQL> conn SYS/tibero
Connected to Tibero.

SQL> GRANT SELECT ANY TABLE TO Susan WITH ADMIN OPTION;
Granted.
b 回收系统权限 (Revoking System Privileges)
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;

查看系统权限(Viewing Privilege Data)

Static View	Description
DBA_SYS_PRIVS	Information about the system privileges granted to all users.
USER_SYS_PRIVS	Information about the system privileges granted to the current user.
DBA_TBL_PRIVS	Information about all schema object privileges in the database.
USER_TBL_PRIVS	Information about all schema object privileges that the current user owns.
ALL_TBL_PRIVS	Information about all schema object privileges owned by the current user and of all schema object privileges that are owned by the public user.
DBA_COL_PRIVS	Information about object privileges of all columns in the database.
USER_COL_PRIVS	Information about object privileges of columns for which the current user is the object owner, granter, or grantee.
ALL_COL_PRIVS	Information about object privileges of columns for which the current user or public user is the object owner or granter.
USER_COL_PRIVS_MADE	Information about object privileges of columns for which the current user is the granter.
ALL_COL_PRIVS_MADE	Information about object privileges of columns for which the current user is the object owner or granter.
USER_COL_PRIVS_RECD	Information about object privileges of columns for which the current user is the grantee.
ALL_COL_PRIVS_RECD	Information about object privileges of columns for which the current user or public user is the grantee.

额外权限 （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'. Y: To execute TRUNCATE, the TRUNCATE privilege must be granted for tables in other schema. N: To execute TRUNCATE, the DROP ANY TABLE system privilege must be granted.
GRANT ALL	When executing GRANT ALL, the ALL privilege scope differs depending on the USE_TRUNCATE _PRIVILEGE parameter. Y: GRANT ALL includes the TRUNCATE privilege. N: GRANT ALL does not include the TRUNCATE privilege.
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. Y: When executing REVOKE ALL, the TRUNCATE ANY TABLE privilege is also revoked. N: The TRUNCATE ANY TABLE privilege is not revoked. For a schema object privilege, the TRUNCATE schema object privilege is revoked regardless of the USE_TRUNCATE_PRIVILEGE parameter.

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'.

Y: To execute TRUNCATE, the TRUNCATE privilege must be granted for tables in other schema.
N: To execute TRUNCATE, the DROP ANY TABLE system privilege must be granted.

GRANT ALL

When executing GRANT ALL, the ALL privilege scope differs depending on the USE_TRUNCATE _PRIVILEGE parameter.

Y: GRANT ALL includes the TRUNCATE privilege.
N: GRANT ALL does not include the TRUNCATE privilege.

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.

Y: When executing REVOKE ALL, the TRUNCATE ANY TABLE privilege is also revoked.
N: The TRUNCATE ANY TABLE privilege is not revoked.

For a schema object privilege, the TRUNCATE schema object privilege is revoked regardless of the USE_TRUNCATE_PRIVILEGE parameter.


3 配置文件 （Profiles）

创建/改变/删除配置文件 (Creating, Changing, and Deleting Profiles)
 创建一个配置文件：
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.

配置文件参数类型 (Profile Parameter Types)

SQL> ALTER PROFILE pro LIMIT password_lock_time 1
　　　　　　　　　　　　　　　　　password_reuse_time 30;

删除配置文件
SQL> DROP PROFILE prof CASCADE;

Profile 'PROF' dropped.

指定一个配置文件 （Specifying a Profile）
SQL> CREATE USER peter IDENTIFIED BY abcd PROFILE prof;
User 'PETER' created.
修改用户默认配置文件：
SQL> ALTER USER peter PROFILE default;
User 'PETER' altered.

查看配置文件数据 （Viewing Profile Data）
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.

查看用户对应配置文件

SQL> select username, profile from dba_users;
 
USERNAME   PROFILE
---------- ----------
USER1      
PETER      T_PROF
OUTLN      
SYSGIS     
SYSCAT     
SYS        
 
6 rows selected.

验证函数（VERIFY_FUNCTION）

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.

角色（Roles）

创建，授权和回收角色 (Creating/Granting /Revoking Roles)

SQL> conn SYS/tibero
Connected to Tibero.

SQL> GRANT CREATE ROLE, ALTER ANY ROLE, GRANT ANY ROLE TO Peter;
Granted.

创建角色
create role app_user;
create role clerk;

grant create session to app_user;
grant select, insert on Peter.EMPLOYEE to clerk;
grant select, insert on Peter.TIME_CARDS to clerk;
grant select, insert on Peter.DEPARTMENT to clerk;

授予角色给另一个角色
GRANT APP_USER TO CLERK；

grant clerk to Susan with admin option;
grant clerk to Peter;

回收权限(Revoking Roles)


REVOKE APP_USER FROM Peter;

REVOKE APP_USER FROM CLERK;
预定义角色（Predefined Roles）

Role	Included Privileges	Description
CONNECT	CREATE SESSION	Simple database access role. This role is required for all users.
RESOURCE	CREATE PROCEDURE CREATE SEQUENCE CREATE TABLE CREATE TRIGGER	Role for creating basic schema objects within the user's own schema. This role is needed by application developers.
DBA	-	Role that includes the system privileges of the DBA. After granting this role to a user, the user can grant the system privileges to any other user. All system privileges are granted with the WITH ADMIN OPTION clause.
HS_ADMIN_ROLE	-	Role that has the system privileges required for a DBA who uses heterogeneous services.


默认角色 （Default Roles）

可以使用SET role命令在会话中动态地打开或关闭授予用户的角色。

例如，如果用户拥有职员、资源和APP_USER角色，那么用户可以使用下面的命令之一来打开或关闭必要的角色:

SET ROLE CLERK, RESOURCE;       /* Turns on the CLERK and RESOURCE roles *打开2角色/
SET ROLE ALL EXCEPT CLERK;      /* Turns on all roles except CLERK *打开所有角色除了CLERK/
SET ROLE ALL;                   /* Turns on all roles * 打开所有角色/
SET ROLE NONE;                  /* Turns off all roles *关闭所有角色/

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;

查看角色数据

Static View	Description
DBA_ROLES	Information about all roles within Tibero.
DBA_ROLE_PRIVS	Information about all roles granted to users or other roles.
USER_ROLE_PRIVS	Information about the roles granted to the current user or the public user.
ROLE_SYS_PRIVS	Information about the system privileges granted to the roles that can be accessed by the current user.
ROLE_TAB_PRIVS	Information about the system object privileges granted to the roles that can be accessed by the current user.
ROLE_ROLE_PRIVS	Information about the other roles granted to the roles that can be accessed by the current user.

网络访问控制（Network Access Control）

网络访问控制(NAC)是一种阻止和控制未经授权用户访问网络的网络安全技术。通过NAC, Tibero有效地保护了企业的IT资源。

根据网络安全范围的不同，Tibero提供了以下两种NAC策略:

全网络访问控制(Full Network Access Control)

完整的网络访问控制块或允许TCP/IP网络访问的所有客户端。

下面的语句允许所有客户机访问网络。当Tibero服务器第一次启动时使用以下命令:

ALTER SYSTEM LISTENER REMOTE ON;

打开系统监听器远程;

下面的语句阻塞所有客户机的网络访问。

ALTER SYSTEM LISTENER REMOTE OFF;

关闭系统监听器;

上面的命令阻止外部客户机对网络的访问，但是如果tbdsn中的IP仍然允许本地主机上的客户机对网络的访问。本地主机的tbr文件被设置为'localhost'。已经连接的客户机的访问不受影响。

5.5.2. IP-based Network Access Control

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 parameterLSNR_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 parameterLSNR_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 orLSNR_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 orLSNR_DENIED_IP of $TB_SID.tip and applies any changes.

Note

Checking the contents of a listener's trace log file is recommended to verify that the corresponding initialization parameter has been applied properly.

5.5.3. Dynamically Adding and Deleting a Listener Port

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;

Note

Dynamic addition and deletion of listener ports on Windows OS is planned to be supported in the future.

. Auditing 审计

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.

审计模式对象（Auditing Schema Objects）

SQL> AUDIT delete ON t BY SESSION WHENEVER SUCCESSFUL;

Audited.

审计系统权限

SQL> AUDIT create table BY tibero;

Audited.


禁用审计（Disabling Auditing）

SQL> NOAUDIT create table BY tibero;

Noaudited.

5.6.2. Audit Trails

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.

Recording Audit Trails

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

The above statement saves up to 10 megabytes of audit trails in"/home/tibero/audit/audit_trail.log".

Note

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”.

Viewing Audit Trails

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.

Note

1. For more information about static views, refer to Tibero Reference Guide.

5.6.3. Auditing SYS User

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".