mysql创建数据库和用户并授权

谁借莪１个温暖的怀抱￠ 2021-12-08 17:45 385阅读 0赞

转自：[https://www.cnblogs.com/chenjianxiang/p/7053830.html][https_www.cnblogs.com_chenjianxiang_p_7053830.html]

1.通过mysql数据库的user表查看用户相关信息

![复制代码][copycode.gif]

![复制代码][copycode.gif]

2.创建数据库

mysql> create database [databasename] default character set utf8 collate utf8_general_ci;
    Query OK, 1 row affected (0.00 sec)

3.创建用户

mysql> create user 'dba'@'%' identified by '*******';
    Query OK, 0 rows affected (0.00 sec)

user表中host列的值的意义

%               匹配所有主机

localhost    localhost不会被解析成IP地址，直接通过UNIXsocket连接

127.0.0.1   会通过TCP/IP协议连接，并且只能在本机访问；

::1              ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

4.对dba用户给予针对指定数据库进行增删改查的权限

mysql> grant select,insert,update,delete,create on [databasename].* to dba;
    Query OK, 0 rows affected (0.00 sec)

注意：修改完权限以后 一定要刷新服务，或者重启服务，刷新服务用：FLUSH PRIVILEGES  
  
5.可以通过show grants命令查看权限，若想要在原来的基础上增加权限则继续执行grant

![复制代码][copycode.gif]

mysql> grant drop on [databasename].* to dba;
    Query OK, 0 rows affected (0.00 sec)
    mysql> show grants for dba;
    +----------------------------------------------------------------------------------------------------+
    | Grants for dba@%                                                                                   |
    +----------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'dba'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `[databasename]`.* TO 'dba'@'%'              |
    +----------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

![复制代码][copycode.gif]

6.通过revoke命令可以移除用户的相关权限

![复制代码][copycode.gif]

mysql> revoke drop on [databasename].* from dba;
    Query OK, 0 rows affected (0.00 sec)
    mysql> show grants for dba;
    +----------------------------------------------------------------------------------------------------+
    | Grants for dba@%                                                                                   |
    +----------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'dba'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `[databasename]`.* TO 'dba'@'%'                    |
    +----------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

[https_www.cnblogs.com_chenjianxiang_p_7053830.html]: https://www.cnblogs.com/chenjianxiang/p/7053830.html
[copycode.gif]: /images/20211209/f4f64c1bbc524d51a0a700b012e21184.png