Thursday, November 05, 2009

A quick example of proxy authentication

It is fairly typical to have a bunch of developers working in a single schema. Often you want to keep track of which person did what in that schema. [Ideally, you want to know why, but reading minds is beyond the scope of this article.] One way to improve the traceability of activity in the schema is to give everyone a user account with their own password and then allow those development users to connect as a proxy to the schema user. Since everyone connects to the single schema using their own individual username/password, no-one needs to know the schema password and the DBA can even set it to some random jumble of 20 to 30 characters to prevent guessing.

The proxy user doesn't naturally appear in the V$ views (as far as I can tell), but can be derived from SYS_CONTEXT, and therefore used in a LOGON trigger to set CLIENT_INFO which is visible, or you could trace DDLs with AUDIT or a trigger and store the value there.

C:\>sqlplus gary/gary@xe
SQL*Plus: Release - Production

SQL> create user schema1 identified by ***** default tablespace users;
User created.
SQL> alter user schema1 quota unlimited on users;
User altered.
SQL> create table schema1.test (id number);
Table created.
SQL> create user devuser identified by dangermouse default tablespace users;
User created.
SQL> alter user schema1 grant connect through devuser;
User altered.
SQL> grant create session to schema1;
Grant succeeded.
SQL> conn devuser[schema1]/dangermouse@xe
SQL> select user from dual;
SQL> select sys_context('userenv','proxy_user') from dual;

With a JDBC client, such as SQuirrel, I simply put the username as DEVUSER[SCHEMA1] and use the DEVUSER password.


YuriAP said...

Typical developers recompile schema objects such as package. What kind of privileges they must have?

SydOracle said...

The devuser is only used for password authentication. When they connect as a proxy user to the schema account they will get all the privileges of that schema account.

Just think of it as a way to have multiple, individual passwords for the same account.

YuriAP said...

Sorry, my question has been caused by my overlooking the create procedure privilege lack on my development schema.

Karesh said...

Nice post!
I'm trying to achieve same thing. I've 2 local users (schema1 user & schema2 user). Now I'm using schema1 user account to switch to schema2 tablespace and create/update tables.

I'm able to SELECT the tables but can't insert or update..
Ran these queries:-
2) ALTER USER system GRANT CONNECT THROUGH user1; commit;

Error on INSERT query:-

SQL Error: ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to change the current username or password
without the appropriate privilege.

SydOracle said...

I suspect there's extra issues involved in the SYSTEM user that prevent proxy authentication working there.

I'd be looking at other mechanism to 'delegate' SYSTEM functionality.

Though a database trace would be interesting on working out the connection between an INSERT and changing a password. I'd suspect a trigger is involved.