[mysql]權限設定(包含遠端存取)

IT_man · 發表於 2014-12-31 08:01:47

本帖最後由 IT_man 於 2014-12-31 09:25 編輯

【說明】
MySQL資料庫授權有分成本機及遠端，例如，一個帳號綁定給localhost的話，那該帳號只能在MySQL資料庫本機使用，要開放給遠端主機連線到MySQL資料庫主機的話，那就必須將帳號綁定給遠端主機使用，下方介紹方式都是授權或刪除所有權限，MySQL權限有很多可以參考下列:

資料庫(DateBase)十五種權限:

ALL PRIVILEGES、ALTER、CREATE、DELETE、DROP、FILE、INDEX、INSERT、PROCESS、REFERENCES、RELOAD、SELECT、SHUTDOWN、UPDATE、USAGE

資料表(Table)八種權限:

SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、INDEX、ALTER

資料欄(column)三種權限:

SELECT INSERT UPDATE

【實例:】

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.52 Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> grant all privileges on *.* to test@localhost identified by '1234567'; //第一個星號是database，第二個星號是table

Query OK, 0 rows affected (0.00 sec)    //建立test帳號本機權限

mysql> grant all privileges on *.* to [email protected] identified by '1234567';

Query OK, 0 rows affected (0.01 sec) //建立test帳號遠端權限

mysql> select host,user from mysql.user; //查詢MySQL所有授權帳號

+-----------------------+-------+
| host                | user  |
+-----------------------+-------+
| 127.0.0.1          | root  |
| 192.168.1.2          | test  |    //帳號test遠端授權
| localhost          |    |
| localhost          | test  |       //帳號test本機授權
| localhost          | root  |
| localhost.localdomain |    |
| localhost.localdomain | root  |
+-----------------------+-------+
7 rows in set (0.00 sec)

mysql> quit //離開MySQL
Bye
[root@localhost ~]

p.s 其他mysql 重要語法請參考http://mail.hmes.kh.edu.tw/~jona/redhat/mysqlphp/mysqlsyntax.htm

		自動登錄	找回密碼
密碼			立即註冊

[網頁伺服器] [mysql]權限設定(包含遠端存取)