Oracle database links to grant/revoke roles

Question

I have a question about how to use database links in Oracle PL/SQL. Let's say I have a database link (Database2) created in my current database, and I am using it to access a second database. For example:

select *from CustomerTable@Database2;

Update CustomerTable@Database2
set Comment = 'Hello world!'
where CustomerID = 123;

These all work successfully.

But let's say I want to grant roles to or revoke roles from a user in Database2. Can that be done through my database link? If so, what is the syntax?

If I open a PL/SQL session in Database2, then the syntax would be:

REVOKE some_role FROM bsmith;

GRANT some_other_role TO rjones;

Is there any way to do this from my original database using my database link?

Answer 1

You can do DDL over a DB Link by calling the DBMS_SQL package on the remote database:

declare
 v_cursor   NUMBER;
 v_ind        number;
 v_ret      varchar2(2000);
BEGIN
  v_cursor := dbms_sql.open_cursor@DB;
  dbms_sql.parse@DB(v_cursor, 'create sequence xyz_seq', dbms_sql.native);
  v_ind := dbms_sql.EXECUTE@DB( v_cursor );
end;
/