MySQL用户权限

最新推荐文章于 2023-04-11 21:35:08 发布
最新推荐文章于 2023-04-11 21:35:08 发布
阅读量303 收藏
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/bck1453925668/article/details/118000760
版权

在MySQL数据库系统中，权限分配是按照 user表>db表>table_pric表>columns_priv表 的顺序来分配的。

即先判断user表中的权限是否为Y，如果user表中的值是Y，就不需要检查后面的表。否则依次检查db表、table_priv表和columns_priv表。

mysql 5.7.30

新建用户

默认 情况用户只有登录权限（USAGE），不能查看任何的库表
create user foo identified by "123456";
show grants for foo;

新建用户并指定密码
mysql> create user foo identified by "123456";  
Query OK, 0 rows affected (0.00 sec)

mysql5.7 新建用户需要带密码  5.6不需要

mysql> show grants for foo;
+---------------------------------+
| Grants for foo@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'%' |    # 只有 USAGE 权限，表示只有登录权限，看不到任何的库表
+---------------------------------+
1 row in set (0.00 sec)

#查看mysql.user表 foo对应的权限全为 NO

mysql> select * from mysql.user;
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host      | User          | 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 | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| %         | foo           | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N                | 2021-06-17 19:58:19   |              NULL | N              |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
8 rows in set (0.03 sec)

赋权-只读

GRANT SELECT ON hive.* TO 'foo'@'%';
GRANT SELECT,insert,update,delete,create,drop ON hive.* TO 'foo'@'%';

给某些用户 对 某些库 进行赋权
mysql> GRANT SELECT ON hive.* TO 'foo'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for foo;
+---------------------------------------+
| Grants for foo@%                      |
+---------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'%'       |
| GRANT SELECT ON `hive`.* TO 'foo'@'%' |
+---------------------------------------+
2 rows in set (0.00 sec)

GRANT 权限 ON 库.表 TO '用户'@'登录的地址';

此时的mysql.user下 关于 foo用户的权限 仍然都为 NO
从mysql.db 可以看到 foo用户 对那些库有权限
mysql> select * from mysql.db;
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db                 | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| %         | hive               | foo           | Y           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | N            |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
6 rows in set (0.00 sec)


mysql> GRANT SELECT,insert,update,delete,create,drop ON hive.* TO 'foo'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for foo;
+-----------------------------------------------------------------------------+
| Grants for foo@%                                                            |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'%'                                             |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `hive`.* TO 'foo'@'%' |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from mysql.db;
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db                 | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| %         | hive               | foo           | Y           | Y           | Y           | Y           | Y           | Y         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | N            |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
6 rows in set (0.00 sec)

GRANT all ON hive.* TO 'foo'@'%';
 

mysql> GRANT all ON hive.* TO 'foo'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for foo;
+-----------------------------------------------+
| Grants for foo@%                              |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'%'               |
| GRANT ALL PRIVILEGES ON `hive`.* TO 'foo'@'%' |
+-----------------------------------------------+
2 rows in set (0.00 sec)

#mysql.user 不变  都是NO
#mysql.db 可以看出 foo用户 对某个库 拥有很多权限
mysql> select * from mysql.db;
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db                 | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| %         | hive               | foo           | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
6 rows in set (0.00 sec)

GRANT all ON *.* TO 'foo'@'%';

对某个用户  赋 所有库 所有权 登录地址不限

对某个用户
mysql> GRANT all ON *.* TO 'foo'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for foo;
+-----------------------------------------------+
| Grants for foo@%                              |
+-----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'foo'@'%'      |
| GRANT ALL PRIVILEGES ON `hive`.* TO 'foo'@'%' |
+-----------------------------------------------+
2 rows in set (0.00 sec)


mysql> select * from mysql.user;
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host      | User          | 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 | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| %         | foo           | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N                | 2021-06-17 19:58:19   |              NULL | N              |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
8 rows in set (0.00 sec)

去除权限

revoke ALL  ON `hive`.* from 'foo'@'%';

