Oracle proxy user, create dblink, job in different schema

This post describes how database administrator can use to log on to a user when the password is not known.

Sometimes DBAs want to create database links in another schema, that is not possible just with create database link username.database_link_name, but the connected user should be the same as the owner of the link.
Also if DBA want the job, during creation, to take different user credentials than they are connected there are two methods(maybe more):

1. By resetting user password

  1.  -- Get user password hash
    SELECT password
    FROM   sys.user$
    WHERE  name = 'MARI';
    
    PASSWORD
    ------------------------------
    D456844C36682A67
    -- Reset the password
    ALTER USER MARI IDENTIFIED BY 123;
  2. --Connect by mari and do what you want.
    CONN MARI/123
  3. --Return back mari's password
    CONN / AS SYSDBA
    ALTER USER MARI IDENTIFIED BY VALUES 'D456844C36682A67';

2. By using proxy user. Assume that we have DBA user named my_dba, by which we will be able to connect to the database as MARI without knowing MARI password.

  1. ALTER USER MARI GRANT CONNECT THROUGH my_dba;
  2. CONN my_dba[MARI]/my_dba_password
  3. SHOW USER
    USER is "MARI"

    Do what you want with mari user.

  4. Proxy users can be identified using the PROXY_USERS view.
    SELECT * FROM proxy_users;
  5. –To revoke permission
    ALTER USER scott REVOKE CONNECT THROUGH test_user;

About Mariami Kupatadze
Oracle Certified Master Linkedin: https://www.linkedin.com/in/mariami-kupatadze-01074722/

2 Responses to Oracle proxy user, create dblink, job in different schema

  1. doganay says:

    Creating an sp on user’s schema and executing it is also is a nice method:
    https://doganay.wordpress.com/2011/11/16/how-to-drop-private-database-link/

    Also, you can use DBMS_SYS_SQL.PARSE_AS_USER:
    https://doganay.wordpress.com/2015/10/19/create-private-database-link-for-another-user-using-dbms_sys_sql-parse_as_user/

Leave a Reply

%d bloggers like this: