What is a proxy user?

If you use a proxy user, you can connect to another database user without knowing their password.

Why use a proxy user?

I believe (and I’m not the only one) that giving developers, such as John and Rafal, passwords to, for example, schemas CO and HR and allowing them to log in to the same schemas using the same password is not a good idea.

Another argument is that, e.g. your user has left your project. Instead of revoking his grants from multiple schemas (HR, CO, etc.), you simply drop his user. Nice, isn’t it?

Again, sharing the same password (e.g. for schema HR) with multiple developers is not advisable. Please don’t do it.

How is it helpful for my CI/CD ?

If you utilise SQLcl projects (learn more about it here) for your CI/CD in Oracle DB and APEX, it will enable you to securely allow more people to export changes from different schemas.

To avoid conflicts in a shared Oracle Database development environment, consider using the “Loki” tool (read more about it here). And that would require you to use proxy users as well.

Creating proxy users

First, I created two new database users called JOHN and RAFAL for my developers in the project.

create user JOHN identified by "Qwerty12345$";
create user RAFAL identified by "Qwerty12345$";

And I gave them only the ability to connect.

grant connect to JOHN;
grant connect to RAFAL;

Now, it’s time to allow JOHN and RAFAL to connect directly to the HR and CO schemas without knowing the passwords to those schemas (this is where so-called proxy users are created).

alter user HR grant connect through JOHN;
alter user HR grant connect through RAFAL;
alter user CO grant connect through JOHN;
alter user CO grant connect through RAFAL;

How can John connect to CO using his password?

See below my SQL Developer configuration.

Username = JOHN[CO]

Password = John’s password

Without knowing the password for the CO schema, JOHN can connect, change objects at CO, and so on, just as if he were connected directly to the CO schema.

The same applies to the schema HR, e.g., RAFAL[HR], etc.

Easy, right?

Confusion regarding proxy users and grants

Just to clarify everything.

Imagine you have a user named JOHN who can connect to the HR schema. You remember that, right?

Now, you want to add a new grant so that your application using the HR schema can select a view from the schema XD, e.g., XD.V_SALARIES.

Do you need to add a grant to JOHN? No.

Do you need to add a grant to the HR schema? Yes

Remember, JOHN, as a proxy user to HR, is connecting as he was an HR user, so he doesn’t need to have any additional grants.

Schema JOHN has only one grant → “CONNECT”

I hope you liked this blog post and you will finally start using proxy users :)

Rafal

PS If you want to learn more about PROXY, check this article on ORACLE-BASE → here