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

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

 

權限設定的指令結構如下(MariaDB 10.2):

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. 給予使用者 myaccount@localhost 全域最高權限:

GRANT ALL PRIVILEGES ON *.* TO 'myaccount'@'localhost' WITH GRANT OPTION;

 

2. 給予使用者 myaccount@localhost 存取資料庫 mydb 的所有權限(不含管理權限):

GRANT ALL PRIVILEGES ON `mydb`.* TO 'myaccount'@'localhost';

 

3. 給予 myaccount@localhost 存取資料庫 mydbSELECTINSERTUPDATEDELETE 權限:

GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO  'myaccount'@'localhost';

 

4. 移除使用者 myaccount@localhost 在資料庫 mydb 的所有權限(含管理權限):

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

 

5. 修改使用者 myaccount@localhost 在資料庫 mydb 僅能執行 SELECT(具體作法為先移除所有權限後再 GRANT 指定權限):

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

 

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

 


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

留言