Mysql如何創(chuàng)建用戶、權(quán)限及如何重置管理員密碼

下文主要給大家?guī)?a title="Mysql" target="_blank" href="http://kemok4.com/mysql/">Mysql如何創(chuàng)建用戶、權(quán)限及如何重置管理員密碼，希望Mysql如何創(chuàng)建用戶、權(quán)限及如何重置管理員密碼能夠帶給大家實(shí)際用處，這也是我編輯這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。

一、簡介

由MySQL AB公司開發(fā)，是最流行的開放源碼SQL數(shù)據(jù)庫管理系統(tǒng)，主要特點(diǎn)：

• 1、是一種數(shù)據(jù)庫管理系統(tǒng)

• 2、是一種關(guān)聯(lián)數(shù)據(jù)庫管理系統(tǒng)

• 3、是一種開放源碼軟件，且有大量可用的共享MySQL軟件

• 4、MySQL數(shù)據(jù)庫云服務(wù)器具有快速、可靠和易于使用的特點(diǎn)

• 5、MySQL云服務(wù)器工作在客戶端/云服務(wù)器模式下，或嵌入式系統(tǒng)中

• 
  

• InnoDB存儲引擎將InnoDB表保存在一個(gè)表空間內(nèi)，該表空間可由數(shù)個(gè)文件創(chuàng)建。這樣，表的大小就能超過單獨(dú)文件的最大容量。表空間可包括原始磁盤分區(qū)，從而使得很大的表成為可能。表空間的最大容量為64TB。

• 
  

二、MySQL用戶賬號、權(quán)限管理     

2.1. MySQL用戶賬號包括：用戶名@主機(jī)名

   用戶名：16個(gè)字符以內(nèi)

   主機(jī)有以下幾種表現(xiàn)方式

      主機(jī)名： mysql, www.magedu.com

      IP地址: 172.16.90.111

      網(wǎng)絡(luò)地址：172.16.0.0/255.255.0.0

      通配符：%，_   

        %：任意字符   172.16.%.%   ,%.magedu.com 

        _：任意一個(gè)   172.16_.%.%  

2.2. 權(quán)限級別： 全局級別、 庫級別、 表級別、 列級別、 存儲過程 和存儲函數(shù)級別

   全局級別：SELECT * FROM db\G;     查詢?nèi)謳旒墑e的權(quán)限

mysql> SELECT * FROM db \G

*************************** 1. row ***************************

          Host: %

           Db: test

          User: 

        Select_priv: Y

        Insert_priv: Y

        Update_priv: Y

        Delete_priv: Y

        Create_priv: Y

        Drop_priv: Y

        Grant_priv: N

      References_priv: Y

        Index_priv: Y

        Alter_priv: Y

Create_tmp_table_priv: Y

    Lock_tables_priv: Y

    Create_view_priv: Y

     Show_view_priv: Y

  Create_routine_priv: Y

  Alter_routine_priv: N

      Execute_priv: N

      Event_priv: Y

      Trigger_priv: Y

**************************************************************************

CREATE USER username@host [IDENTIFIED BY 'password']   創(chuàng)建用戶

DROP USER 'username'@'host';     刪除用戶

RENAME USER old_name TO new_name;  重命名用戶

SHOW GRANTS FOR 'username'@'host'; 查看用戶權(quán)限列表

FLUSH PRIVILEGES ;           刷新權(quán)限列表

GRANT PRIVILEGES ON [object_type] db.* TO 'username'@'host'; 給用戶增加權(quán)限

REVOKE SELECT ON db.* FROM 'username'@'host';    取消用戶的SELECT權(quán)限

2.3. 權(quán)限對應(yīng)的作用范圍明細(xì)：

2.4. 創(chuàng)建用戶及權(quán)限：

  2.4.1. 創(chuàng)建用戶，并通過IDENTIFIED BY 'password'，設(shè)定密碼

   CREATE USER username@host [IDENTIFIED BY 'password']

Usage:

mysql> CREATE USER test@localhost IDENTIFIED BY 'test';   #創(chuàng)建用戶test本地?cái)?shù)據(jù)庫賬號，密碼test。

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES ;           #刷新權(quán)限列表

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR test@localhost\G;   #查看數(shù)據(jù)庫賬戶test@localhost的權(quán)限列表

