mysql用户管理及授权管理

mysql用户和权限管理

mysqld进程在启动的时候,将table,host,db,table_privs,clumn_privs,procs_privs,proxies_privs载入内存.

用户账号:

用户名+主机

用户名:16字符以内.

主机:

主机名: mytest

IP地址:172.168.1.20

网络地址:172.168.1.0/255.255.0.0

通配符:

172.168.%.%

172.168.1.2__

%.qq.com,注如果是域名,则mysql需要进行解析,如果取消解析,可以在启动mysqld服务时,增加参数--skip-name-resolve.

权限分以下几种:

1.全局级别.

2.库级别.

3.表级别

4.列级别.

5.存储过程和存储函数.

创建用户:

1.create user mytest@‘%‘ identified by ‘mytest‘;  //方式自动通知mysql读取授权表.

2.grant all on mysql.* to mytest@‘%‘ identified by "mytest";

3.insert into mysql.user,该方式需要执行flush privlieges,触发mysql重新读取用户权限信息.

mysql> create database mytest;

ERROR 1044 (42000): Access denied for user ‘mytest‘@‘localhost‘ to database ‘mytest‘(未授权前)

---------------------------------------------------------

在另外一个窗口给用户授权.

mysql> grant create on mytest.* to mytest@localhost;

Query OK, 0 rows affected (0.00 sec)

---------------------------------------------------------

mysql> create database mytest;

Query OK, 1 row affected (0.01 sec)

mysql> use mytest;

Database changed

mysql> create table mytab1(id int not null auto_increment  primary key,name char(20));

Query OK, 0 rows affected (0.03 sec)

---------------------------------------------------------

在另外一个窗口给用户授权.

mysql> grant select,insert on mytest.* to mytest@localhost;

Query OK, 0 rows affected (0.00 sec)

mysql> grant alter on mytest.* to mytest@localhost;

Query OK, 0 rows affected (0.00 sec)

---------------------------------------------------------

重新登录后,即可对表执行insert与update.

mysql> alter table mytab1 add age tinyint;

ERROR 1142 (42000): ALTER command denied to user ‘mytest‘@‘localhost‘ for table ‘mytab1‘

mysql> \r

Connection id:    22

Current database: mytest

mysql> alter table mytab1 add age tinyint;

Query OK, 3 rows affected (0.02 sec)

Records: 3  Duplicates: 0  Warnings: 0

查看授权信息:

mysql> grant all on mysql.* to mytest@‘%‘ identified by "mytest";

Query OK, 0 rows affected (0.07 sec)

mysql> show grants for mytest@‘%‘;

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

| Grants for mytest@%                                                                                   |

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

| GRANT USAGE ON *.* TO ‘mytest‘@‘%‘ IDENTIFIED BY PASSWORD ‘*58F4612C3598D20A3C51A37D7B2643BF15806832‘ |

| GRANT ALL PRIVILEGES ON `mysql`.* TO ‘mytest‘@‘%‘                                                     |

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

2 rows in set (0.00 sec)

grant all on table db.mytest to mytest@‘%‘;

grant all on function db.mytest to mytest@‘%‘;

with_option:

GRANT OPTION

 | MAX_QUERIES_PER_HOUR count  //每小时最多查询次数,0表示无限制.

 | MAX_UPDATES_PER_HOUR count //每小时最多更新次数,0表示无限制.

 | MAX_CONNECTIONS_PER_HOUR count //每小时最多连接次数,0表示无限制.

 | MAX_USER_CONNECTIONS count //最大用户同时连接数,0表示无限制.

给字段授权:

mysql> grant update(age) on mytest.mytab1 to mytest@localhost;

Query OK, 0 rows affected (0.00 sec)

授权超级用户权限:

mysql> grant super on *.* to mytest@localhost;

Query OK, 0 rows affected (0.00 sec)

收回权限:

mysql> show grants for [email protected];

[email protected]-----------------------------------------------+

| Grants for [email protected]                                                                                   |

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

| GRANT SUPER ON *.* TO ‘mytest‘@‘127.0.0.1‘ IDENTIFIED BY PASSWORD ‘*58F4612C3598D20A3C51A37D7B2643BF15806832‘ |

| GRANT SELECT, INSERT, CREATE, ALTER ON `mytest`.* TO ‘mytest‘@‘127.0.0.1‘                                     |

| GRANT UPDATE (age) ON `mytest`.`mytab1` TO ‘mytest‘@‘127.0.0.1‘                                               |

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

3 rows in set (0.00 sec)

mysql> revoke select on mytest.* from [email protected];

Query OK, 0 rows affected (0.00 sec)

删除用户:

drop user 用户名@主机.

重命名用户:

rename user new@主机名 to old_name@主机名;

mysql> rename user mytest@localhost to [email protected];

Query OK, 0 rows affected (0.00 sec)

初始化mysql密码:

通过设置--skip-grant-tables和--skip-networking,重启mysqld服务:

[root@idc131 ~]# service mysqld stop

Shutting down MySQL... SUCCESS! 

[root@idc131 ~]# mysqld_safe  --skip-grant-tables --skip-networking --user=mysql &

修改所有用户密码:

mysql> update mysql.user set password=password(‘mysql‘);

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4  Changed: 4  Warnings: 0

重启mysql服务:

[root@idc131 ~]# service mysqld restart

Shutting down MySQL.140803 23:09:12 mysqld_safe mysqld from pid file /usr/local/mysql/data/idc131.pid ended

SUCCESS! 

Starting MySQL.. SUCCESS! 

[1]+  Done                    mysqld_safe --skip-grant-tables --skip-networking --user=mysql

[root@idc131 ~]# 

[root@idc131 ~]# 

[root@idc131 ~]# mysql -uroot -p 

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> 

注意:此次因服务启动时,未读取授权表,故无法通过以下方式修改密码:

mysql> set password root@‘localhost‘=password(‘123‘);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘root@‘localhost‘=password(‘123‘)‘ at line 1

本文出自 “webseven” 博客，请务必保留此出处http://webseven.blog.51cto.com/4388012/1535124

mysql用户管理及授权管理,古老的榕树,5-wow.com