MySql Database 用户与权限管理

创建用户 mysql> create user 'tpcc'@'%' identified by 'tpcc'; Query OK, 0 rows affected (0.02 sec) 修改用户密码 mysql> set password for 'tpcc'@'%'=password('abcd.1234'); Query OK, 0 rows affected (0.03 sec) 查询用户权限 mysql> show grants for 'tpcc'@'%'; +-----------------------------------------------------------------------------------------------------+ | Grants for tpcc@%                                                                                   | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*8E329B15E3C0FF9DDF7597B748CCE9473593BF60' | +-----------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 授予列级别的权限 mysql> grant all (w_id) on tpcc1000.warehouse to 'tpcc'@'%'; Query OK, 0 rows affected (0.02 sec) mysql> grant select (w_name) on tpcc1000.warehouse to 'tpcc'@'%'; Query OK, 0 rows affected (0.03 sec) mysql> grant insert (w_street_1) on tpcc1000.warehouse to 'tpcc'@'%'; Query OK, 0 rows affected (0.04 sec) mysql> select * from mysql.columns_priv; +------+----------+------+------------+-------------+---------------------+-------------+ | Host | Db       | User | Table_name | Column_name | Timestamp           | Column_priv | +------+----------+------+------------+-------------+---------------------+-------------+ | %    | tpcc1000 | tpcc | warehouse  | w_id        | 0000-00-00 00:00:00 | Select      | | %    | tpcc1000 | tpcc | warehouse  | w_name      | 0000-00-00 00:00:00 | Select      | | %    | tpcc1000 | tpcc | warehouse  | w_street_1  | 0000-00-00 00:00:00 | Insert      | +------+----------+------+------------+-------------+---------------------+-------------+ 3 rows in set (0.00 sec) mysql> show grants for 'tpcc'@'%'; +-----------------------------------------------------------------------------------------------------+ | Grants for tpcc@%                                                                                   | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' | | GRANT SELECT (w_id, w_name), INSERT (w_street_1) ON `tpcc1000`.`warehouse` TO 'tpcc'@'%'            | +-----------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> revoke select (w_id, w_name), insert (w_street_1) on tpcc1000.warehouse from 'tpcc'@'%'; Query OK, 0 rows affected (0.03 sec) mysql> show grants for 'tpcc'@'%'; +-----------------------------------------------------------------------------------------------------+ | Grants for tpcc@%                                                                                   | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' | +-----------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 授予表级别的权限 mysql> grant all on tpcc1000.warehouse to 'tpcc'@'%'; Query OK, 0 rows affected (0.03 sec) mysql> show grants for 'tpcc'@'%'; +-----------------------------------------------------------------------------------------------------+ | Grants for tpcc@%                                                                                   | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' | | GRANT ALL PRIVILEGES ON `tpcc1000`.`warehouse` TO 'tpcc'@'%'                                        | +-----------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from mysql.tables_priv where user='tpcc'\G; *************************** 1. row ***************************        Host: %          Db: tpcc1000        User: tpcc  Table_name: warehouse     Grantor: root@localhost   Timestamp: 0000-00-00 00:00:00  Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger Column_priv:  1 row in set (0.00 sec) mysql> revoke all on tpcc1000.warehouse from 'tpcc'@'%'; Query OK, 0 rows affected (0.02 sec) mysql> show grants for 'tpcc'@'%'; +-----------------------------------------------------------------------------------------------------+ | Grants for tpcc@%                                                                                   | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' | +-----------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 授予库级别的权限 mysql> grant all on tpcc1000.* to 'tpcc'@'%';   Query OK, 0 rows affected (0.02 sec) mysql> show grants for 'tpcc'@'%'; +-----------------------------------------------------------------------------------------------------+ | Grants for tpcc@%                                                                                   | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' | | GRANT ALL PRIVILEGES ON `tpcc1000`.* TO 'tpcc'@'%'                                                  | +-----------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from mysql.db where user='tpcc'\G; *************************** 1. row ***************************                  Host: %                    Db: tpcc1000                  User: tpcc           Select_priv: Y           Insert_priv: Y           Update_priv: Y           Delete_priv: Y           Create_priv: Y             Drop_priv: Y            Grant_priv: N       References_priv: Y            Index_priv: Y            Alter_priv: Y Create_tmp_table_priv: Y      Lock_tables_priv: Y      Create_view_priv: Y        Show_view_priv: Y   Create_routine_priv: Y    Alter_routine_priv: Y          Execute_priv: Y            Event_priv: Y          Trigger_priv: Y 1 row in set (0.00 sec) mysql> revoke all on tpcc1000.* from 'tpcc'@'%';   Query OK, 0 rows affected (0.02 sec) mysql> show grants for 'tpcc'@'%'; +-----------------------------------------------------------------------------------------------------+ | Grants for tpcc@%                                                                                   | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' | +-----------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 授予全局权限 mysql> grant all on *.* to 'tpcc'@'%';   Query OK, 0 rows affected (0.01 sec) mysql> show grants for 'tpcc'@'%'; +--------------------------------------------------------------------------------------------------------------+ | Grants for tpcc@%                                                                                            | +--------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' | +--------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from mysql.user where user='tpcc'\G; *************************** 1. row ***************************                   Host: %                   User: tpcc               Password: *D2B995B159B5840C27690FC435C6ACEE7B9E1348            Select_priv: Y            Insert_priv: Y            Update_priv: Y            Delete_priv: Y            Create_priv: Y              Drop_priv: Y            Reload_priv: Y          Shutdown_priv: Y           Process_priv: Y              File_priv: Y             Grant_priv: N        References_priv: Y             Index_priv: Y             Alter_priv: Y           Show_db_priv: Y             Super_priv: Y  Create_tmp_table_priv: Y       Lock_tables_priv: Y           Execute_priv: Y        Repl_slave_priv: Y       Repl_client_priv: Y       Create_view_priv: Y         Show_view_priv: Y    Create_routine_priv: Y     Alter_routine_priv: Y       Create_user_priv: Y             Event_priv: Y           Trigger_priv: Y Create_tablespace_priv: Y               ssl_type:              ssl_cipher:             x509_issuer:            x509_subject:           max_questions: 0            max_updates: 0        max_connections: 0   max_user_connections: 0                 plugin: mysql_native_password  authentication_string:        password_expired: N 1 row in set (0.00 sec) mysql> revoke all on *.* from 'tpcc'@'%'; Query OK, 0 rows affected (0.03 sec) mysql>  show grants for 'tpcc'@'%'; +-----------------------------------------------------------------------------------------------------+ | Grants for tpcc@%                                                                                   | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' | +-----------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 删除用户 mysql> drop user 'tpcc'@'%'; Query OK, 0 rows affected (0.07 sec) 创建用户 mysql> create user 'tpcc'@'%' identified by