*************************** 1. row ***************************

Grants for test@localhost: GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'

1 row in set (0.00 sec)

  2.4.2. 測試登錄

重新打開另一個(gè)客戶端，登錄以test賬號登錄mysql

[root@lamp ~]# mysql -utest -p  #登錄mysql數(shù)據(jù)庫以test用戶，輸入用戶密碼

mysql> SHOW DATABASES;  #登錄成功，查看該賬號下的數(shù)據(jù)庫

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

| Database   |

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

| information_schema |

| test     |

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

2 rows in set (0.00 sec)

  2.4.3. 權(quán)限設(shè)定GRANT

GRANT PRIVILEGES ON [object_type] db.* TO 'username'@'host'; 

#指定權(quán)限PRIVILEGES

 ON指定對象名稱db.* 

object_type指定對象類型:

    TABLE   表

  | FUNCTION  函數(shù)

  | PROCEDURE 

 TO username@host 指定用戶。

with_option:

    GRANT OPTION

  | MAX_QUERIES_PER_HOUR count

  | MAX_UPDATES_PER_HOUR count

  | MAX_CONNECTIONS_PER_HOUR count

  | MAX_USER_CONNECTIONS count

object_type對象類型有：TABLE（表） FUNCTION（函數(shù)） PROCEDURE(程序、庫)

Usage：GRANT EXECUTE ON FUNCTION db.abc TO 'username'@'host'; #授權(quán)給username@host用戶,對

db數(shù)據(jù)庫的abc函數(shù)有執(zhí)行權(quán)限。

  GRANT UPDATE(Age) ON db.testtb TO 'username'@'host'; #授權(quán)給username@host用戶對db數(shù)據(jù)庫的，testtb表的Age字段具有UPDATE權(quán)限。

  2.4.3.1.測試開通創(chuàng)建庫權(quán)限 

A連接：

mysql> GRANT CREATE ON test01.* TO 'test'@'%';

Query OK, 0 rows affected (0.01 sec)

B連接：（A連接命令執(zhí)行前）

mysql> CREATE DATABASE test01;

ERROR 1007 (HY000): Can't create database 'test'; database exists

B連接：（A連接命令執(zhí)行后）

mysql> CREATE DATABASE test01;

Query OK, 1 row affected (0.00 sec)

mysql> use test01

Database changed

mysql> CREATE TABLE testdb (ID INT UNSINED AUTO_INCREMENT NOT NULL,Name CHAR(20),PRIMARY KEY (ID));

Query OK, 0 rows affected (0.02 sec)

  2.4.3.2.測試開通創(chuàng)建表權(quán)限，權(quán)限設(shè)定后需重連

A連接：

mysql> GRANT INSERT ON test01.* TO 'test'@'%';

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT ON test01.* TO 'test'@'%';

Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR 'test'@'%';  #查看權(quán)限正常

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

| Grants for test@%           |

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

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

| GRANT CREATE ON `test`.* TO 'test'@'%'     |

| GRANT SELECT, INSERT, CREATE ON `test01`.* TO 'test'@'%' |

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

3 rows in set (0.00 sec)

[root@lamp ~]# tail /mydata/data/lamp.err   #查看日志正常

170612  9:31:34 InnoDB: Completed initialization of buffer pool

170612  9:31:34 InnoDB: highest supported file format is Barracuda.

170612  9:31:35 InnoDB: Waiting for the background threads to start

170612  9:31:36 InnoDB: 1.1.8 started; log sequence number 5241255

170612  9:31:36 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306

170612  9:31:36 [Note] - '0.0.0.0' resolves to '0.0.0.0';

170612  9:31:36 [Note] Server socket created on IP: '0.0.0.0'.

170612  9:31:36 [Note] Event Scheduler: Loaded 0 events

170612  9:31:36 [Note] /usr/local/mysql/bin/mysqld: ready for connections.

Version: '5.5.28-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution

B連接：（A連接命令執(zhí)行前）

mysql> USE test01

Database changed

mysql> INSERT INTO testdb (Name) VALUES ('TOM');