mysql> show grants for foo;
+-----------------------------------------------------------------------------+
| Grants for foo@%                                                            |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'%'                                             |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `hive`.* TO 'foo'@'%' |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)


mysql> 
mysql> revoke ALL  ON `hive`.* from 'foo'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for foo;
+---------------------------------+
| Grants for foo@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'%' |
+---------------------------------+
1 row in set (0.00 sec)

 

用户锁定

ALTER USER 'foo'@'%' ACCOUNT LOCK;
ALTER USER 'foo'@'%' ACCOUNT UNLOCK;

 

mysql> ALTER USER 'foo'@'' ACCOUNT LOCK;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'foo'@'localhost' ACCOUNT LOCK;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'foo'@'%' ACCOUNT LOCK;
Query OK, 0 rows affected (0.00 sec)



mysql> select * from mysql.user;
+-----------+---------------+---+-------------------+----------------+
| Host      | User          | d | password_lifetime | account_locked |
+-----------+---------------+---+-------------------+----------------+
| %         | foo           |   |              NULL | Y              |
+-----------+---------------+---+-------------------+----------------+
8 rows in set (0.00 sec)

[root@]# mysql -ufoo -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3118 (HY000): Access denied for user 'foo'@'localhost'. Account is locked

解锁
ALTER USER 'foo'@'%' ACCOUNT unLOCK;

密码过期

用户密码设置过期 但是可以登录 ，但是不能操作任何东西 ，提示要重设置密码
ALTER USER 'foo'@'localhost' PASSWORD EXPIRE;
set password=password('123456');

mysql> ALTER USER 'foo'@'localhost' PASSWORD EXPIRE;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'foo'@'%' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.user;
+-----------+---------------+-+------------------+--+-------------------+----------------+
| Host      | User          | | password_expired |  | password_lifetime | account_locked |
+-----------+---------------+-+------------------+--+-------------------+----------------+
| %         | foo           | | Y                |  |              NULL | N              |
+-----------+---------------+-+------------------+--+-------------------+----------------+
8 rows in set (0.00 sec)

用户密码设置过期 但是可以登录 ，但是不能操作任何东西 ，提示要重设置密码

[root@ ~]# mysql -ufoo -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 78539
Server version: 5.7.30

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> 
mysql> set password=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sys                |
+--------------------+
9 rows in set (0.00 sec)

从MySQL 5.7.4版开始，用户的密码过期时间这个特性得以改进，可以通过一个全局变量default_password_lifetime来设置密码过期的策略，此全局变量可以设置一个全局的自动密码过期策略。

用法示例： 
可以在MySQL的配置文件中设置一个默认值，这会使得所有MySQL用户的密码过期时间都为90天，MySQL会从启动时开始计算时间。my.cnf配置如下：

[mysqld]
default_password_lifetime=90

如果要设置密码永不过期的全局策略，可以这样：（注意这是默认值，配置文件中可以不声明）

[mysqld]
default_password_lifetime=0

在MySQL运行时可以使用超级权限修改此配置：

mysql> SET GLOBAL  default_password_lifetime = 90;
Query OK, 0 rows affected (0.00 sec)

还可以使用ALTER USER命令为每个具体的用户账户单独设置特定的值，它会自动覆盖密码过期的全局策略。要注意ALTER USER语句的INTERVAL的单位是“天”。

设置单独用户过期时间

ALTER USER  ‘testuser'@‘localhost' PASSWORD  EXPIRE INTERVAL 30 DAY;

禁用密码过期：

ALTER USER  'testuser'@'localhost' PASSWORD  EXPIRE NEVER;

让用户使用默认的密码过期全局策略：

ALTER USER  'testuser'@'localhost' PASSWORD  EXPIRE DEFAULT;

删除用户

drop user foo;
删除后 mysql.user mysql.db 关于该用户的所有都会被删除
DROP USER 'jack'@'localhost';

 

 

