Database MariaDB MySQL SQL

[MariaDB/MySQL] 建立連線帳號與指派權限

建立帳號，其中「myaccount」是帳號，「mypassword」是密碼，主機為 localhost。

1

CREATE USER 'myaccount'@'localhost' IDENTIFIED BY 'mypassword';

權限設定的指令結構如下（MariaDB 10.2）：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user  [IDENTIFIED [BY [PASSWORD] 'password']
        |{VIA|WITH} plugin_name 
           [{USING|AS} 'plugin_option']]
        [, user [IDENTIFIED [BY [PASSWORD] 'password']
        |{VIA|WITH} plugin_name] 
           [{USING|AS} 'plugin_option']]
    user_options...

GRANT PROXY ON user_specification
    TO user_specification [, user_specification] ...
    [WITH GRANT OPTION]

user_options:
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH with_option [with_option] ...]

object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
  | MAX_STATEMENT_TIME time

tls_option:
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

這裡列出五種較常用的權限設定指令：

  1. 給予使用者 code>myaccount@localhost 全域最高權限：

[```
GRANT ALL PRIVILEGES ON . TO ‘myaccount’@‘localhost’ WITH GRANT OPTION;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27


[2. 給予使用者](mailto:code>myaccount@localhost</code) [code>myaccount@localhost 存取資料庫 `mydb` 的所有權限（不含管理權限）：](mailto:code>myaccount@localhost</code)

[```
GRANT ALL PRIVILEGES ON mydb.* TO 'myaccount'@'localhost';
```](mailto:code>myaccount@localhost</code) 

[](mailto:code>myaccount@localhost</code)4. [給予](mailto:code>myaccount@localhost</code) [code>myaccount@localhost 存取資料庫 `mydb` 的 `SELECT`、`INSERT`、`UPDATE`、`DELETE` 權限：](mailto:code>myaccount@localhost</code)
[](mailto:code>myaccount@localhost</code)

[```
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO  'myaccount'@'localhost';
```](mailto:code>myaccount@localhost</code) 

[](mailto:code>myaccount@localhost</code)5. [移除使用者](mailto:code>myaccount@localhost</code) [code>myaccount@localhost 在資料庫 `mydb` 的所有權限（含管理權限）：](mailto:code>myaccount@localhost</code)
[](mailto:code>myaccount@localhost</code)

[```
REVOKE ALL PRIVILEGES ON mydb.* FROM 'myaccount'@'localhost' WITH GRANT OPTION;
```](mailto:code>myaccount@localhost</code) 

[](mailto:code>myaccount@localhost</code)6. [修改使用者](mailto:code>myaccount@localhost</code) [code>myaccount@localhost 在資料庫 `mydb` 僅能執行 `SELECT`（具體作法為先移除所有權限後再 GRANT 指定權限）：](mailto:code>myaccount@localhost</code)
[](mailto:code>myaccount@localhost</code)

[```
REVOKE ALL PRIVILEGES ON mydb.* FROM 'myaccount'@'localhost' WITH GRANT OPTION;
GRANT SELECT ON mydb.* TO 'myaccount'@'localhost';

權限修改完畢後，記得輸入 FLUSH PRIVILEGES; 重新載入權限，剛剛所做的異動才會生效。

    • *](mailto:code>myaccount@localhost</code)

References: CREATE USER - MariaDB Knowledge Base GRANT - MariaDB Knowledge Base

comments powered by Disqus