ERROR 1142 (42000): INSERT command denied to user 'test'@'localhost' for table 'testtb'

B連接：（A連接命令執(zhí)行且重新連接會話后）

mysql> use test01

Database changed

mysql> INSERT INTO testdb (Name) VALUES ('TOM');

Query OK, 1 row affected (0.01 sec)

  2.4.3.3.測試開通新增表中字段的權(quán)限，權(quán)限設(shè)定后需重連

A連接：

mysql> GRANT ALTER ON test01.* TO 'test'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

B連接：（A連接命令執(zhí)行前）

mysql> ALTER TABLE testdb ADD Age TINYINT UNSIGNED;

ERROR 1142 (42000): ALTER command denied to user 'test'@'localhost' for table 'testdb'

B連接：（A連接命令執(zhí)行且重新連接會話后）

mysql> use test01;

Database changed

mysql> ALTER TABLE testdb ADD Age TINYINT UNSIGNED;

Query OK, 1 row affected (0.03 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM testdb;

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

| ID | Name | Age |

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

| 1 | TOM | NULL |

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

1 row in set (0.00 sec)

  2.4.3.4.測試開通新增表中字段的權(quán)限，權(quán)限設(shè)定后需重連

A連接：

mysql> GRANT UPDATE(Age) ON test01.testdb TO 'test'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'test'@'%';

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

| Grants for test@%         |

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

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

| GRANT CREATE ON `test`.* TO 'test'@'%'      |

| GRANT SELECT, INSERT, CREATE, ALTER ON `test01`.* TO 'test'@'%' |

| GRANT UPDATE (Age) ON `test01`.`testdb` TO 'test'@'%'  |

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

4 rows in set (0.00 sec)

B連接：（A連接命令執(zhí)行且重新連接會話后）

mysql> use test01;

mysql> UPDATE testdb SET Age=30 WHERE ID=1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM testdb;

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

| ID | Name | Age |

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

| 1 | TOM | 30 |

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

1 row in set (0.00 sec)

mysql> UPDATE testdb SET Name='Jack' WHERE ID=1;  #只有更新Age的權(quán)限，所以出錯

ERROR 1143 (42000): UPDATE command denied to user 'test'@'localhost' for column 'Name' in table 'testdb'

  2.4.3.5.測試開通全局的權(quán)限，權(quán)限設(shè)定后需重連  XXX測試失敗

A連接：

mysql> SET GLOBAL tx_isolation='READ-UNCOMMITTED';

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

mysql> show grants for 'test'@'%';

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

| Grants for test@%      | 

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

| GRANT SUPER ON *.* TO 'test'@'%'       |

| GRANT CREATE ON `test`.* TO 'test'@'%'   |

| GRANT SELECT, INSERT, CREATE, ALTER ON `test01`.* TO 'test'@'%' |

| GRANT UPDATE (Age) ON `test01`.`testdb` TO 'test'@'%' |

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

4 rows in set (0.00 sec)

B連接：（A連接命令執(zhí)行且重新連接會話后）

mysql> use test01;

mysql> set GLOBAL tx_isolation = 'READ-UNCOMMITTED';

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

mysql>

  2.4.3.6.測試撤銷授權(quán)

mysql> help revoke

Name: 'REVOKE'

Syntax:

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] ...

REVOKE PROXY ON user

    FROM user [, user] ...

mysql> show grants for 'test'@'%';

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

| Grants for test@%           |

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

| GRANT SUPER ON *.* TO 'test'@'%'    |

| GRANT CREATE ON `test`.* TO 'test'@'%'   |

| GRANT SELECT, INSERT, CREATE, ALTER ON `test01`.* TO 'test'@'%' |

| GRANT UPDATE (Age) ON `test01`.`testdb` TO 'test'@'%'    |

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

4 rows in set (0.00 sec)

mysql> revoke SELECT ON test.* FROM 'test'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'test'@'%';

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

| Grants for test@%               |

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

| GRANT SUPER ON *.* TO 'test'@'%'       |

| GRANT CREATE ON `test`.* TO 'test'@'%'      |

| GRANT SELECT, INSERT, CREATE, ALTER ON `test01`.* TO 'test'@'%' |

