Become another user in PL/SQL

There are some things even SYS cannot achieve without becoming the user, two things spring to mind.

1. Create private database links
2. Submit and modify DBMS_JOBS and scheduler jobs.

For the last 2 you can use DBMS_IJOB and DBMS_ISCHED.

But for database links there is another way - DBMS_SYS_SQL

This hidden package has a function called PARSE_AS_USER which can parse a cursor after doing a 'become user'.

Here's a sample bit of code that creates a database link as another user.

First you have to find out the user's USER_ID (in my case it was 428) and then pass that number in to the function to tell it which user to become.

DECLARE
  l_cursors DBMS_SQL.NUMBER_TABLE;
  l_result  NUMBER;
BEGIN
  l_cursors(428):=DBMS_SYS_SQL.OPEN_CURSOR;

  --parsing statement as a different owner

  DBMS_SYS_SQL.PARSE_AS_USER(
      c             => l_cursors(428),
      statement     => 'create database link luke connect to scott identified by tiger using ''dev''',
      language_flag => dbms_sql.native,
      userid        => 428
  );

 l_result:=DBMS_SYS_SQL.EXECUTE(l_cursors(428));
END;
/

And there you have it the database link luke is now created with an owner user_id 428.