MySQL 新增使用者與權限設定 (筆記)

MySql 新增使用者與設定權限

每次要在 MySQL 新稱使用者總是用一次查一次，我來記錄一下好了。要新增使用者與賦予權限的方式如下：

先使用 root 進入 MySQL

mysql -uroot -p

接下來我們新增一個使用者 killme, 密碼設定為 killpwd, 並且給予資料庫的所有權限

use mysql;

INSERT INTO user(host,user,password) VALUES('%','killme',password('killpwd'));

GRANT ALL ON *.* TO 'killme'@localhost IDENTIFIED BY 'killpwd' WITH GRANT OPTION;

FLUSH PRIVILEGES;

如果要給予使用者特定資料庫的操作權限可以這麼做：

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE ON dbname.* TO 'killme'@localhost IDENTIFIED BY 'killpwd';

執行過程如下:

[root@server htdocs]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53481
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into user (host,user,password) values('%','killme',password('killpwd'));
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> GRANT ALL ON *.* TO 'killme'@localhost IDENTIFIED BY 'killpwd' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>