mysql使用者管理及授權管理

標籤：webseven   mysql授權   mysql使用者   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 [email protected]‘%‘ identified by ‘mytest‘;  //方式自動通知mysql讀取授權表.

2.grant all on mysql.* to [email protected]‘%‘ 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 [email protected];

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 [email protected];

Query OK, 0 rows affected (0.00 sec)

mysql> grant alter on mytest.* to [email protected];

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 [email protected]‘%‘ identified by "mytest";

Query OK, 0 rows affected (0.07 sec)

mysql> show grants for [email protected]‘%‘;

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

| Grants for [email protected]%                                                                                   |

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

| 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 [email protected]‘%‘;

grant all on function db.mytest to [email protected]‘%‘;

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 [email protected];

Query OK, 0 rows affected (0.00 sec)

授權超級使用者權限:

mysql> grant super on *.* to [email protected];

Query OK, 0 rows affected (0.00 sec)

收回許可權:

mysql> show grants for [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 [email protected]主機名稱 to [email protected]主機名稱;

mysql> rename user [email protected] to [email protected];

Query OK, 0 rows affected (0.00 sec)

初始化mysql密碼:

通過設定--skip-grant-tables和--skip-networking,重啟mysqld服務:

[[email protected] ~]# service mysqld stop

Shutting down MySQL... SUCCESS! 

[[email protected] ~]# 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服務:

[[email protected] ~]# 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

[[email protected] ~]# 

[[email protected] ~]# 

[[email protected] ~]# 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 [email protected]‘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 ‘[email protected]‘localhost‘=password(‘123‘)‘ at line 1

本文出自 “webseven” 部落格，請務必保留此出處http://webseven.blog.51cto.com/4388012/1535124