linux mysql 数据库权限

发布网友 发布时间：2022-04-19 14:10

我来回答

共4个回答

热心网友 时间：2022-04-07 22:44

hi 楼主，在数据库中创建包含很多，视图，索引，临时表的创建权限都能分开赋予，你可以执行 show privileges 来查看权限参数，我这边就以创建表为例，只包含查询表功能，其他修改，删除，备份没有权限；以下是步骤：
1，create user 'tom'@'%' identified by '123456';---创建用户，无权限；
2， grant create,select on wangxh2.* to tom;-----把wangxh2库的所有表的创建和查询赋予tom
3，flush privileges;-----刷新权限表才能起效
接下来是测试：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
| wangxh2 |
+--------------------+
3 rows in set (0.06 sec)

mysql> use wangxh2
Database changed
mysql> show tables;
+-------------------+
| Tables_in_wangxh2 |
+-------------------+
| test |
+-------------------+
1 row in set (0.00 sec)

mysql> drop test;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test' at line 1
mysql> drop table test;
ERROR 1142 (42000): DROP command denied to user 'tom'@'localhost' for table 'test'
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 33554432 |
+----------+
1 row in set (0.01 sec)

mysql> insert into test values(1);
ERROR 1142 (42000): INSERT command denied to user 'tom'@'localhost' for table 'test'
mysql> delete from test;
ERROR 1142 (42000): DELETE command denied to user 'tom'@'localhost' for table 'test'
mysql> update test set id=1;
ERROR 1142 (42000): UPDATE command denied to user 'tom'@'localhost' for table 'test'
mysql> create table test1 (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test1 values(1);
ERROR 1142 (42000): INSERT command denied to user 'tom'@'localhost' for table 'test1'

[mysql@localhost ~]$ mysqlmp -u tom -paidengshan wangxh2 >/home/mysql/aa.sql
mysqlmp: Got error: 1044: Access denied for user 'tom'@'%' to database 'wangxh2' when using LOCK TABLES
[mysql@localhost ~]$
-----------------------------------------------------------------------------------------

以上测试发现，tom对wangxh2有建表，查询表的权限，但是修改，删除，新增，备份都没有权限，达到你的需求了追问应该是有修改权限！删除，备份等没有

追答那把加权限的语句改成 grant create,select,update on wangxh2.* to tom

热心网友 时间：2022-04-08 00:02

数据库权限的赋值使用grant命令，以下为创建一个数据库、赋值权限、设置数据库用户名和密码；
mysql -u root -p
create database mysqltest;
grant Select,Insert,Update privileges on mysqltest.* to 'mysqltest'@'localhost' identified by 'mysqltestmima123';
flush privileges;
quit

创建数据库mysqltest,数据库用户名mysqltest,数据库密码mysqltestmima123
数据库相应的权限是：Select,Insert,Update
查询，写入，更新

热心网友 时间：2022-04-08 01:37

mysql> grant select,update on db.* to user@'%' identified by 'user';追问应该是有修改权限！删除，备份等没有

热心网友 时间：2022-04-08 03:28

可实现。
当然权限越低越好。