| GRANT UPDATE (Age) ON `test01`.`testdb` TO 'test'@'%'   |

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

4 rows in set (0.00 sec)

  2.4.3.7.測試開通SSL連接的權(quán)限，權(quán)限設(shè)定后需重連

  2.4.3.8.測試開通其它的權(quán)限，權(quán)限設(shè)定后需重連

mysql> help drop user   #刪除用戶

Name: 'DROP USER'

Syntax:

DROP USER user [, user] ...

mysql> help rename user   #用戶重命名

Name: 'RENAME USER'

Syntax:

RENAME USER old_user TO new_user

    [, old_user TO new_user] ...

2.5. MySQL數(shù)據(jù)庫ROOT用戶密碼忘記解決方案步驟：

  2.3.1. 先關(guān)閉mysqld進(jìn)程，并修改配置文件/etc/my.cnf

[root@lamp ~]# service mysqld stop  #先停止mysqld進(jìn)程

Shutting down MySQL..       [  OK  ]  

[root@lamp ~]# vim /etc/init.d/mysqld #修改mysqld的啟動腳本，修改內(nèi)容如下紅色框內(nèi)

查找start:   /start

新增跳過權(quán)限表及禁止網(wǎng)絡(luò)登錄： --skip-grant-tables --skip-networking

  2.3.2. 啟動mysqld服務(wù)，并登入修改mysql.user中的密碼

[root@lamp ~]# service mysqld start  #啟動mysqld進(jìn)程

Starting MySQL..               [  OK  ] 

[root@lamp ~]# mysql          #此時(shí)登錄mysql即可不需要用戶名和密碼

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

Your MySQL connection id is 1

Server version: 5.5.28-log Source distribution

Copyright (c) 2000, 2012, 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> USE mysql

Database changed

mysql> SELECT User,Host,Password FROM user; #查詢user表的三個(gè)字段，是需要密碼登錄的

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

| User | Host      | Password       |

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

| root | localhost | *A198E6EEE923DA319BBF86C99624479A198E6EEE9 |

| root | lamp   | *A198E6EEE9823DA319BBF86C99624479A198E6EEE9 |

| root | 127.0.0.1 | *A198E6EEE9DA319BBF86C99624479A198E6EEE9 |

| root | ::1   | *A198E6EEE93DA319BBF86C99624479A198E6EEE9 |

| test | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |

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

5 rows in set (0.00 sec)

mysql> UPDATE user SET Password=PASSWORD('redhat') WHERE User='root'; 

#此時(shí)由于跳過了grant權(quán)限列表，所以只能通過修改user表的Password字段的值來修改用戶密碼。

Query OK, 0 rows affected (0.00 sec)

Rows matched: 4  Changed: 0  Warnings: 0

mysql> SELECT User,Host,Password FROM user;  #查詢user表的三個(gè)字段，是需要密碼登錄的

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

| User | Host  | Password   |

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

| root | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| root | lamp  | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| root | 127.0.0.1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| root | ::1   | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| test | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |

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

5 rows in set (0.00 sec)

mysql>\q

  2.3.3. 關(guān)閉mysqld服務(wù)，并登入刪除之前增加的安全啟動參數(shù)

[root@lamp ~]# service mysqld stop  #停止mysqld進(jìn)程

Shutting down MySQL.                              [  OK  ]

[root@lamp ~]# vim /etc/init.d/mysqld #修改啟動腳本，把之前修改的內(nèi)容去掉后保存退出

  2.3.4. 啟動mysqld服務(wù)，并使用修改后的密碼登錄

[root@lamp ~]# service mysqld start #啟動mysqld進(jìn)程

Starting MySQL..           [  OK  ]

[root@lamp ~]# mysql        #此時(shí)直接登錄mysql提示輸入用戶及密碼

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@lamp ~]# mysql -uroot -p   #指定通過root賬號登錄 -p指定需要輸入密碼登錄

Enter password:      

對于以上關(guān)于Mysql如何創(chuàng)建用戶、權(quán)限及如何重置管理員密碼，大家是不是覺得非常有幫助。如果需要了解更多內(nèi)容，請繼續(xù)關(guān)注我們的行業(yè)資訊，相信你會喜歡上這些內(nèi)容的。