Proxy Users & Schema Only Accounts

Remember those MYAPP/MYAPP@MYDB logons? With all the security buzz and auditors out there, I thought of those as a thing of the past. This blog won't preach about the importance of strong passwords. Instead, I'll explain which capabilities of Oracle Database we can use to avoid the need for such logons and what I consider to be the best practice for them.

Schema Only Accounts

They were introduced in 18c, and they allow us to create a user (who is the owner of objects, such as tables or views) without a password. Such a user cannot log on to the database.

It's simple to create such an account:

SQL> create user MYAPP no authentication;

Or, if you already have an existing user, you can modify it:

SQL> alter user MYAPP no authentication;

On older databases, we could achieve a similar result by setting an “impossible” password hash, as explained on Pete Finnigan's Oracle Security Weblog.

Note that traditionally, on older databases, simply revoking CREATE SESSION from the MYAPP user and then locking the account doesn’t really work because then you cannot log in to this schema via a proxy user, as explained in the next chapter.

So, now we have a user who has access to all the objects it owns - but can't log on.

Oracle Proxy Users

These are the answer to how developers can create/modify the objects owned by MYAPP user. Every developer should have his/her own account on the database. If there are many developers/users working on many databases, then maybe consider using externally authenticated users (such as those from MS Active Directory/Kerberos, Radius, etc). The point here is that, regardless of how developers' accounts are authenticated, there should be a database user for each developer.

Let's suppose that we have a developer named SCOTT. So we create a traditional database account for him:

SQL> create user SCOTT identified by "tiger";

Notice how we didn't grant anything to user SCOTT? At this point, he cannot even connect to the database, because he's lacking the CREATE SESSION privilege. Which we don't need to grant. Now, for the fun part:

SQL> alter user MYAPP grant connect through SCOTT;
SQL> grant CREATE SESSION to MYAPP;

We only allowed the schema owner, MYAPP, to connect through SCOTT. So, if SCOTT wants to use sqlplus to work on the MYAPP schema, he would connect like this:

$ sqlplus scott[myapp]/tiger@MYDB
SQL> select user from dual;

USER
-----------
MYAPP

So, he used his personal password to connect to the database, and yet he is working as the MYAPP user with all the permissions that the MYAPP user has.

Database Design and "API Schemas"

We already have MYAPP, which is the main schema owner with all the data, that is, all the tables. It can also contain packages and views with logic that is common to all API schemas. However, only developers, using proxy users, can connect. What about application servers and other end-user applications that need data from this schema?

Most importantly, no end-user should connect to MYAPP directly. Instead, create API schemas, such as, say MYAPP_GUI. There could be others, e.g., MYAPP_CLI. This API schema should have views and packages that are essentially wrappers for what MYAPP provides. This allows access to only the parts of MYAPP that are relevant for the interface and, equally important, exposes the interface so that its users can easily access it. For example, some libraries in certain languages have difficulty specifying RECORDs as parameters to package functions.

If clients (that is, applications) connect to those API schemas directly, we have already made a step forward from where this blog started. But we should still go a step further.

We can create those API schemas with no authentication in the same way we did with our main MYAPP schema. Then we can let end users connect using their own usernames and grant each of them roles, which allow them to execute actions on API schemas. After logging on, they would do something like:

SQL> alter session set current_schema=MYAPP_GUI;

Application Servers & Connection Pools

Traditional applications, such as Oracle Forms, tended to allow login using an end-user database account. And everything described above "just works." However, today, most web-based applications prefer to have a connection pool.

The problem with a connection pool is that it establishes a fixed number of connections, e.g., 30 connections. All of them are established with the same database user. So far, this is how we’ve tackled this problem:

About ten years ago, I wrote a connection pool compatible with the WildFly application server, which establishes connections with a user that has no privileges at all. Let's call it POOL_USER. The only privilege that this POOL_USER has is CREATE SESSION. We let it establish all the connections with this user. However, whenever we take a connection from the pool, we call:

oracle.jdbc.OracleConnection.openProxySession(), which essentially does the same as we showed with the sqlplus example earlier in the post. And whenever we return a connection to the pool, we call conn.close(OracleConnection.PROXY_SESSION), which returns to the unauthenticated context of being logged in as our POOL_USER, no longer as the real end-user. For more info, check Java Doc.

While this worked, it had a drawback - most Java applications would release database connections as quickly as possible (according to Java best practices). But that means developers can't use PL/SQL session state because it is lost after every page load. So, a few years later, I wrote H2DPool, which matches an HTTP session with one or many database sessions. As long as the user is logged in, the user has a database session established. That way, we're logging in as end-users and utilizing all the power of PL/SQL. And yes, this is no longer really a "pool," even though I named it so, because connections are established on application logon and closed on logout (or on session timeout).

Final Thoughts

This year, my colleague took my work on H2DPool even further and added support for OIDC, which may be the topic for another blog post. We never made those pool implementations publicly available; I just wanted to illustrate one of the possible ways to stay true to the guidelines presented in this blog in today’s world where 2FA, central user management and similar requirements are expected from most applications.

You can also read about topics from this post on oracle-base.com. Maybe the principles discussed here were considered utopian there back in 2005, but by sharing those ideas, evidently, they can eventually become reality.