mysql授权

grant语法

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]
    [AS user
        [WITH ROLE
            DEFAULT
          | NONE
          | ALL
          | ALL EXCEPT role [, role ] ...
          | role [, role ] ...
        ]
    ]
}

GRANT PROXY ON user_or_role
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]

GRANT role [, role] ...
    TO user_or_role [, user_or_role] ...
    [WITH ADMIN OPTION]

priv_type:权限类型
object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user_or_role: {
    user (see Section 6.2.4, “Specifying Account Names”)
  | role (see Section 6.2.5, “Specifying Role Names”)
}

基本语法

grant [权限] on [数据库名].[表名] to 'user_name'@'host_name' ....;

例子

授权db的所有权限给指定的账户
GRANT ALL ON db.* to 'user_name'@'localhost';

给指定的用户授予角色
GRANT 'role1','role2' to 'user_name'@'localhost','user_name2'@'localhost';

授权db数据库的查询权限给指定用户
GRANT SELECT,INSERT ON db.* to 'user_name'@'localhost';

给用户授权所有数据库的权限
GRANT ALL ON *.* to 'user_name'@'localhost';

把test数据库所有的表授权给localhost登录的所有用户
GRANT ALL ON test.* TO ''@'localhost' ...;

数据库权限

数据库权限存储在mysql.db表里面

查看表结构
desc mysql.db

查看所有用户的数据库权限
select * from mysql.db;

账户授权数据库权限,数据库权限包含表和过程的权限
GRANT ALL ON mydb.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

数据库对应的权限:
CREATE, DROP, EVENT, GRANT OPTION, LOCK TABLES, REFERENCES 

表权限

查看表权限mysql.tables_priv
select * from mysql.tables_priv

给表授权
GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

表权限:
 ALTER, CREATE VIEW, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE

列权限

列权限表mysql.columns_priv
select * from mysql.columns_priv;

列权限:
INSERT, REFERENCES, SELECT, and UPDATE

给列授权
GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'someuser'@'somehost';

存储权限

存储权限所在表
mysql.procs_priv

查询存储权限
select * from mysql.proces_priv;

权限范围
ALTER ROUTINE, EXECUTE, and GRANT OPTION

授予存储权限
GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

注意:存储权限不包含 CREATE ROUTINE;

用户代理权限

代理表
mysql.proxies_priv

查询代理用户
select * from proxy_priv

代理用户需要设置 with grant option 

授予代理用户 'localuser'@'localhost' 代理 'externaluser'@'somehost'
GRANT PROXY ON 'localuser'@'localhost' TO 'externaluser'@'somehost';

角色权限

授予角色权限
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';

其它特性

授予用户可以授权的操作
USAGE只能登录没有任何权限
WITH GRANT OPTION 给用户设置可以授权的权限
GRANT USAGE ON *.* TO 'someuser'@'somehost' WITH GRANT OPTION;

查看权限列表

show privileges;

查询系统表里面用户的访问权限
select User,Host from mysql.user;

查询当前登录的用户和地址
select current_user();

授权以后必须刷新才能立即生效

FLUSH PRIVILEGES