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;
Advertisements

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: