gpt4 book ai didi

mysql - 在MySQL中创建用户而不删除、更新和更改权限

转载 作者:行者123 更新时间:2023-11-29 13:33:16 25 4
gpt4 key购买 nike

出于审核目的,我必须向某些用户提供对数据库的直接访问权限,并且应该添加限制以避免这些新用户没有删除、更新和更改权限。

最佳答案

只需创建一个用户并授予SELECT权限。

CREATE USER user_name@host_name identified by 'password';
GRANT SELECT ON db_name.* TO user_name@host_name;

检查用户拥有哪些权限

SHOW GRANTS FOR user_name@host_name;

并确保用户只有GRANT USAGEGRANT SELECT ON db_name.*

<小时/>

假设我有 my_db 数据库,其中包含 test 表,我想创建一个名为 user1 的用户,该用户将被允许仅从本地主机连接,并且能够从此数据库中的所有表中读取数据,但无法插入、更改和删除数据。

mysql> create user user1@localhost identified by 'password';Query OK, 0 rows affected (0.00 sec)mysql> show grants for user1@localhost;+--------------------------------------------------------------------------------------------------------------+| Grants for user1@localhost                                                                                   |+--------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |+--------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> grant select on my_db.* to user1@localhost;Query OK, 0 rows affected (0.00 sec)mysql> show grants for user1@localhost;+--------------------------------------------------------------------------------------------------------------+| Grants for user1@localhost                                                                                   |+--------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' || GRANT SELECT ON `my_db`.* TO 'user1'@'localhost'                                                             |+--------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

现在让我们看看我们的 user1 可以做什么和不能做什么

$ mysql -uuser1 -p mysql> use mysqlERROR 1044 (42000): Access denied for user 'user1'@'localhost' to database 'mysql'mysql> use testERROR 1044 (42000): Access denied for user 'user1'@'localhost' to database 'test'mysql> use my_dbDatabase changed

如您所见,我们的 user1 只能连接到 my_db 数据库。

现在让我们看看该用户可以使用表 test(该数据库中唯一的表)中的数据执行哪些操作

mysql> select * from test;+------+| id   |+------+|    1 ||    2 |+------+2 rows in set (0.00 sec)mysql> insert into test values (3);ERROR 1142 (42000): INSERT command denied to user 'user1'@'localhost' for table 'test'mysql> delete from test where id = 1;ERROR 1142 (42000): DELETE command denied to user 'user1'@'localhost' for table 'test'mysql> update test set id = 10 where id = 1;ERROR 1142 (42000): UPDATE command denied to user 'user1'@'localhost' for table 'test'

同样,用户只能从表格中进行选择。

关于mysql - 在MySQL中创建用户而不删除、更新和更改权限,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19122992/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com