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，则只能在用户没有任何模式对象时删除该用户。 引用被删除用户的架构对象的所有视图、同义词、过程和函数都将无效。如果稍后创建了另一个具有相同名称的用户，则新用户不会继承具有相同名称的前一个用户的任何内容。 |
Removing a user named John is shown below:
DROP USER John CASCADE;
查看用户数据
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.
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.
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)
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）
系统权限列表：
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)
额外权限 （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. |
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）
默认角色 （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;
查看角色数据
网络访问控制（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'。已经连接的客户机的访问不受影响。
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_IPAllows 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_FILESets 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_IPBlocks 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_FILESets 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_IPandLSNR_DENIED_IPare specified in the $TB_SID.tip file,LSNR_DENIED_IPis ignored. That is, only access by clients that have IP addresses set inLSNR_INVITED_IPis allowed. -
If neither
LSNR_INVITED_IPnorLSNR_DENIED_IPis 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_IPandLSNR_DENIED_IP. -
To modify the configuration of
LSNR_INVITED_IPorLSNR_DENIED_IPwhile Tibero server is operating, modify the configuration ofLSNR_INVITED_IPorLSNR_DENIED_IPin 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_IPorLSNR_DENIED_IPof $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.
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 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.
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.
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".