1.创建用户
mysql> create user 'test'@'%' identified by 'test';
Query OK, 0 rows affected (0.05 sec)
mysql> grant all privileges on test.* to test@'%';
Query OK, 0 rows affected (0.09 sec)
2.查看数据库权限
mysql> select *from information_schema.user_privileges;
+------------+---------------+-------------------------+--------------+
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+------------+---------------+-------------------------+--------------+
| 'root'@'%' | def | Select | YES |
| 'root'@'%' | def | Insert | YES |
| 'root'@'%' | def | Update | YES |
| 'root'@'%' | def | Delete | YES |
| 'root'@'%' | def | Create | YES |
| 'root'@'%' | def | Drop | YES |
| 'root'@'%' | def | Process | YES |
| 'root'@'%' | def | References | YES |
| 'root'@'%' | def | Alter | YES |
| 'root'@'%' | def | Show Databases | YES |
| 'root'@'%' | def | Super | YES |
| 'root'@'%' | def | Execute | YES |
| 'root'@'%' | def | Index | YES |
| 'root'@'%' | def | Create User | YES |
| 'root'@'%' | def | Create Tablespace | YES |
| 'root'@'%' | def | Trigger | YES |
| 'root'@'%' | def | Create View | YES |
| 'root'@'%' | def | Show View | YES |
| 'root'@'%' | def | Create Role | YES |
| 'root'@'%' | def | Drop Role | YES |
| 'root'@'%' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'%' | def | LOCK TABLES | YES |
| 'root'@'%' | def | CREATE ROUTINE | YES |
| 'root'@'%' | def | ALTER ROUTINE | YES |
| 'root'@'%' | def | EVENT | YES |
| 'root'@'%' | def | SHUTDOWN | YES |
| 'root'@'%' | def | RELOAD | YES |
| 'root'@'%' | def | FILE | YES |
| 'root'@'%' | def | CONFIG | YES |
| 'root'@'%' | def | REPLICATION CLIENT | YES |
| 'root'@'%' | def | REPLICATION SLAVE | YES |
| 'test'@'%' | def | USAGE | NO |
+------------+---------------+-------------------------+--------------+
32 rows in set (0.00 sec)
如上是数据库可以使用的权限。
3.检查数据库权限
mysql> show grants;
+----------------------------------------------+
| Grants for User |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' |
| GRANT ALL PRIVILEGES ON test.* TO 'test'@'%' |
+----------------------------------------------+
2 rows in set (0.00 sec)
4.操作测试用户test;
[tidb@mysql1 raft]$ mysql -u test -h 192.168.1.13 -P 4000 -p'test'
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
| test |
+--------------------+
2 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| my_test1 |
| my_test2 |
+----------------+
2 rows in set (0.01 sec)
mysql> insert into my_test1 values(2,'xsq1');
Query OK, 1 row affected (0.02 sec)