在同一服务器上的数据库之间复制用户权限 [英] Copy user privileges between databases on the same server

问题描述

最近的一个项目更新涉及为应用程序添加大量相对(每 6-12 个月更新一次)静态数据.由于它的大小(大约 1GB，包括索引)，它被选择包含在一个单独的数据库中.代码已经更新以处理这一切，但是现在在开发机器上简单的一次性使用更新部署将是一个相当大的麻烦:即将应用程序的用户权限添加到新数据库.

A recent project update involved adding a massive amount of relatively (updates every 6-12 months) static data for the application. Because of its size (appx 1GB, including indexing), it was elected to include it in a separate database. The code has been updated to handle it all already, but now what was an easy one-off on the development machines would be a rather large headache to deploy with the update: namely, to add the application's user privileges to the new database.

是否有一种简单的方法可以复制主数据库中已存在的用户以访问新用户?

Is there an easy way to copy the user that already exists on the main database to have access to the new one?

例如用户最初是使用标准GRANT ALL on main_db.* to user@localhost 由‘XX’标识创建的；"

e.g. The user was originally created with the standard "GRANT ALL on main_db.* to user@localhost identified by 'XX';"

而且我希望找到一种方法可以轻松地将其转换为在 static_db.* 上将所有内容全部授予由 'XX' 标识的 user@localhost；"无需在部署应用程序的每台服务器上手动执行此操作(是的，它们都有不同的XX"值.:))

And I'm hoping to find a way to easily turn that into "GRANT ALL on static_db.* to user@localhost identified by 'XX';" without having to manually do it on every server the application is deployed on (and yes, they all have different values for "XX". :) )

以 Devart 的建议为起点，我得到了我需要的东西.有问题的数据库范围权限表是 mysql.db.在这里发布查询(以通用形式)，以防它对其他人有帮助.

Using Devart's advice as as starting point, I got what I needed. The table in question for database-wide permissions is mysql.db. Posting the query here (in generic form) in case it helps anyone else.

INSERT INTO mysql.db 
SELECT 
Host, 'New_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
    FROM mysql.db
WHERE
User='username' AND Host = 'hostname' AND Db='Old_Db';

推荐答案

所有授予的权限都存储在系统表中，例如- mysql.tables_priv, mysql.columns_priv, mysql.db...你可以写一个脚本复制你需要的所有权限.

All granted privileges are stored in system tables, e.g. - mysql.tables_priv, mysql.columns_priv, mysql.db... You can write a script to copy all privileges you need.

假设我们有用户 -

CREATE USER 'user1'@'%';
GRANT Insert ON TABLE database1.table1 TO 'user1'@'%';
GRANT Execute ON PROCEDURE database1.procedure1 TO 'user1'@'%';

现在，我们将复制这些权限以访问数据库 'database2' -

Now, we will copy these privileges to have access to database 'database2' -

-- Copy table's privileges
INSERT INTO mysql.tables_priv SELECT host, 'database2', user, table_name, grantor, timestamp, table_priv, column_priv FROM mysql.tables_priv
  WHERE user = 'user1' AND host = '%' AND db = 'database1';

-- Copy routine's privileges
INSERT INTO mysql.procs_priv SELECT host, 'database2', user, routine_name, routine_type, grantor, proc_priv, timestamp FROM mysql.procs_priv
  WHERE user = 'user1' AND host = '%' AND db = 'database1';

-- Do not forget to apply changes ;-)
FLUSH PRIVILEGES;

对另一个系统表执行相同的操作.

Do the same for another system tables.

这篇关于在同一服务器上的数据库之间复制用户权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！