MySQL 模式下用户管理

下述为用户管理语法树：

create_user_stmt:
    CREATE USER [IF NOT EXISTS] user_name [IDENTIFIED BY 'password'];
alter_user_stmt:
      ALTER USER user_name ACCOUNT {LOCK | UNLOCK};
    | ALTER USER user_name IDENTIFIED BY 'password';
    | SET PASSWORD [FOR user_name] = PASSWORD('password');
    | RENAME USER rename_user_action_list;
drop_user_stmt:
    DROP USER user_name_list;
rename_user_action_list:
    rename_user_action [, rename_user_action ...]
rename_user_action:
    user_name TO user_name
user_name_list:
    user_name [, user_name ...]
password:
    STR_VALUE

下述为用户权限管理语句的语法：

grant_stmt:
    GRANT privilege_type_list ON privilege_level TO user_option_list [WITH GRANT OPTION];
revoke_stmt:
    REVOKE privilege_type_list ON privilege_level FROM user_name_list;
show_grants_stmt:
    SHOW GRANTS [FOR user_name];
privilege_type_list:
    {ALL [PRIVILEGES] | privilege_type [, privilege_type ...]}
privilege_type:
      ALTER
    | CREATE
    | CREATE USER
    | CREATE VIEW
    | DELETE
    | DROP
    | GRANT OPTION
    | INDEX
    | INSERT
    | PROCESS
    | SELECT
    | SHOW DATABASES
    | SHOW VIEW
    | SUPER
    | UPDATE
    | USAGE
privilege_level:
      *
    | *.*
    | database_name.*
    | database_name.table_name
    | table_name
user_option_list:
    user_option [, user_option ...]
user_option:
    user_name [IDENTIFIED BY 'password']
password:
    STR_VALUE
user_name_list:
    user_name [, user_name ...]

Oracle 模式下用户管理

下述为用户管理语法树：

create_user_stmt:
    CREATE USER [IF NOT EXISTS] user_name [IDENTIFIED BY "password"];
alter_user_stmt:
      ALTER USER user_name ACCOUNT {LOCK | UNLOCK};
    | ALTER USER user_name IDENTIFIED BY "password";
    | SET PASSWORD [FOR user_name] = PASSWORD("password");
drop_user_stmt:
    DROP USER user_name_list [cascade];
user_name_list:
    user_name [, user_name ...]
password:
    STR_VALUE

下述为用户权限管理语句的语法：

grant_stmt:
    GRANT privilege_type_list ON privilege_level TO user_option_list [WITH GRANT OPTION];
revoke_stmt:
    REVOKE privilege_type_list ON privilege_level FROM user_name_list;
show_grants_stmt:
    SHOW GRANTS [FOR user_name];
privilege_type_list:
    {ALL [PRIVILEGES] | privilege_type [, privilege_type ...]}
privilege_type:
      ALTER
    | CREATE
    | CREATE USER
    | CREATE VIEW
    | DELETE
    | DROP
    | GRANT OPTION
    | INDEX
    | INSERT
    | PROCESS
    | SELECT
    | SHOW DATABASES
    | SHOW VIEW
    | SUPER
    | UPDATE
    | USAGE
privilege_level:
      *
    | *.*
    | database_name.*
    | database_name.table_name
    | table_name
user_option_list:
    user_option [, user_option ...]
user_option:
    user_name [IDENTIFIED BY "password"]
password:
    STR_VALUE
user_name_list:
    user_name [, user_name ...]

白名单管理

ob_tcp_invited_nodes 参数是租户全局的白名单限制参数。运行下述语句查看该参数以确认白名单：

obclient> show variables like 'ob_tcp_invited_nodes';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| ob_tcp_invited_nodes | %     |
+----------------------+-------+

权限限制

Lock 用户操作：

obclient>  ALTER USER demo ACCOUNT LOCK ;
Query OK, 0 rows affected (0.02 sec)
obclient -udemo@demo0_111 -P2881 -h11.166.87.1   -pttt
obclient: [Warning] Using a password on the command line interface can be insecure.
ERROR 5039 (01007): User locked

Unlock 用户操作：

obclient>  ALTER USER demo ACCOUNT unLOCK ;
Query OK, 0 rows affected (0.02 sec)
obclient -udemo@demo0_111 -P2881 -h11.166.87.1   -pttt
obclient: [Warning] Using a password on the command line interface can be insecure.
Welcome to the OceanBase monitor.  Commands end with ; or \g.
Your OceanBase connection id is 3222098552
Server version: 5.7.25 OceanBase 2.2.20 (r1856628-f787fd142eb53a646fb793a64fff55be0ba28250) (Built Aug 10 2019 15:27:33)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient>

权限授予操作：

obclient>  grant SELECT  on demo.*  TO demo;

权限摘除操作：

obclient> revoke  SELECT  on demo.*  from demo;
Query OK, 0 rows affected (0.03 sec)
obclient -udemo@demo0_111 -P2881 -h11.166.87.1   -pttt
obclient: [Warning] Using a password on the command line interface can be insecure.
ERROR 1044 (42000): Access denied for user 'DEMO'@'%' to database 'DEMO'