建立用戶

改用 mycli 登入 mariadb 資料庫，命令提示列包含用戶、主機名稱、目前使用的資料庫名稱，還沒有指定使用資料庫，所以顯示 none。

[root@kvm3 ~]# mycli -uroot -p123123
Connecting to socket /var/lib/mysql/mysql.sock, owned by user mysql
MariaDB 10.11.11
mycli 1.39.1
Home: http://mycli.net
Bug tracker: https://github.com/dbcli/mycli/issues
Thanks to the contributor - Open Query Pty Ltd
MariaDB root@(none):(none)>

使用名為 mysql 的資料庫。

MariaDB root@(none):(none)> use mysql;
You are now connected to database "mysql" as user "root"
Time: 0.001s
MariaDB root@(none):mysql>

顯示資料庫 mysql 中的資料表，其中 user 儲存用戶的表格。

MariaDB root@(none):mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| global_priv               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| help_topic                |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+

31 rows in set
Time: 0.026s

列出資料表 user 的所有欄位，其中增加新用戶最常或必須設定的欄位為前 6 項。

MariaDB root@(none):mysql> select column_name from information_schema.columns whe
                        -> re table_name='user';
+------------------------+
| column_name            |
+------------------------+
| Host                   |
| User                   |
| Password               |
| Select_priv            |
| Insert_priv            |
| Update_priv            |
| Delete_priv            |
| Create_priv            |
| Drop_priv              |
| Reload_priv            |
| Shutdown_priv          |
| Process_priv           |
| File_priv              |
| Grant_priv             |
| References_priv        |
| Index_priv             |
| Alter_priv             |
| Show_db_priv           |
| Super_priv             |
| Create_tmp_table_priv  |
| Lock_tables_priv       |
| Execute_priv           |
| Repl_slave_priv        |
| Repl_client_priv       |
| Create_view_priv       |
| Show_view_priv         |
| Create_routine_priv    |
| Alter_routine_priv     |
| Create_user_priv       |
| Event_priv             |
| Trigger_priv           |
| Create_tablespace_priv |
| Delete_history_priv    |
| ssl_type               |
| ssl_cipher             |
| x509_issuer            |
| x509_subject           |
| max_questions          |
| max_updates            |
| max_connections        |
| max_user_connections   |
| plugin                 |
| authentication_string  |
| password_expired       |
| is_role                |
| default_role           |
| max_statement_time     |
+------------------------+

47 rows in set
Time: 0.039s

新增一筆用戶名稱為 dywang，密碼 123qwe，此用戶只能由本機登入。

MariaDB root@(none):mysql> create user 'dywang'@localhost identified by '123qwe';
                        ->
Query OK, 0 rows affected
Time: 0.083s

查看 mysql 資料庫的 user 資料表，新增了用戶 dywang。

MariaDB root@(none):mysql> select user from user;
+-------------+
| User        |
+-------------+
| dywang      |
| mariadb.sys |
| mysql       |
| root        |
+-------------+

4 rows in set
Time: 0.036s

查看用戶 dywang，沒有給存取資料庫的權限。

MariaDB root@(none):mysql> select * from user where user='dywang'\G
***************************[ 1. row ]***************************
Host                   | localhost
User                   | dywang
Password               | *050376F3855A67F5E2C6514FD3130B31006C1276
Select_priv            | N
Insert_priv            | N
Update_priv            | N
Delete_priv            | N
Create_priv            | N
Drop_priv              | N
Reload_priv            | N
Shutdown_priv          | N
Process_priv           | N
File_priv              | N
Grant_priv             | N
References_priv        | N
Index_priv             | N
Alter_priv             | N
Show_db_priv           | N
Super_priv             | N
Create_tmp_table_priv  | N
Lock_tables_priv       | N
Execute_priv           | N
Repl_slave_priv        | N
Repl_client_priv       | N
Create_view_priv       | N
Show_view_priv         | N
Create_routine_priv    | N
Alter_routine_priv     | N
Create_user_priv       | N
Event_priv             | N
Trigger_priv           | N
Create_tablespace_priv | N
Delete_history_priv    | N
ssl_type               |
ssl_cipher             |
x509_issuer            |
x509_subject           |
max_questions          | 0
max_updates            | 0
max_connections        | 0
max_user_connections   | 0
plugin                 | mysql_native_password
authentication_string  | *050376F3855A67F5E2C6514FD3130B31006C1276
password_expired       | N
is_role                | N
default_role           |
max_statement_time     | 0.000000

1 row in set
Time: 0.045s

grant 增加存取資料庫權限給用戶 dywang，其中 *.* 表示所有資料庫的所有資料表，若要限定可以修改此項。
```
MariaDB root@(none):mysql> grant all privileges on *.* to 'dywang'@'localhost';
Query OK, 0 rows affected
Time: 0.019s
```

查看用戶 dywang，有存取資料庫的權限。

MariaDB root@(none):mysql> select * from user where user='dywang'\G
***************************[ 1. row ]***************************
Host                   | localhost
User                   | dywang
Password               | *050376F3855A67F5E2C6514FD3130B31006C1276
Select_priv            | Y
Insert_priv            | Y
Update_priv            | Y
Delete_priv            | Y
Create_priv            | Y
Drop_priv              | Y
Reload_priv            | Y
Shutdown_priv          | Y
Process_priv           | Y
File_priv              | Y
Grant_priv             | N
References_priv        | Y
Index_priv             | Y
Alter_priv             | Y
Show_db_priv           | Y
Super_priv             | Y
Create_tmp_table_priv  | Y
Lock_tables_priv       | Y
Execute_priv           | Y
Repl_slave_priv        | Y
Repl_client_priv       | Y
Create_view_priv       | Y
Show_view_priv         | Y
Create_routine_priv    | Y
Alter_routine_priv     | Y
Create_user_priv       | Y
Event_priv             | Y
Trigger_priv           | Y
Create_tablespace_priv | Y
Delete_history_priv    | Y
ssl_type               |
ssl_cipher             |
x509_issuer            |
x509_subject           |
max_questions          | 0
max_updates            | 0
max_connections        | 0
max_user_connections   | 0
plugin                 | mysql_native_password
authentication_string  | *050376F3855A67F5E2C6514FD3130B31006C1276
password_expired       | N
is_role                | N
default_role           |
max_statement_time     | 0.000000

1 row in set
Time: 0.021s

更新用戶權限。

MariaDB root@(none):mysql> flush privileges;
Query OK, 0 rows affected
Time: 0.023s