Mysql用户&&权限管理

àì夳堔傛蜴生んèń 2022-04-24 06:12 416阅读 0赞

## Mysql用户&&权限管理 ##

**背景**  
MySQL 默认有个root用户，但是这个用户权限太大，一般只在管理数据库时候才用。如果在项目中要连接 MySQL 数据库，则建议新建一个权限较小的用户来连接。在稍微大一点的公司里面都是进行着明显的权限管理。特别是操作数据库这样重要的东西，万一你要是一不小心删库了，就麻烦了，因为root的权限太多（增删改查权限都有），一不小心就会给公司造成很大损失。所以在公司里面一般一个项目都会创建一个专门的用户，指定其权限只能控制某一个数据库进行操作。

**一、创建新用户**

用户被创建后，包括密码、权限和资源限制等信息都会被存储在一个名为mysql数据库中的名为user的表中。

1.  创建新用户

命令:
    CREATE USER 'username'@'host' IDENTIFIED BY 'password';
    
    说明:
    username：你将新建的用户名
    host：指定该用户可以在哪个主机上登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%.
    password：该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.
    
    例子: 
    CREATE USER 'hejie'@'localhost' IDENTIFIED BY '123456';  //只能在本地计算机（127.0.0.1）上访问
    CREATE USER 'hejie'@'192.168.1.101' IDENDIFIED BY '123456';  //只能在该IP下才能访问
    CREATE USER 'hejie'@'192.168.1.%' IDENTIFIED BY '123456';  //只能在该IP段内才能访问
    CREATE USER 'hejie'@'%' IDENTIFIED BY '';  //可以在任意IP下访问，允许外网访问，并且不用输入密码

1.  删除用户

drop user '用户名'@'IP地址';
    例子：
    drop user 'hejie'@'localhost';

1.  修改用户名

rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

1.  修改密码

set password for '用户名'@'IP地址' = Password('新密码');

1.  查询存在的所有用户  
    在Mysql中其实有一个内置且名为mysql的数据库，这个数据库中存储的是Mysql的一些数据，比如用户、权限信息、存储过程等

select User,Host FROM mysql.user;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjg2OTM2NQ_size_16_color_FFFFFF_t_70]

1.  切换用户  
    ![在这里插入图片描述][20190422091708191.jpg]
2.  查看当前登录使用的是什么用户

select current_user();或者select user();

![在这里插入图片描述][20190422080952461.jpg]![在这里插入图片描述][20190422081016254.jpg]  
**二、授予权限**  
mysql的权限就是允许做你权利以内的事情，不可以越界。比如只允许select操作，就不能执行update操作。只允许你从某台机器上连接mysql，那么你就不能从其他机器连接mysql。  
mysql权限的实现分为两个阶段验证：  
第一阶段：服务器首先会检查你是否允许连接。因为创建用户的时候会加上主机限制，可以限制成本地、某个IP、某个IP段、以及任何地方等，只允许你从配置的指定地方登陆。  
第二阶段：如果你能连接，Mysql会检查你发出的每个请求，看你是否有足够的权限实施它。比如你要更新某个表、或者查询某个表，Mysql会查看你对那个表或者那个列是否有权限。再比如，你要运行某个存储过程，Mysql会检查你对存储过程是否有执行权限等。

**对root用户和普通用户都可以进行权限操作。。。。所有权限的操作必须在root用户下进行，在普通用户下无法进行授权和删除权限操作，但可以执行查询权限操作**

1.  授权

命令: grant 权限 on 数据库.表 to '用户'@'IP地址';
    grant privileges ON databasename.tablename TO 'username'@'ip地址'
    
    说明: 
    privileges：用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所的权限则使用ALL
    databasename：数据库名
    tablename：表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 如*.*
    
    例子: 
    GRANT SELECT, INSERT ON practice.user1 TO 'hejie'@'localhost'; //对practice数据库的user1表仅有select和insert权限
    GRANT ALL ON *.* TO 'hejie'@'localhost'; //对所有数据库的所有表有所有操作权限，all等同于all privileges
    
    注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
    GRANT privileges ON databasename.tablename TO 'username'@'ip地址' WITH GRANT OPTION;

1.  权限刷新

