[TOC]

连接

主要登录方式

方式一
默认是当前登录的系统用户 $USER@localhost

[root@mysqlenv_test ~]#mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

方式二 使用socket连接
LINUX 系统才有socket连接

mysql -S /tmp/mysql.sock -uroot -p

方式三 指定主机名

mysql -h 127.0.0.1 -p   

mysql -h localhost -p 

# 属于不同的用户,授权时需要指定的host不同

免密码登录

主要是修改my.cnf
方式一 

cat /etc/my.cnf 
[client]
user="root"  
password="123456"

方式二 

#单对定义不同的客户端

[mysql] # 这个是给/usr/loca/mysql/bin/mysql 使用的
user=root
password="password"

[mysqladmin] # 给mysqladmin使用
user=root
password="password"

# 用户自己家目录设置
#Filename: ~/.my.cnf

[client]
user="root"
password="你的密码"

方式三 使用密文登录
mysql,mysqladmin，mysqldump都可以使用

# 1. 生成密文
[root@mysqlenv_test ~]#mysql_config_editor set -G login_sert_passwd -h localhost -u root -p
Enter password: 

/root/.mylogin.cnf # 默认会在用户的家目录生成.mylogin.cnf隐藏文件
# 2.查看所有的密文
[root@mysqlenv_test ~]#mysql_config_editor print --all
[login_sert_passwd]
user = root
password = *****
host = localhost

# 3. 使用密文登录
mysql --login-path=login_sert_passwd
mysqladmin  --login-path=login_sert_passwd status
mysqldump  --login-path=login_sert_passwd  --opt mysql

参数类型

  • 全局参数: GLOBAL
    • 可修改参数
    • 不可修改参数
  • 会话参数: SESSION
    • 可修改参数
    • 不可修改参数

注意：

  1. 用户登录只能在线修改非只读参数,只读参数只能在my.cnf配置文件中预先设置，重启数据库方能生效（例如监听的端口 port,bind等参数）
  2. 在线修改的所有参数（GLOBAL或者SESSION）生效周期是当前正在运行的状态数据库，重启后在线修改的配置作用都会丢失，只有修改my.cnf才能永久生效，（例如set GLOBAL slow_query_log =0;）
  3. 有些参数即存在于GLOBAL又存在于SESSION， （例如autocommit SET GLOBAL autocommit = 0;, SET SESSION autocommit = 1;）

参数设置

参数查看

mysql> SHOW GLOBAL VARIABLES ; # 只查看全局的变量参数
mysql> SHOW VARIABLES ; # 查看所有的参数

设置GLOBAL或SESSION参数

# 全局
mysql> SET GLOBAL autocommit = 0;

# 会话
mysql> SET SESSION autocommit = 1;


# 查看
mysql> SELECT @@GLOBAL.autocommit;
+---------------------+
| @@GLOBAL.autocommit |
+---------------------+
|                   0 |
+---------------------+

mysql> SELECT @@SESSION.autocommit;  # 等价于 SELECT @@autocommit;
+----------------------+
| @@SESSION.autocommit |
+----------------------+
|                    1 |
+----------------------+

权限

MySQL校验权限流程

Title: MySQL校验权限流程
开始 ->> IP和用户名能否访问: 查看mysql.user表 （所有库权限）
IP和用户名能否访问 ->> 指定库权限: 查看mysql.db表
指定库权限 ->> 指定表权限: 查看mysql.tables_priv表
指定表权限 ->>指定列权限:  查看mysql.columns_priv表
指定列权限 -->> 开始 : 没有权限

系统表权限信息:

  • a) 用户名和IP是否允许
  • b) 查看mysql.user表 // 查看全局所有库的权限
  • c) 查看mysql.db表 // 查看指定库的权限
  • d) 查看mysql.table_priv表 // 查看指定表的权限
  • e) 查看mysql.column_priv表 // 查看指定列的权限

所有权限详情

常用权限：

  • SQL语句：SELECT、INSERT、UPDATE、DELETE、INDEX
  • 存储过程：CREATE ROUTINE、ALTER ROUTINE、EXECUTE、TRIGGER
  • 管理权限：SUPER、RELOAD、SHOW DATABASE、SHUTDOWN、

可选资源项，资源限制类型:

  • MAX_QUERIES_PER_HOUR count
  • MAX_UPDATES_PER_HOUR count
  • MAX_CONNECTIONS_PER_HOUR count
  • MAX_USER_CONNECTIONS count

授权

当前用户权限

mysql> SHOW GRANTS ;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR root@'localhost';

查看详细权限信息
user, db , tables_priv ,columns_priv 这些表都是权限相关

