Creating and using proxy users

In this recipe, you'll learn about proxy users.

Getting ready

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).

How to do it...

1. Connect to the database as a user who has a DBA role:

   $ sqlplus /
   
   

2. Create a proxy user named appserver:

   SQL> create user appserver identified by oracle_1;
   
   

3. Grant create session to the user appserver:

   SQL> grant create session to appserver;
   
   

4. Alter the user to connect through the proxy user:

   SQL> alter user mike grant connect through appserver;
   

5. Connect to the database through proxy user:

   SQL> connect appserver[mike]
   

6. Enter a password for the appserver user (for example, oracle_1):

   Enter password:
   

7. To revoke connection through the proxy user, first connect to the database as a user who has altered user privilege:

   $ sqlplus /
   

8. Revoke connection through the proxy user appserver from user mike:

   SQL> alter user mike revoke connect through appserver;
   

How it works...

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.

Tip

It is recommended that you grant only the create session privilege to proxy users.

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.

There's more...

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.