MySQL数据库用户基本管理

MySQL数据库用户基本管理

目录

• MySQL数据库用户基本管理

1. 登录管理数据库对象

mysql> create user oldguo@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.06 sec)
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| wordpress     | %         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| oldguo        | 10.0.0.%  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| kedao         | %         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)
#小测试
mysql> create user oldboy@'172.16.1.%' identified by 'oldboy123';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,authentication_string from user;
+---------------+------------+-------------------------------------------+
| user          | host       | authentication_string                     |
+---------------+------------+-------------------------------------------+
| root          | localhost  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost  | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost  | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| wordpress     | %          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| oldboy        | 172.16.1.% | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| oldguo        | 10.0.0.%   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| kedao         | %          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+------------+-------------------------------------------+
7 rows in set (0.00 sec)

2 . 查看表结构

mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.03 sec)

3. 修改和删除用户

#修改
mysql> alter user root@'localhost' identified by '123456';  #修改密码
Query OK, 0 rows affected (0.03 sec)

#删除
mysql> drop user oldguo@'10.0.0.%';
Query OK, 0 rows affected (0.03 sec)
mysql> select user,host,authentication_string from user;
+---------------+------------+-------------------------------------------+
| user          | host       | authentication_string                     |
+---------------+------------+-------------------------------------------+
| root          | localhost  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost  | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost  | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| wordpress     | %          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| oldboy        | 172.16.1.% | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| kedao         | %          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+------------+-------------------------------------------+
6 rows in set (0.00 sec)

4. 权限管理

MySQL的权限定义就是SQL语句。
ALL:
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能

#授予权限
mysql> grant insert,create,update,select,delete on wordpress.* to wordpress@'%';
Query OK, 0 rows affected (0.00 sec)

#回收权限
mysql> revoke all on wordpress.* from  wordpress@'%';
Query OK, 0 rows affected (0.04 sec)

#查看权限
mysql> show grants for wordpress@'%';
+----------------------------------------------------------------------------------+
| Grants for wordpress@%                                                           |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpress'@'%'                                            |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `wordpress`.* TO 'wordpress'@'%' |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

5. 创建 zhihu业务用户能够对zhihu业务库进行业务操作

#第一种方法
mysql> grant insert,update,select,delete on zhihu.* to zhihu@'10.0.0.%' identified by '123456';   #这种创建方法在mysql8.0版本以上已经不能使用了
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show grants for zhihu@'10.0.0.%';
+-------------------------------------------------------------------------+
| Grants for zhihu@10.0.0.%                                               |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhihu'@'10.0.0.%'                                |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `zhihu`.* TO 'zhihu'@'10.0.0.%' |
+-------------------------------------------------------------------------+
2 rows in set (0.00 sec)
#第二种方法
create  user zhihu@'10.0.0.%' identified by '123456';
grant insert,update,delete,select on zhihu.* to zhihu@'10.0.0.%';

6. 权限的查看和回收

#查看
show grants for 用户名@'localhost';
mysql> show grants for root@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

#回收
revoke 权限 on 数据库名.表名 from 用户名@'localhost';
mysql> show grants for zhihu@'10.0.0.%';
+-------------------------------------------------------------------------+
| Grants for zhihu@10.0.0.%                                               |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhihu'@'10.0.0.%'                                |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `zhihu`.* TO 'zhihu'@'10.0.0.%' |
+-------------------------------------------------------------------------+
2 rows in set (0.00 sec)

#收回zhihu用户对zhihu库所有表的删除权限
mysql> revoke delete on zhihu.* from zhihu@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for zhihu@'10.0.0.%';
+-----------------------------------------------------------------+
| Grants for zhihu@10.0.0.%                                       |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhihu'@'10.0.0.%'                        |
| GRANT SELECT, INSERT, UPDATE ON `zhihu`.* TO 'zhihu'@'10.0.0.%' |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)
#注意已经没有delete权限了
``