Pages

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.

I'm able to SELECT the tables but can't insert or update..Ran these queries:-1) GRANT CONNECT, RESOURCE, CREATE ANY DIRECTORY, DROP ANY DIRECTORY TO user1;2) ALTER USER system GRANT CONNECT THROUGH user1; commit;

Error on INSERT query:-

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