Creating and using proxy users

To complete this recipe, you'll need an existing (for example, OS-authenticated) user who has a DBA role and another existing user (for example, mike).

Proxy authentication is best-suited type of authentication for three-tiered environments. The middle tier is represented as a proxy user in the database and this user can authenticate end-users in such a way that these end users can be audited by the database. In the second step, you created a user appserver (to be the proxy user). In the third step, you granted this user only the create session privilege.

In step 4, you authorized user mike to connect through proxy user appserver. This means that the user appserver can connect to the database on behalf of user mike:

SQL> connect appserver[mike]

Enter password:
Connected.

SQL> show user
USER is "MIKE"

SQL> select sys_context('USERENV','PROXY_USER') from dual;
SYS_CONTEXT('USERENV','PROXY_USER')
-----------------------------------
APPSERVER

To see proxy users, you can query the proxy_users view:

SQL> select * from proxy_users;

PROXY      CLIENT  AUT   FLAGS---------- ------- ---- ------------------------------------
APPSERVER   MIKE    NO   PROXY MAY ACTIVATE ALL CLIENT ROLES

In the last step, you revoked authorization from user mike to connect through proxy user appserver. This means that the user appserver can no longer connect to the database on behalf of user mike.

You can control which roles the proxy user can activate for user. By default, all user roles are activated. If you want the proxy user to activate only particular roles (or no roles) for a user, you can do that by adding the WITH ROLES <role1, role2, .., roleN> (or WITH NO ROLES) clause at the end of the alter user statement. For instance, if the user mike has many roles (including usr_role), and you want him to have only usr_role when he is connected through proxy user appserver, statement will look like this:

SQL> alter user mike grant connect through appserver with roles usr_role;


User altered.


SQL> connect appserver[mike]


Enter password:
Connected.


SQL> select * from session_roles;


ROLE
------------
USR_ROLE


SQL> connect mike


Enter password:
Connected.


SQL> select count(*) from session_roles;


COUNT(*)
--------
25

You can request reauthentication of a user to the database. This means that during proxy authentication, a user's password must be provided. This is done by using the authentication required clause at the end of alter user statement:

SQL> alter user mike grant connect through appserver authentication required;
User altered.