bck1453925668
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
MySQL权限管理
不再疯要傻
11-25 1万+
用户和权限管理： 语法 grant 权限 on 数据库.数据表 to '用户' @ '主机名'; 例：给 xiaogang 分配所有的权限 grant all on *.* to 'xiaogang'@'%'; 这个时候 xiaogang 就拥有了 所有权限了   权限列表 权限 说明 举例 usage 连接（登陆）权
MySQL用户权限详细汇总
02-26
这些权限存储在mysql.user表中。GRANTALLON.和REVOKEALLON.只授予和撤销全局权限。数据库层级：据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db表中。GRANTALLONdb_name.和REVOKEALLONdb_name.只...
为什么只有grant usage权限却可以增删改查
IT那些事儿
08-05 1万+
用dbuser在192.168.0.1上登录远程数据库： show grants; 显示 GRANT USAGE ON *.* TO 'dbuser'@'192.168.0.1' IDENTIFIED BY PASSWORD '*FE63xxxxxxxxxxxxx1C16A' 仅此一条，  dbuser这个用户只有usage权限，查了下mysql manual，mysql
MySQL权限详解
weixin_34280237的博客
08-27 213
一．权限表 mysql数据库中的3个权限表：user 、db、 host 权限表的存取过程是： 1)先从user表中的host、 user、 password这3个字段中判断连接的IP、用户名、密码是否存在表中，存在则通过身份验证； 2)通过权限验证，进行权限分配时，按照user?db?tables_priv?columns_priv的顺序进行分配。即先检查全局权限表...
mysql grant usage_为什么只有grant usage权限却可以增删改查
weixin_39890708的博客
01-19 1553
用dbuser在192.168.0.1上登录远程数据库：show grants;显示GRANT USAGE ON *.* TO 'dbuser'@'192.168.0.1' IDENTIFIED BY PASSWORD '*FE63xxxxxxxxxxxxx1C16A' 仅此一条，dbuser这个用户只有usage权限，查了下mysql manual，mysql usage权限就是空权限，默认cr...
PostgreSQL中关于USAGE和SELECT权限的探讨
倪灵-ziop
04-11 2764
主要内容：在PostgreSQL中创建角色、授权的相关语法及作用，通过对于`CREATE ROLE`、`GRANT USAGE ON SCHEMA`及`GRANT SELECT ON TABLE`等命令的解析，阐述了为角色分配不同权限的效果。文章还详细解答了关于只授权SCHEMA的“USAGE”但不授权“SELECT”权限以及只授权“SELECT”但不授权“USAGE”权限的具体表现。
mysql用户权限设置
01-11
MySQL 用户权限设置 在 MySQL 数据库中，用户权限设置是非常重要的，因为它涉及到数据的安全和访问控制。本文将详细介绍 MySQL 用户权限设置的相关知识点，包括创建用户、修改密码、赋予权限、回收权限、查看用户...
mysql用户权限管理实例分析
09-08
MySQL用户权限管理是数据库系统中一个至关重要的环节，它确保了数据的安全性和访问控制，防止未经授权的用户访问或操作敏感信息。在MySQL中，权限管理是通过定义不同级别的权限给不同用户来实现的，以便限制或允许...
设置mysql用户权限
12-17
设置mysql用户权限
Greenplum权限管理
helontian的博客
10-10 1378
Greenplum权限配置
mysql数据备份
Wild
07-30 597
早上朋友问这个问题，我就把这些选项详细整理一下，偶会随时更新的。前提： 使用mysqldump的用户，必须至少具有SELECT 和 LOCK TABLES的权限。库或者表。如果要使用--tab开关，还必须有FILE的权限准备工作：库以及表：Table Create Table ------ ---
mysql 创建、授权、删除用户
qq_25934401的博客
08-06 181
1.mysql 创建只读账户 通过GRANT来创建用户（创建用户同时授权）： GRANT SElECT ON *.* TO 'reader'@'172.16.%.%' IDENTIFIED BY "reader123" *.* 是所有库，如果是只授权某个库例如test，写成test.* reader 是用户名 172.16.%.% 是允许连接到这个数据库的ip网段,当然如果...
MySQL 开启与关闭远程访问
热门推荐
duck_arrow的专栏
10-30 5万+
MySQL 开启与关闭远程访问 (1)通过MySQL用户去限制访问 权限系统目的: MySQL基于安全考虑root账户一般只能本地访问，但是在开发过程中可能需要打开root的远程访问权限，今天介绍的就是如何开启和关闭Mysql远程访问 MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并且赋予该用户在数据库上的SELECT、INSERT、UPDATE和DELETE权限
MySQL8 创建用户,设置修改密码,授权
kfepiza的博客
02-10 1万+
MySQL5.7可以 (创建用户,设置密码,授权) 一步到位 👇👆这样的语句在MySQL8.0中行不通, 必须 创设和授权 分步执行👇也可以分三步刷新权限设置。
mysql grant 用户权限总结
xiewenyu2008的博客
06-30 2157
mysql grant 用户权限总结 说明一下Mysql的权限匹配规则 当用户从客户端请求登陆时，MySQL将授权表中的条目与客户端所提供的条目进行比较，包括用户的用户名，密码和主机。授权表中的Host字段是可以使用通配符作为模式进行匹配的，如test.example.com, %.example.com, %.com和%都可以匹配test.example.com这个主机。授权表中的User字段不允许使用模式匹配，但是可以有一个空字符的用户名代表匿名用户，并且空字符串可以匹配所有的用户名，就像通配符一
如何查看MySQL用户权限
weixin_34416649的博客
01-19 153
如果需要查看MySQL用户权限，应该如何实现呢？下面就为您介绍查看MySQL用户权限的方法，并对授予MySQL用户权限的语句进行介绍，供您参考。 查看MySQL用户权限： show grants for 你的用户 比如： show grants for root@'localhost'; Grant 用法 GRANT USAGE ON *.* TO 'discuz...
MySQL数据库之用户管理
weixin_59629968的博客
10-20 445
mysql用户管理可以对用户权限进行设置远程登陆以及修改密码
MySQL用户和权限及破解root口令
weixin_34032621的博客
05-02 108
MySQL用户和权限 在MySQL中有一个系统自身就带有的数据库叫MySQL，数据库装好以后系统自带了好几个数据库MySQL就是其中过一个，MySQL数据库有个用户账户权限相关的表叫user表，在其中就有创建的用户。 MySQL中完整的用户名是由用户+主机名形成，主机名决定了这个用户在哪个主机上能登陆。 一、用户的创建和密码修改 1.用户的创建 create user 'USER...
mysql 用户权限
最新发布
09-03
MySQL用户权限是指用户对于MySQL数据库的访问和操作权限。MySQL提供了多种权限级别，用户可以被授予不同级别的权限，包括全局级别、数据库级别、表级别和列级别权限。 可以使用命令来管理MySQL用户权限。例如，要创建一个用户并授予相应的权限，可以使用以下命令： mysql -uroot -p -hlocalhost -P3306 mysql -e "CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; GRANT SELECT, INSERT, UPDATE, DELETE ON database.* TO 'username'@'localhost';" 这个命令将创建一个名为'username'的用户，并授予该用户对于'database'数据库的SELECT、INSERT、UPDATE和DELETE权限。 如果要同时修改多个用户的资源，可以使用ALTER USER命令。例如，要同时修改'test1'用户的身份验证方式和'test2'用户的连接数限制，可以使用以下命令： ALTER USER 'test1' IDENTIFIED WITH mysql_native_password, 'test2'@'localhost' IDENTIFIED BY '222222' WITH max_user_connections 2; 要删除一个用户，可以使用以下命令： DROP USER 'username'@'localhost'; 要查看用户的权限情况，可以使用以下命令： SELECT db, table_name, table_priv, column_priv FROM mysql.tables_priv WHERE user = 'username'; 这个命令将显示名为'username'的用户在各个数据库和表上的权限。 请注意，以上命令只是示例，具体的命令和参数需要根据实际情况进行调整。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* [MySQL深入学习（二一）：用户与权限管理](https://blog.csdn.net/qq_38697437/article/details/123983468)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *2* *3* [Mysql用户与权限操作](https://blog.csdn.net/weixin_44826356/article/details/108730250)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值