MariaDB 10.6にgrant文でユーザー作成と権限設定を行う

2022年1月9日

環境
Windows 10 64bit
MariaDB 10.6.4

1.grant文を使いユーザー作成と権限設定を行います
書式
GRANT 権限 ON データベース.テーブル名 TO 'ユーザ’@’ホスト’ identified by 'パスワード’;

使用例1
「testdb」の全テーブルに対する全ての権限を付与

MariaDB [testdb]> GRANT ALL ON testdb.* TO arkgame02@localhost identified by '12345#';
Query OK, 0 rows affected (0.023 sec)

作成したユーザーの権限を確認します

MariaDB [testdb]> show grants for arkgame02@localhost;
+------------------------------------------------------------------------------------------------------------------+
| Grants for arkgame02@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `arkgame02`@`localhost` IDENTIFIED BY PASSWORD '*AFF33F36F68159E68DBA95F22F0A346DD919E1C1' |
| GRANT ALL PRIVILEGES ON `testdb`.* TO `arkgame02`@`localhost`                                                    |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

使用例2
「testdb」の「user」テーブルに対するSELECT文、INSERT文、UPDATE文の実行権限を付与

MariaDB [testdb]> GRANT SELECT,INSERT,UPDATE ON testdb.user TO arkgame03@localhost identified by '1234#a';
Query OK, 0 rows affected (0.230 sec)

作成したユーザーの権限を確認します

MariaDB [testdb]> show grants for arkgame03@localhost;
+------------------------------------------------------------------------------------------------------------------+
| Grants for arkgame03@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `arkgame03`@`localhost` IDENTIFIED BY PASSWORD '*8E53676F5FF2EFBD24188D50135DF2B98FC77965' |
| GRANT SELECT, INSERT, UPDATE ON `testdb`.`user` TO `arkgame03`@`localhost`                                       |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.038 sec)