mysql-用户权限 & 修改用户密码

青旅半醒 2022-07-14 14:46 325阅读 0赞

研究mysql用户权限是有原因的，惨痛的教训。  
代码放到git上面的时候，不小心把服务器IP、数据库用户名、密码都泄露了，当初懒，只设了两个拥有各种权限的用户，结果就是数据库被清空了，还留了QQ，加QQ付钱给还原数据。倔强如我，数据库卸了重装！庆幸里面没放很重要的数据，而且还有备份。  
所以有了前面安装mysql的博文。讲正事儿，mysql的用户权限。  
废话不多说，上命令：

root@ip-x-x-x-:~# mysql -u username -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 147
    Server version: 5.5.53-0ubuntu0.14.04.1 (Ubuntu)
    
    Copyright (c) 2000, 2016, 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.
    
    mysql> show databases; # 看一下有哪些数据库 +--------------------+
    | Database | +--------------------+
    | information_schema | | db_xxx             |
    | db_xxx | | db_xxx             |
    | db_xxx | | mysql | | performance_schema |
    +--------------------+
    n rows in set (0.01 sec)
    
    mysql> use mysql;    # 切换到mysql数据库
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    
    mysql> show tables; # 看一下有哪些数据表，我们要用的是user表 +---------------------------+
    | Tables_in_mysql | +---------------------------+
    | columns_priv | | db | | event | | func | | general_log               |
    | help_category | | help_keyword              |
    | help_relation | | help_topic                |
    | host                      |
    | ndb_binlog_index          |
    | plugin                    |
    | proc                      |
    | procs_priv | | proxies_priv              |
    | servers                   |
    | slow_log | | tables_priv               |
    | time_zone | | time_zone_leap_second     |
    | time_zone_name            |
    | time_zone_transition      |
    | time_zone_transition_type | | user | +---------------------------+ 24 rows in set (0.00 sec) 
    mysql> select user, host, password from user; +---------------+-----------+------------------+
    | user | host | password | +---------------+-----------+------------------+
    | usename1      | ip1       | password         |
    | usename2 | ip2 | password | +---------------+-----------+------------------+
    n rows in set (0.00 sec)
    # 这里说明一下，host字段为“%”的表示允许所有ip使用此用户访问
    
    # 创建一个所有ip可用的用户
    mysql> CREATE USER 'newuser'@'%' IDENTIFIED BY '123';  
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> select user, host, password from user; +--------------+----------------+---------------+
    | user | host | password | +--------------+----------------+---------------+
    | newuser | % | *23AE809DDAC | +--------------+----------------+---------------+
    12 rows in set (0.00 sec)
    
    # 创建一个仅能在本地使用的用户
    mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY '123';  
    Query OK, 0 rows affected (0.00 sec)
    mysql> select user, host, password from user; +--------------+----------------+---------------+
    | user | host | password | +--------------+----------------+---------------+
    | newuser      | %              | *23AE809DDAC | | newuser | localhost | *23AE809DDAC  |
    +--------------+----------------+---------------+
    12 rows in set (0.00 sec)
    
    # 添加完之后刷新一下
    mysql> flush privileges;
    
    # 接下来给用户分配权限
    # 语句格式如下：grant authority on database.table to username@host identified by password;
    mysql> grant select, update, insert on db_name.* to 'username'@'host' identified by 'password'; mysql> flush privileges; mysql> show grants for 'username'@'host'; +----------------------------------------------+ | Grants for mysql_app@%                       |
    +----------------------------------------------+
    | GRANT USAGE ON *.* TO 'username'@'host' IDENTIFIED BY PASSWORD '*passwd' |
    | GRANT SELECT, INSERT, UPDATE ON `database`.* TO 'username'@'host' | +-------------------------------------+
    2 rows in set (0.00 sec)
    # 完毕

再来看一下精简的命令。

# 登录数据库
    root@ip-x-x-x-:~# mysql -u username -p
    Enter password: 
    
    # 切换数据库
    mysql> use mysql;
    
    # 查看存在的用户
    mysql> select user, host, password from user;
    
    # 创建新用户 "host"部分可以是ip，%表示任意ip
    mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'passwd';
    # 刷新一下
    mysql> flush privileges;
    
    # 给新用户赋权限
    mysql> grant select, update, insert on db_name.* to 'username'@'host' identified by 'password';
    # 刷新一下
    mysql> flush privileges;
    
    # 完毕

写在最后：删表等操作赋权限要慎重，建议多用户分邦（数据库）而治。

2017-12-01补充：  
好久没有这么深层的用过mysql了，都快忘记怎么修改用户密码了。借着用到新的mysql，重温修改密码的命令

use mysql;
    select * from user;
    update user set password=password('123') where user='username';
    flush privileges;