How to create table on another schema ?
Question:
we want to create table on anothere schema use different user;
just like user a create table in user b.
create user a identified by "pass" password expire account lock
alter user a accountunlock;
create user b identified by "pass"
Solution:
1:for a simple solution, we can use 'grant create any table to a;'
2:anothere good one as follow:
grant create session to a;
grant create session to b;
grant create table to a;
create or replace procedure a.CreateTable(table_def varchar2)
is
BEGIN
EXECUTE IMMEDIATE table_def ;
END;
create or replace synonym b.CreateTable for a.CreateTable;
grant execute on a.CreateTable to b ;
Test :
begin
CreateTable('create table t12345(name varchar(255))');
end;