Mysql权限管理

03/19/2017 | | Post a Comment

注意:以下所有命令都是在mysql 5.7.17之后中实现的,之前的版本与此略有不同,还请参考其他文档。

一、创建/删除用户

创建用户:

mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass'; //创建用户的时候创建密码
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost' 
    ->     WITH GRANT OPTION;  //在所有主机上都可以登录,并赋所有的权限,也可以将自己所拥有的权限赋给其他用户
mysql> CREATE USER 'finley'@'%' IDENTIFIED BY 'some_pass'; // % 代表所有主机
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_pass';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> CREATE USER 'dummy'@'localhost';
mysql> CREATE USER [ IF NOT EXISTS ] 'username'@'hostname' [ IDENTIFIED BY 'password' ]
mysql> CREATE USER 'david'@'192.58.197.0/255.255.255.0';(子网掩码书写规范)

删除用户:

mysql> DROP USER 'username'@'hostname'

修改用户密码:

mysql> ALTER USER ‘username’@’hostname’ IDENTIFIED BY ‘new password’ [ PASSWORD EXPIRE default|never|N day ] //180 day 代表半年

eg:mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘nashsun123@’;

mysql> UPDATE user SET authentication_string=password(‘nashsun’) WHERE User=’root’;

注:hostname可以是特定的主机,如果不指明默认为%,代表所有主机。

(如果忘记密码:在/etc/my.cf的[mysqld]中加入skip-grant-tables,直接进入mysql利用UPDATE更改密码,注意下如果报这种错误,这个字段也需要改下password_expired:ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.)

二、登录mysql数据库

shell>mysql -u user_name -p password db_name

shell> mysql –user=user_name –password=your_password db_name

二、用户权限

查看用户权限:

mysql>SHOW GRANTS FOR username@hostname;

查看非用户权限的属性:

mysql> SHOW CREATE USER root@localhost;    //注意 CREATE 后面没有FOR

Mysql权限参数:

Privilege Column Context
CREATE Create_priv databases, tables, or indexes
DROP Drop_priv databases, tables, or views
GRANT OPTION Grant_priv databases, tables, or stored routines
LOCK TABLES Lock_tables_priv databases
REFERENCES References_priv databases or tables
EVENT Event_priv databases
ALTER Alter_priv tables
DELETE Delete_priv tables
INDEX Index_priv tables
INSERT Insert_priv tables or columns
SELECT Select_priv tables or columns
UPDATE Update_priv tables or columns
CREATE TEMPORARY TABLES Create_tmp_table_priv tables
TRIGGER Trigger_priv tables
CREATE VIEW Create_view_priv views
SHOW VIEW Show_view_priv views
ALTER ROUTINE Alter_routine_priv stored routines
CREATE ROUTINE Create_routine_priv stored routines
EXECUTE Execute_priv stored routines
FILE File_priv file access on server host
CREATE TABLESPACE Create_tablespace_priv server administration
CREATE USER Create_user_priv server administration
PROCESS Process_priv server administration
PROXY see proxies_priv table server administration
RELOAD Reload_priv server administration
REPLICATION CLIENT Repl_client_priv server administration
REPLICATION SLAVE Repl_slave_priv server administration
SHOW DATABASES Show_db_priv server administration
SHUTDOWN Shutdown_priv server administration
SUPER Super_priv server administration
ALL [PRIVILEGES] server administration
USAGE server administration

权限参数链接:https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

赋权语法:

GRANT priv_type [(column_list)]
              [, priv_type [(column_list)]] …
            ON [TABLE|FUNCTION|PROCEDURE] priv_level
            TO username@hostname [IDENTIFIED BY ‘password’], [username@hostname [],…]
            [REQUIRE SSL]
[WITH with_option …]

priv_type 就是上表中的各项

priv_level是指database_name.table_name

解除权限语法:

REVOKE
            priv_type [(column_list)]
              [, priv_type [(column_list)]] …
            ON [object_type] priv_level
            FROM user [, user] …

        REVOKE ALL PRIVILEGES, GRANT OPTION
            FROM user [, user] …

练习:

1、授予testuser能够通过172.16.0.0/16网络内的任意主机访问当前mysql服务器的权限;
mysql> CREATE USER testuser@’172.16.%.%’;
2、让此用户能够创建及删除testdb数据库,及库中的表;
mysql> GRANT create,drop ON testdb.* to testuser@’172.16.%.%’;
3、让此用户能够在testdb库中的t1表中执行查询、删除、更新和插入操作;
mysql> GRANT select,delete,update,insert ON testdb.t1 TO testuser@’172.16.%.%’;
4、让此用户能够在testdb库上执行创建和删除索引;
mysql> GRANT index ON testdb.* TO testuser@’172.16.%.%’;
5、让此用户能够在testdb.t2表上查询id和name字段,并允许其将此权限转授予其他用户;
mysql> GRANT select(id,name) ON testdb.t1 TO testuser@’172.16.%.%’ WITH GRANT OPTION;

实验所用msql 5.7.17 下载链接:

http://pan.baidu.com/s/1gf0yvKJ

此资源是个rpm包,现在之后请安装此rpm,会生成新的yum源,再用yum命令直接安装即可。



Want to say something? Post a comment

电子邮件地址不会被公开。 必填项已用*标注