下述为 MySQL 用户管理语法树:

  1. create_user_stmt:
  2. CREATE USER [IF NOT EXISTS] user_name [IDENTIFIED BY 'password'];
  3. alter_user_stmt:
  4. ALTER USER user_name ACCOUNT {LOCK | UNLOCK};
  5. | ALTER USER user_name IDENTIFIED BY 'password';
  6. | SET PASSWORD [FOR user_name] = PASSWORD('password');
  7. | RENAME USER rename_user_action_list;
  8. drop_user_stmt:
  9. DROP USER user_name_list;
  10. rename_user_action_list:
  11. rename_user_action [, rename_user_action ...]
  12. rename_user_action:
  13. user_name TO user_name
  14. user_name_list:
  15. user_name [, user_name ...]
  16. password:
  17. STR_VALUE

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

  1. grant_stmt:
  2. GRANT privilege_type_list ON privilege_level TO user_option_list [WITH GRANT OPTION];
  3. revoke_stmt:
  4. REVOKE privilege_type_list ON privilege_level FROM user_name_list;
  5. show_grants_stmt:
  6. SHOW GRANTS [FOR user_name];
  7. privilege_type_list:
  8. {ALL [PRIVILEGES] | privilege_type [, privilege_type ...]}
  9. privilege_type:
  10. ALTER
  11. | CREATE
  12. | CREATE USER
  13. | CREATE VIEW
  14. | DELETE
  15. | DROP
  16. | GRANT OPTION
  17. | INDEX
  18. | INSERT
  19. | PROCESS
  20. | SELECT
  21. | SHOW DATABASES
  22. | SHOW VIEW
  23. | SUPER
  24. | UPDATE
  25. | USAGE
  26. privilege_level:
  27. *
  28. | *.*
  29. | database_name.*
  30. | database_name.table_name
  31. | table_name
  32. user_option_list:
  33. user_option [, user_option ...]
  34. user_option:
  35. user_name [IDENTIFIED BY 'password']
  36. password:
  37. STR_VALUE
  38. user_name_list:
  39. user_name [, user_name ...]

白名单管理

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

  1. obclient> SHOW VARIABLES LIKE "ob_tcp_invited_nodes";
  2. +----------------------+-------+
  3. | Variable_name | Value |
  4. +----------------------+-------+
  5. | ob_tcp_invited_nodes | % |
  6. +----------------------+-------+

权限限制

Lock 用户操作:

  1. obclient> ALTER USER demo ACCOUNT LOCK;
  2. Query OK, 0 rows affected (0.02 sec)
  3. obclient -udemo@demo0_111 -P2881 -h10.10.10.1 -pttt
  4. obclient: [Warning] Using a password on the command line interface can be insecure.
  5. ERROR 3118 (HY000): User locked

Unlock 用户操作:

  1. obclient> ALTER USER demo ACCOUNT UNLOCK;
  2. Query OK, 0 rows affected (0.02 sec)
  3. obclient -udemo@demo0_111 -P2881 -h10.10.10.1 -pttt
  4. obclient: [Warning] Using a password on the command line interface can be insecure.
  5. Welcome to the OceanBase monitor. Commands end with ; or \g.
  6. Your OceanBase connection id is 3222145887
  7. Server version: 5.7.25 OceanBase 2.2.30 (r20200715203914-b36a08355d624ed1923c4782d11e06a92441cebe) (Built Jul 15 2020 21:30:23)
  8. Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  12. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  13. obclient>

权限授予操作:

  1. obclient> GRANT ALL PRIVILEGES ON *.* TO demo with grant option;

权限摘除操作:

  1. obclient> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'demo';
  2. Query OK, 0 rows affected (0.03 sec)