MySQL用户管理及SQL语句详解

[(none)]>select user,host from mysql.user; #查询用户和主机 +---------------+-----------+ | user | host | +---------------+-----------+ | root | 10.0.0.% | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 4 rows in set (0.00 sec)

1.1.2 用户的作用

1、用户登录

TCP/<span style="color: #000000;">IP方式（远程、本地）：
mysql </span>-uroot -poldboy123 -h <span style="color: #800080;">10.0</span>.<span style="color: #800080;">0.51</span> -<span style="color: #000000;">P3306
Socket方式(仅本地)：
mysql </span>-uroot -poldboy123 -S /tmp/mysql.sock<br><br><br>

<code class="  language-kotlin">用户名@<span class="token string">‘白名单‘
<span class="token label symbol">wordpress@<span class="token string">‘10.0.0.%‘    
<span class="token label symbol">wordpress@<span class="token string">‘%‘
<span class="token label symbol">wordpress@<span class="token string">‘10.0.0.200‘
<span class="token label symbol">wordpress@<span class="token string">‘localhost‘
<span class="token label symbol">wordpress@<span class="token string">‘db02‘
<span class="token label symbol">wordpress@<span class="token string">‘10.0.0.5%‘
<span class="token label symbol">wordpress@<span class="token string">‘10.0.0.0/255.255.254.0‘</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

2、用于管理数据库及数据

<span style="color: #000000;">增：
mysql</span>> create user oldboy@<span style="color: #800000;">‘</span><span style="color: #800000;">10.0.0.%</span><span style="color: #800000;">‘</span> identified by <span style="color: #800000;">‘</span><span style="color: #800000;">123</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
查：
mysql</span>> desc mysql.user;    ----><span style="color: #000000;">  authentication_string
mysql</span>> <span style="color: #0000ff;">select</span> user ,host ,authentication_string <span style="color: #0000ff;">from</span><span style="color: #000000;"> mysql.user  查用户密码，一般都是加密
改:
mysql</span>> alter user oldboy@<span style="color: #800000;">‘</span><span style="color: #800000;">10.0.0.%</span><span style="color: #800000;">‘</span> identified by <span style="color: #800000;">‘</span><span style="color: #800000;">456</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
删：
mysql</span>> drop user oldboy@<span style="color: #800000;">‘</span><span style="color: #800000;">10.0.0.%</span><span style="color: #800000;">‘</span>;

命令:

  grant 权限 on 权限范围  to 用户  identified  by ‘密码‘

权限

     对数据库的读、写等操作
    （insert update、select、delete、drop、create等）

<code class="  language-dart">ALL<span class="token punctuation">:
SELECT<span class="token punctuation">,INSERT<span class="token punctuation">, UPDATE<span class="token punctuation">, DELETE<span class="token punctuation">, CREATE<span class="token punctuation">, DROP<span class="token punctuation">, RELOAD<span class="token punctuation">, SHUTDOWN<span class="token punctuation">, PROCESS<span class="token punctuation">, FILE<span class="token punctuation">, REFERENCES<span class="token punctuation">, INDEX<span class="token punctuation">, ALTER<span class="token punctuation">, SHOW DATABASES<span class="token punctuation">, SUPER<span class="token punctuation">, CREATE TEMPORARY TABLES<span class="token punctuation">, LOCK TABLES<span class="token punctuation">, EXECUTE<span class="token punctuation">, REPLICATION SLAVE<span class="token punctuation">, REPLICATION CLIENT<span class="token punctuation">, CREATE VIEW<span class="token punctuation">, SHOW VIEW<span class="token punctuation">, CREATE ROUTINE<span class="token punctuation">, ALTER ROUTINE<span class="token punctuation">, CREATE USER<span class="token punctuation">, EVENT<span class="token punctuation">, TRIGGER<span class="token punctuation">, CREATE TABLESPACE
ALL <span class="token punctuation">: 以上所有权限，一般是普通管理员拥有的
<span class="token keyword">with grant option：超级管理员才具备的，给别的用户授权的功能</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

权限范围