mysql> SELECT * from user WHERE User='root' AND Host='localhost'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           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: Y
       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: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
      password_expired: N
 password_last_changed: 2017-09-16 15:56:38
     password_lifetime: NULL
        account_locked: N
1 row in set (0.02 sec)

操作
将被废弃的授权方式

mysql> GRANT ALL ON test.* TO tomcat@'127.0.0.1' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> SHOW GRANTS FOR tomcat@'127.0.0.1' ;
+----------------------------------------------------------+
| Grants for tomcat@127.0.0.1                              |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tomcat'@'127.0.0.1'               |
| GRANT ALL PRIVILEGES ON `test`.* TO 'tomcat'@'127.0.0.1' |
+----------------------------------------------------------+
2 rows in set (0.01 sec)

新的授权方式, 先创建用户后授权

mysql> CREATE USER 'nginx'@'127.0.0.1' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON sys.* TO 'nginx'@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)

具有再次授权

mysql> GRANT ALL   ON test.* TO 'apache'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
mysql> SHOW GRANTS FOR 'apache'@'%';
+--------------------------------------------------------------------+
| Grants for apache@%                                                |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'apache'@'%'                                 |
| GRANT ALL PRIVILEGES ON `test`.* TO 'apache'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

回收权限

  • REVOKE 只收回权限，并不会删除用户
  • revoke 语法同grant一致, 从grant ... to 变为revoke ... from
    # 回收SELECT 
    mysql> REVOKE SELECT ON test.* FROM  'apache'@'%';
    Query OK, 0 rows affected (0.19 sec)

    # 回收所有权限
    mysql> REVOKE ALL  ON test.* FROM  'apache'@'%';
    Query OK, 0 rows affected (0.01 sec)

    mysql> SHOW GRANTS FOR 'apache'@'%';
    +-----------------------------------------------------------+
    | Grants for apache@%                                       |
    +-----------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'apache'@'%'                        |
    | GRANT USAGE ON `test`.* TO 'apache'@'%' WITH GRANT OPTION |
    +-----------------------------------------------------------+

删除用户

mysql> DROP USER  'apache'@'%';
Query OK, 0 rows affected (0.04 sec)

角色

角色(Role)可以用来批量管理用户，同一个角色下的用户，拥有相同的权限。
MySQL5.7.X以后可以模拟角色(Role)的功能，通过mysql.proxies_priv模拟实现。
mysql.proxies_priv在5.5.X和5.6.X的时候就存在，但是无法模拟角色(Role)功能
继承关系，方便管理

是否开启权限代理功能

mysql> SHOW VARIABLES LIKE "%proxy%";
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| check_proxy_users                 | ON    |
| mysql_native_password_proxy_users | ON    |
| proxy_user                        |       |
| sha256_password_proxy_users       | ON    |
+-----------------------------------+-------+
4 rows in set (0.00 sec)

如果为off 需要修改my.cnf

# cat /etc/my.cnf
# ... ...
### 角色相关参数 ####
check_proxy_users = 1
mysql_native_password_proxy_users = 1
sha256_password_proxy_users = 1

操作

# 创建用户
mysql> CREATE USER 'web'@'127.0.0.1' IDENTIFIED BY '123456';
mysql> CREATE USER 'tomcat'@'127.%';
mysql> CREATE USER 'nginx'@'127.%';

# 权限映射, 将'web'@'127.0.0.1' 所有的权限映射给 nginx，tomcat用户
mysql> GRANT PROXY ON 'web'@'127.0.0.1' TO 'tomcat'@'127.%','nginx'@'127.%';
Query OK, 0 rows affected (0.03 sec)


# 授权
mysql> GRANT  SELECT ON *.* TO 'web'@'127.0.0.1';
Query OK, 0 rows affected (0.03 sec)


# 查看权限
mysql> SHOW GRANTS FOR 'web'@'127.0.0.1';
+------------------------------------------+
| Grants for web@127.0.0.1                 |
+------------------------------------------+
| GRANT SELECT ON *.* TO 'web'@'127.0.0.1' |
+------------------------------------------+
1 row in set (0.00 sec)


# 虽然nginx用户没有select权限，但是使用了代理模式，nginx用户将继承到了web用户的select权限
mysql> SHOW GRANTS FOR 'nginx'@'127.%';
+-----------------------------------------------------+
| Grants for nginx@127.%                              |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'nginx'@'127.%'               |
| GRANT PROXY ON 'web'@'127.0.0.1' TO 'nginx'@'127.%' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)

# 验证nginx用户是否有权限
mysql> SELECT USER();
+-----------------+
| USER()          |
+-----------------+
| nginx@localhost |
+-----------------+
1 row in set (0.00 sec)
# 可以顺利执行
mysql> SELECT * FROM user;