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.
原文：https://www.cnblogs.com/zykLove/p/12133729.html