Following tutorial will explain you the process of executing DDL statements on remote database by users other then schema owner with permission control. I created this when we were not allowed to use schema owner as application user but need to execute DDL's securely from other user.
1. Create procedure in remote database that accepts query as parameter
CREATE OR REPLACE PROCEDURE EXECUTEDDL
(ddl_in in varchar2)
IS
BEGIN
EXECUTE IMMEDIATE (ddl_in);
END;
/
CREATE OR REPLACE PROCEDURE EXECUTEDDL
(ddl_in in varchar2)
IS
BEGIN
EXECUTE IMMEDIATE (ddl_in);
END;
/
2. Create private database link in source database if not existing
CREATE DATABASE LINK <Link name> CONNECT TO <Schema name> IDENTIFIED BY <password> USING '<Service name>';
CREATE DATABASE LINK <Link name> CONNECT TO <Schema name> IDENTIFIED BY <password> USING '<Service name>';
3. Create synonym in source database for the procedure created in remote database. I prefer creating synonyms to access any objects of remote database so that code can look clean and changes in remote database objects/schema name can easily be applied in the source database by updating synonyms.
CREATE OR REPLACE SYNONYM <Synonym name> FOR <remote schema name>.EXECUTEDDL@<remote database link >
CREATE OR REPLACE SYNONYM <Synonym name> FOR <remote schema name>.EXECUTEDDL@<remote database link >
4. Create wrapper procedure in source database which will call remote procedure via synonym.
CREATE OR REPLACE PROCEDURE EXECUTEDDL_REMOTE
(ddl_in in varchar2)
IS
BEGIN
<Synonym name>(ddl_in);
END;
/
CREATE OR REPLACE PROCEDURE EXECUTEDDL_REMOTE
(ddl_in in varchar2)
IS
BEGIN
<Synonym name>(ddl_in);
END;
/
5. Grant permission to other users to execute new procedure
GRANT EXECUTE ON EXECUTEDDL_REMOTE TO <User name>
GRANT EXECUTE ON EXECUTEDDL_REMOTE TO <User name>
