记一次MySQL授权问题

1. 查看某个用户的权限：
   假设我有的MySQL数据库只有一个用户，名为root

列出所有用户:

select concat('user:''',user,'''@''',host,''',') as quary from mysql.user;

或者更直接的写法：

select user,host from mysql.user;

示例结果：

+--------------+-----------+
| user         | host      |
+--------------+-----------+
| root         | localhost |
+--------------+-----------+

查看某个用户的权限：

show grants for root@'localhost';

示例结果:

+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

2. 创建一个新用户，名为 myuser ， 密码为123456

create user 'myuser' identified by '123456';

3. 给予这个用户bd_json表的所有权限:

grant all privileges on my_db.bd_json to myuser@'%' identified by '123456';

4. 给予这个用户bd_data表的create update select权限

grant create,update,select on my_db.bd_data to myuser@'%' identified by '123456';

常用的权限有:

all privileges：所有权限。
select：读取权限。
delete：删除权限。
update：更新权限。
create：创建权限。
drop：删除数据库、数据表权限

5. 查看某个用户的权限

SHOW GRANTS FOR 'testuser' @'%';

6. 移除用户权限

REVOKE USAGE ON *.* FROM 'testuser'@'%';
REVOKE ALL PRIVILEGES ON `test_db`.* FROM 'testuser'@'%';

7. 授予用户在某个数据库上执行所有存储过程的权限

GRANT EXECUTE ON `test_db`.*  TO 'testuser'@'%';

8. 授予某个用户执行某个存储过程的权限

GRANT EXECUTE  ON  PROCEDURE `procedure_name` TO 'testuser'@'%'

9. 刷新权限

FLUSH PRIVILEGES;

10. all privileges包含哪些权限

grant all privileges on test.* to 'jeanron'@'%' identified by 'jeanron100';
show grants for jeanron;

+---------------------------------------------------+

| Grants for jeanron@%                              |

+---------------------------------------------------+

| GRANT USAGE ON *.* TO 'jeanron'@'%'               |

| GRANT ALL PRIVILEGES ON `test`.* TO 'jeanron'@'%' |

+---------------------------------------------------+

我们做一下收缩。

revoke insert on test.* from jeanron@'%';
show grants for jeanron;

| Grants for jeanron@%    

+------------------------------------------------------------------------------------------------------

| GRANT USAGE ON *.* TO 'jeanron'@'%'      

| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'jeanron'@'%' |

通过抽丝剥茧，从而显现其内在含义。

11. 对于存储过程

虽然授权普通用户创建、修改、执行权限，但任可能查看不了内容，其实是因为缺少了一个中间过程的查询权限，添加以下授权即可；

grant select on mysql.proc to xxxx@'xxxx'

CREATE ROUTINE 创建存储过程；
ALTER ROUTINE 修改存储过程；
EXCUTE 执行存储过程；

12. 特坑 DEFINER

DEFINER 指定存储过程的创建者，一般创建存储过程时不明文指出，默认取当前创建用户；
SQL SECURITY { DEFINER | INVOKER } ：指明谁有权限来执行。
DEFINER 表示按定义者拥有的权限来执行
INVOKER 表示用调用者的权限来执行，注意，如果通过INVOKER指定以普通用户权限执行，需要普通用户具有存储过程内容中访问的所有权限。默认情况下，系统指定为DEFINER
DEFINER=root@% 的存储过程，不能被普通用户修改，即使授权 ALTER ROUTINE。