*.*         所有库所有表                ----><span style="color: #000000;">管理员用户
wordpress.</span>*          指定当前库所有表授权  ----><span style="color: #000000;">开发和应用用户
wordpress.t1   当前库指定表 ti 授权</span>

1.1.3 【练习题】按照要求创建用户

用户只能通过10.0.0.0/24网段访问，用户名为clsn 密码为123

这个用户只能对clsn数据库下的对象进行增insert create、改update 、查select；

  创建命令：

    grant select,create,insert,update on clsn.* to ‘clsn‘@‘10.0.0.%‘ identified by ‘123‘;

查看用户权限

<code class="  language-css">mysql> show grants for app@<span class="token string">‘10.0.0.%‘<span class="token punctuation">;</span></span></code>

1.14 本地管理员用户密码忘记.

[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &<span style="color: #000000;">
mysql
mysql</span>><span style="color: #000000;"> flush privileges;
mysql</span>> alter user root@<span style="color: #800000;">‘</span><span style="color: #800000;">localhost</span><span style="color: #800000;">‘</span> identified by <span style="color: #800000;">‘</span><span style="color: #800000;">123</span><span style="color: #800000;">‘</span>;

查看当前存在的用户:

<span style="color: #0000ff;">select</span> user,host <span style="color: #0000ff;">from</span> mysql.user;

企业里创建用户一般是授权一个内网网段登录，最常见的网段写法有两种。

方法1：172.16.1.%（%为通配符，匹配所有内容）。

方法2：172.16.1.0/255.255.255.0，但是不能使用172.16.1.0/24，是个小遗憾。

标准的建用户方法：

create user <span style="color: #800000;">‘</span><span style="color: #800000;">web</span><span style="color: #800000;">‘</span>@<span style="color: #800000;">‘</span><span style="color: #800000;">172.16.1.%</span><span style="color: #800000;">‘</span> identified by <span style="color: #800000;">‘</span><span style="color: #800000;">web123</span><span style="color: #800000;">‘</span>;

1.1.5用户授权

给用户授权

# 创建用户
create user ‘clsn‘@‘localhost‘ identified by ‘clsn123‘;
# 查看用户
select user,host from mysql.user;
# 授权所有权限给clsn用户
GRANT ALL ON *.* TO ‘clsn‘@‘localhost‘;
# 查看clsn用户的权限
SHOW GRANTS FOR ‘clsn‘@‘localhost‘\G

创建用户的同时授权

grant all on *.* to clsn@‘172.16.1.%‘ identified by ‘clsn123‘;
# 刷新权限
flush privileges; #<==可以不用。

创建用户然后授权

create user ‘clsn‘@‘localhost‘ identified by ‘clsn123‘;
GRANT ALL ON *.* TO ‘clsn‘@‘localhost‘;

授权和root一样的权限

grant all on *.* to system@‘localhost‘ identified by ‘clsn123‘ with grant option;

授权给用户select,create,insert,update 权限

grant select,create,insert,update on clsn.* to ‘clsn‘@‘10.0.0.%‘ identified by ‘123‘;

回收权限

REVOKE INSERT ON *.* FROM clsn@localhost;

可以授权的用户权限

INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, 
PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, 
CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, 
REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER 
ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

【示例】博客授权收回示例

grant select,insert,update,delete,create,drop on blog.* to ‘blog‘@‘172.16.1.%‘ identified by ‘blog123‘;
revoke create,drop on blog.* from ‘blog‘@‘172.16.1.%‘;

   授权博客类的最多权限：select,insert,update,delete

1.2 MySQL 客户端工具

1.2.2.1  source命令的使用

mysql> SOURCE /data/mysql/world.sql

或者使用非交互式：(尽量避免使用mysql 导入数据，会产生大量的无用日志)

mysql</data/mysql/world.sql

1.2.2 mysqladmin命令说明

基本语法

mysqladmin -u<name> -p<password> commands

　

 

 

 

 

MySQL用户管理及SQL语句详解

标签：lan   replicat   numbers   创建   view   客户端   number   temporary   管理数据