I have two schemas.
我有两个模式。
1.Schema A
1。模式一
2.Schema B
2。模式B
I need to do following.
我需要遵循。
- I want create some tables in schema B (same as some tables in A)
- 我想在模式B中创建一些表(与A中的一些表相同)
- Then Move data from A to B.
- 然后将数据从A移动到B。
Now I want to do ALL this from schema A. I have written a package which when executed in A will create all the tables in B and then create synonyms for them in A. And then will just select data from its own tables and insert into B's tables.
现在我想从模式a中完成所有这些，我已经编写了一个包，当在a中执行时，它将在B中创建所有的表，然后在a中为它们创建同义词，然后从它自己的表中选择数据并插入到B的表中。
VERY IMP : Now this entire thing has to happen in one go. Just an execution of one begin block should do the entire job.
小恶魔:现在这整件事必须一下子发生。只执行一个begin块就可以完成整个工作。
Problem : But now the synonyms would not work because Schema A does not have any privs on the tables it created in B .
问题:但是现在同义词不起作用，因为模式A在它在B中创建的表上没有任何私有信息。
so is there a way to create tables (from A to B ) with all the privs given at the creation time ? Or can the schemas be switched in PL-SQL while execution so that privs can be granted from B to A ? (I am sure this can not be done, but nothing is impossible they say ! :O :P so asking )
那么是否有一种方法可以创建表(从a到B)和在创建时提供的所有privs ?或者，在执行过程中，可以在PL-SQL中切换模式，以便从B授予到A ?(我相信这是不可能的，但是他们说没有什么是不可能的!O:P so ask)
Please help me guys ! All suggestions are welcome !
请帮帮我，伙计们!欢迎提出建议!
The Main objective of this job is to do it in one go and from only one schema.
这项工作的主要目标是一次完成，并且只从一个模式开始。
1 个解决方案
#1
3
You can define a procedure which will run under definer rights, instead of caller rights
您可以定义一个将在definer权限下运行的过程，而不是调用方权限
CREATE OR REPLACE PROCEDURE definer_test AUTHID DEFINER IS
BEGIN
...
END definer_test;
You would define such a procedure in schema B, which does the job and call it from schema A.
您将在模式B中定义这样一个过程，它完成这个工作并从模式a调用它。
#1
3
You can define a procedure which will run under definer rights, instead of caller rights
您可以定义一个将在definer权限下运行的过程，而不是调用方权限
CREATE OR REPLACE PROCEDURE definer_test AUTHID DEFINER IS
BEGIN
...
END definer_test;
You would define such a procedure in schema B, which does the job and call it from schema A.
您将在模式B中定义这样一个过程，它完成这个工作并从模式a调用它。