使用这个命令使权限生效，尤其是你对那些权限做了update或者delete更新的时候。以前遇到过使用grant后权限没有更新的情况，只要对权限做了更改就使用flush privileges命令来刷新权限

flush privileges;

1.  查看权限

show grants for '用户'@'IP地址'

1.  收回/删除权限

revoke 权限 on 数据库.表 from '用户'@'IP地址';

1.  mysql中的权限一共29个

<table> 
 <thead> 
  <tr> 
   <th>权限</th> 
   <th>说明</th> 
   <th>举例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>usage</td> 
   <td>连接（登陆）权限，建立一个用户，就会自动授予其usage权限（默认授予）。该权限只能用于数据库登陆，不能执行任何操作；且usage权限不能被回收，也即revoke用户并不能删除usage权限。</td> 
   <td>grant usage on <em>.</em> to 'hejie′@‘localhost’ identified by ‘123456’;</td> 
  </tr> 
  <tr> 
   <td>select</td> 
   <td>必须有select的权限，才能执行select操作</td> 
   <td>grant select on * .* to 'hejie′@‘localhost’;</td> 
  </tr> 
  <tr> 
   <td>insert</td> 
   <td>必须有insert的权限，才可以使用insert into …… values….</td> 
   <td>grant insert on pyt.* to 'root′@‘localhost’; insert into shop(name) values(‘aa’);</td> 
  </tr> 
  <tr> 
   <td>update</td> 
   <td>必须有update的权限，才可以使用update table</td> 
   <td>update shop set price=3.5 where article=0001 and dealer=‘A’;</td> 
  </tr> 
  <tr> 
   <td>delete</td> 
   <td>必须有delete的权限，才可以使用delete from ….where….(删除表中的记录)</td> 
   <td>grant delete on pyt.* to 'root′@‘localhost’;delete from table where id=1;</td> 
  </tr> 
  <tr> 
   <td>alter</td> 
   <td>必须有alter的权限，才可以使用alter table</td> 
   <td>alter table shop modify dealer char(15);</td> 
  </tr> 
  <tr> 
   <td>alter routine</td> 
   <td>必须具有alter routine的权限，才可以使用{alter drop} {procedure/function}</td> 
   <td>grant alter routine on pyt.* to ‘root′@’ localhost ';</td> 
  </tr> 
  <tr> 
   <td>create</td> 
   <td>必须具有create routine的权限，才可以使用{create/alter/drop} {procedure/function}</td> 
   <td>grant create on pyt.* to 'root′@‘localhost’;</td> 
  </tr> 
  <tr> 
   <td>create temporary tables</td> 
   <td>必须有create temporary tables的权限，才可以使用create temporary tables.(注意这里是tables，不是table)</td> 
   <td>grant create temporary tables on pyt.* to 'root′@‘localhost’;</td> 
  </tr> 
  <tr> 
   <td>create view</td> 
   <td>必须有create view的权限，才可以使用create view</td> 
   <td>grant create view on pyt.* to 'root′@‘localhost’;</td> 
  </tr> 
  <tr> 
   <td>create user</td> 
   <td>要使用CREATE USER，必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。</td> 
   <td>grant create user on * .* to 'root′@‘localhost’;</td> 
  </tr> 
  <tr> 
   <td>drop</td> 
   <td>必须有drop的权限，才可以删除库、表、索引、视图等</td> 
   <td>drop database db_name; drop table tab_name;drop view vi_name;drop index in_name;</td> 
  </tr> 
  <tr> 
   <td>show database</td> 
   <td>通过show database只能看到你拥有的某些权限的数据库，除非你拥有全局SHOW DATABASES权限。对于root@localhost用户来说，没有对mysql数据库的权限，所以以此身份登陆查询时，无法看到mysql数据库</td> 
   <td>show databases;</td> 
  </tr> 
  <tr> 
   <td>show view</td> 
   <td>必须拥有show view权限，才能执行show create view</td> 
   <td>show create view name;</td> 
  </tr> 
  <tr> 
   <td>index</td> 
   <td>必须拥有index权限，才能执行[create/drop] index</td> 
   <td>grant index on pyt.* to root@localhost;create index ix_shop on shop(article);drop index ix_shop on shop;</td> 
  </tr> 
  <tr> 
   <td>excute</td> 
   <td>执行存在的Functions,Procedures</td> 
   <td>call pro_shoroot(0001,@a)；</td> 
  </tr> 
  <tr> 
   <td>event</td> 
   <td>event的使用频率较低建议使用root用户进行创建和维护。要使event起作用，MySQL的常量GLOBAL event_scheduler必须为on或者是1</td> 
   <td>show global variables like ‘event_scheduler’;</td> 
  </tr> 
  <tr> 
   <td>lock tables</td> 
   <td>必须拥有lock tables权限，才可以使用lock tables</td> 
   <td>grant lock tables on pyt.* to root@localhost;lock tables a1 read;unlock tables;</td> 
  </tr> 
  <tr> 
   <td>references</td> 
   <td>有了REFERENCES权限，用户就可以将其它表的一个字段作为某一个表的外键约束。</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>reload</td> 
   <td>必须拥有reload权限，才可以执行flush [tables / logs / privileges]</td> 
   <td>grant reload on * .* to 'root′@‘localhost’;</td> 
  </tr> 
  <tr> 
   <td>replication client</td> 
   <td>拥有此权限可以查询master server、slave server状态。</td> 
   <td>grant Replication client on <em>.</em> to root@localhost;或grant super on <em>.</em> to root@localhost; show master status;</td> 
  </tr> 
  <tr> 
   <td>replication slave</td> 
   <td>拥有此权限可以查看从服务器，从主服务器读取二进制日志。</td> 
   <td>grant replication slave on <em>.</em> to root@localhost;show slave hosts;show binlog events;</td> 
  </tr> 
  <tr> 
   <td>Shutdown</td> 
   <td>关闭mysql权限</td> 
   <td>$ mysqladmin shutdown</td> 
  </tr> 
  <tr> 
   <td>grant option</td> 
   <td>拥有grant option，就可以将自己拥有的权限授予其他用户（仅限于自己已经拥有的权限）</td> 
   <td>grant Grant option on pyt.* to root@localhost;grant select on pyt.* to p2@localhost;</td> 
  </tr> 
  <tr> 
   <td>process</td> 
   <td>通过这个权限，用户可以执行SHOW PROCESSLIST和KILL命令。默认情况下，每个用户都可以执行SHOW PROCESSLIST命令，但是只能查询本用户的进程。</td> 
   <td>show processlist;</td> 
  </tr> 
  <tr> 
   <td>file</td> 
   <td>拥有file权限才可以执行 select …into outfile和load data infile…操作，但是不要把file, process, super权限授予管理员以外的账号，这样存在严重的安全隐患</td> 
   <td>grant file on <em>.</em> to root@localhost;grant file on <em>.</em> to root@localhost;</td> 
  </tr> 
  <tr> 
   <td>super</td> 
   <td>这个权限允许用户终止任何查询；修改全局变量的SET语句；使用CHANGE MASTER，PURGE MASTER LOGS。</td> 
   <td>grant super on <em>.</em> to root@localhost;purge master logs before 'mysql-bin.000006′;</td> 
  </tr> 
  <tr> 
   <td>all privileges</td> 
   <td>所有权限。with grant option 可以连带授权</td> 
   <td>grant all privileges on pyt.* to root@localhost with grant option;</td> 
  </tr> 
 </tbody> 
</table>

## 举例 ##

revoke用户hejie的select权限后，查询权限，发现已经没有了select权限  
![在这里插入图片描述][2019042209022172.jpg]  
下图表示没有select权限，无法进行查询  
![在这里插入图片描述][20190422084056146.jpg]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjg2OTM2NQ_size_16_color_FFFFFF_t_70]: /images/20220218/ae3516c6b0e64f8f80bf03dbac7b5a23.png
[20190422091708191.jpg]: /images/20220218/fdf61ec21b1e4900a7f18e8cd9d12549.png
[20190422080952461.jpg]: https://img-blog.csdnimg.cn/20190422080952461.jpg
[20190422081016254.jpg]: /images/20220218/753f90318c3b4a659faaaa1fdee0c962.png
[2019042209022172.jpg]: /images/20220218/961988cbcc1e4cf49d0905eee14ee197.png
[20190422084056146.jpg]: /images/20220218/05c395da40d04e6c9670d5220dd7c014.png