「MySQL 8.0.29」データベースレベルでCREATE 権限を設定する方法
環境
MySQL 8.0.29
Ubuntu 22.04 LTS
データベースレベルの権限を設定する手順
1.ユーザーを作成します
mysql> create user user008@localhost identified by 'user008';
Query OK, 0 rows affected (0.15 sec)
mysql> create user user008@localhost identified by 'user008';
Query OK, 0 rows affected (0.15 sec)
mysql> create user user008@localhost identified by 'user008'; Query OK, 0 rows affected (0.15 sec)
2.データベースレベルでCREATE 権限を設定します
mysql> grant create on testdb.* to user008@localhost;
Query OK, 0 rows affected (0.08 sec)
mysql> exit
Bye
mysql> grant create on testdb.* to user008@localhost;
Query OK, 0 rows affected (0.08 sec)
mysql> exit
Bye
mysql> grant create on testdb.* to user008@localhost; Query OK, 0 rows affected (0.08 sec) mysql> exit Bye
3.コマンドプロンプトから user008@localhost ユーザーで MySQL に接続します
# mysql -u user008 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.29-0ubuntu0.22.04.2 (Ubuntu)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use testdb;
Database changed
# mysql -u user008 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.29-0ubuntu0.22.04.2 (Ubuntu)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use testdb;
Database changed
# mysql -u user008 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.29-0ubuntu0.22.04.2 (Ubuntu) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use testdb; Database changed
4.テーブルを作成します
mysql> create table memtbl (mid int, mname varchar(20));
Query OK, 0 rows affected (2.34 sec)
mysql> create table memtbl (mid int, mname varchar(20));
Query OK, 0 rows affected (2.34 sec)
mysql> create table memtbl (mid int, mname varchar(20)); Query OK, 0 rows affected (2.34 sec)