`grant all privileges on db1.* to user1@'%' with grant option;
Am使用mysql workbench将转储导入到我的数据库中。将转储导入数据库db1时,发生错误,指出
ERROR 1227 (42000) at line 49: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
由于成功转储了所有表,但是在将例程导入数据库时发生了错误。我赋予用户1的特权有什么问题吗?请咨询。
#1 楼
从政治上正确的意义上讲,您所要求的是不可能的。为什么?SUPER特权是全局特权,而不是数据库级别特权。
当您使用
grant all privileges on db1.* to user1@'%' with grant option;
创建用户时,您在表mysql.user
中填充了user='user1'
和host='%'
。所有其他列(全局特权)均默认为“ N”。这些列之一是Super_priv
。表格如下:mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| 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) | YES | | | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.00 sec)
mysql>
Super_priv
出现在Show_db_priv
之后。数据库级别特权已填充到
mysql.db
中。就是这样:mysql> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | 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 | |
| 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 | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_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 | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
mysql>
请注意,
Super_priv
在mysql.db
中不存在。要以纯SQL方式可视化,请以user1身份登录并运行
SHOW GRANTS;
输出将有两行:GRANT USAGE ON *.* TO user1@'%' ...
GRANT ALL PRIVILEGES ON db1.* TO user1@'%' ...
有可以尝试的技巧,但我通常不建议这样做。
步骤01)以
root@localhost
登录mysql(应该具有所有功能) privs)步骤02)运行此查询
UPDATE mysql.user SET Super_Priv='Y' WHERE user='user1' AND host='%';
步骤03)运行此查询
FLUSH PRIVILEGES;
从理论上讲应该可以。然后,user1可能会起作用(我不能保证)。
UPDATE 2014-12-19 15:24 EST
Metafaniel刚刚问到
很好的解释,谢谢。但是,如果您不建议采用这种方法来解决该问题,那么还有什么其他方法可以使用户获得此Super_priv的最佳选择呢?谢谢! – Metafaniel
由于只有数据库访问权限的用户不能拥有SUPER,因此唯一可以做的就是在转储中手动更改DEFINER。基本思想是将mysql仅将例程转储到文本文件中。然后,将定义器编辑为
user1@'%'
。然后,您应该可以重新加载。Oct 02, 2011
:mysqldump转储触发器和过程可以吗?在mysqldump中Jul 25, 2011
:仅转储MySQL中的存储过程用于视图的相同内容
>
Jul 25, 2011
:MySQL视图授权Mar 23, 2012
:在许多视图上修改DEFINER #2 楼
我有同样的问题。我所做的是:GRANT SUPER ON *.* TO user1@localhost
问题解决了。
CAVEAT:向用户授予SUPER特权是否有危险?
#3 楼
导入过程中出现“访问被拒绝”错误,可能是由于导出的数据库与将要导入的数据库之间的DEFINER特权不匹配。我遇到了同样的情况,下面的命令对我成功导入数据库非常有效。cat db1.sql | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -u user1 -p db1
评论
使用mariadb和更新语句“ ERROR 1348(HY000):列'Super_priv'无法更新'
–c4f4t0r
19年9月5日在9:15
一旦我授予授予访问权限,我仍然会看到所有默认值(与* _priv列相同)
–诺曼
20 Mar 24 '20 at 12:19