Using Oracle Proxy USER for Application Schema updates

Just a quick post as a response on short question:

Question:
Is it possible to provide temporary access to application schema for the time of patches application – without providing schema password and without temporary password changing ?

Something like sudo in Unix ?

The short answer is: Yes. Use Oracle proxy user authentication.

Short demo how to use it:
1) creating SCHEMA_OWNER user:

SQL> CREATE USER SCHEMA_OWNER IDENTIFIED BY SCHEMA_OWNER_PW;
User created.

SQL> GRANT CREATE SESSION TO SCHEMA_OWNER;
Grant succeeded.

SQL> GRANT CONNECT, RESOURCE TO SCHEMA_OWNER;
Grant succeeded.

2) creating SCHEMA_PATCHER user:

SQL> CREATE USER SCHEMA_PATCHER IDENTIFIED BY SCHEMA_PATCHER_PW;
User created.

SQL> GRANT CREATE SESSION TO SCHEMA_PATCHER;
Grant succeeded.

SQL> ALTER USER SCHEMA_OWNER GRANT CONNECT THROUGH SCHEMA_PATCHER;
User altered.

3) Initial SCHEMA_OWNER schema setup

SQL> CONNECT SCHEMA_OWNER/SCHEMA_OWNER_PW
Connected.
SQL> CREATE TABLE TST(ID NUMBER);
Table created.

SQL> INSERT INTO TST VALUES(1);
1 row created.

SQL> COMMIT;
Commit complete.

4) patching SCHEMA_OWNER using SCHEMA_PATCHER

SQL> CONNECT SCHEMA_PATCHER[SCHEMA_OWNER]/SCHEMA_PATCHER_PW;
Connected.

SQL> SELECT USER FROM DUAL;
USER
------------------------------
SCHEMA_OWNER

SQL> SELECT * FROM TST;
 ID
----------
 1

SQL> ALTER TABLE TST ADD(V VARCHAR2(2000) DEFAULT 'DEFAULT_VALUE');
Table altered.

SQL> SELECT * FROM TST;
 ID V
--- ---------------------------------------------
  1 DEFAULT_VALUE

.
So we were able to apply updates to schema SCHEMA_OWNER without exploiting it’s password.

I’ll post about some issues of proxy user authentication in next post.

Advertisements

One thought on “Using Oracle Proxy USER for Application Schema updates

  1. Hi!
    Still waiting for “some issues of proxy user authentication in next post”! 😉
    I knew that SCHEMA_PATCHER account could be locked and could have no CONNECT or CREATE SESSION privilege to login as proxy.
    And I just discovered that SCHEMA_PATCHER aren’t being forced to change its password even if its password is expired.
    This is changed since … (I just leave the further story to author’s responsibility 😉 )

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