Oracle 18c introduced schema-only accounts, and they are great for ISVs and anyone else who wants to contain all data and logic within the same schema. Most often you don’t want to grant direct access to such an application account, just to be safe. Prior to Oracle 18c you typically created an account with a secure password, and then locked it.

Schema-only accounts take this idea a step further, by removing most ways of connecting to it.

So in essence, the account has a schema and can own objects, receive privileges, and, if so configured, can be used as the client identity in single-session proxy authentication. However, it has no password or other authentication method, so it cannot log in directly.

Note that this post is based on Oracle AI Database 26ai, so some of the SQL statements you see here might adjusting for earlier releases.

To create a schema-only account, you specify the no authentication clause:

SQL> create user I_OWN_THINGS no authentication
  2  default tablespace users
  3  temporary tablespace temp
  4  quota 10g on users
  5* profile ora_stig_profile;
User I_OWN_THINGS created.
-- now add any grants as necessary

Using no authentication is different from account lock or password expire you may have used earlier: those states still relate to an account with an authentication method. NO AUTHENTICATION means that no authentication method exists at all. You can later enable one, if needed, but otherwise this account cannot be authenticated directly.

There are certain restrictions that you should be aware of, they are listed in the security guide (📕 19c 26ai)

Now that the schema-only account is created, what do you do with it, or, in other words, how do you deploy schema changes?

It probably comes down to these approaches:

1. Temporarily change the schema-only account to a “regular” account using alter user <username> identified ...
2. Use the security model built into the database to deploy into the schema from a super-user (not SYSTEM or even SYS, one that you created)
3. Use a Proxy User

Let’s look at these in more detail.

Convert to regular user

The first option is probably the easiest as it simply involves assigning a password, connecting to I_OWN_THINGS, deploying, and changing the account back to no authentication. There are downsides to the approach, most notably your audit trail won’t be able to identify who exactly deployed the most recent change.

Personally, I wouldn’t use this approach, there are better ways of achieving the same goal.

Administrator Deployment

The second approach is possible, too, but it would require a different account with privileges to perform DML and DDL against I_OWN_THINGS. Whether this is appropriate depends on the discussions you have with the DBAs and security team. You may point them to schema privileges, a new feature in Oracle AI Database 26ai. Those might persuade them to allow you to perform this type of deployment, but theirs is the final word. An earlier blog post of mine discussed administrator vs user deployments (🔗 link to post), it also features a reference to Dan McGhan’s blog, which is well worth your time,

Proxy User/connect-through

Using proxy users is the third option, and I would like to thank Daniel for pointing it out to me. Using a proxy user for deployments solves the problems associated with the previously discussed approaches:

• There is no need to even temporarily attach a password to the user, opening the door to attacks
• The proxy account does not need elevated object privileges; the client schema account must have the privileges needed for the deployment, and an administrator must authorize and later revoke the CONNECT THROUGH relationship.

Continuing with the previous example where I_OWN_THINGS owns the schema and data, you can enable a trusted user to make schema changes. First you need to create your trusted account:

create user if not exists martin identified by secretPassword;
grant create session to martin;
-- now grant anything else the user might need

The schema owner, I_OWN_THINGS needs to have the necessary privileges to change its schema. How you define them is out of scope of this article, but the account probably must be able to create tables, views, indices, and PL/SQL or JavaScript procedures.

As part of the release procedure you grant the deployment user the right to connect as the schema owner:

alter user I_OWN_THINGS grant connect through martin;

Please refer to the docs for all the details concerning proxy users. Once this command completes, martin is a proxy:

SQL> select
  2    *
  3  from
  4*   proxy_users;
PROXY     CLIENT          AUTHENTICATION    FLAGS
_________ _______________ _________________ ______________________________________
MARTIN    I_OWN_THINGS    NO                PROXY MAY ACTIVATE ALL CLIENT ROLES

Important: this privilege is to be revoked once the schema change has been deployed. That way, the schema owner’s account remains safe and cannot be tempered with.

The next step is to connect and run the schema change

SQL> connect martin[I_OWN_THINGS]@localhost/freepdb1
...
Connected.

If you want to be sure that everything is as expected, have a look at the relevant variables in sys_context. The following example has been lifted from oracle-base:

select
    sys_context('userenv','session_user') as session_user,
    sys_context('userenv','session_schema') as session_schema,
    sys_context('userenv','current_schema') as current_schema,
    sys_context('userenv','proxy_user') as proxy_user;
SESSION_USER    SESSION_SCHEMA    CURRENT_SCHEMA    PROXY_USER
_______________ _________________ _________________ _____________
I_OWN_THINGS    I_OWN_THINGS      I_OWN_THINGS      MARTIN

With everything established, it’s time to deploy the schema changes. In this example a deployment artifact, generated by SQLcl’s projects command, is deployed:

SQL> project deploy -file artifacts/release-2.0.1.zip -verbose

Once that’s completed, disconnect, and revoke the connect through privilege from the account used to deploy the change.

SQL> alter user I_OWN_THINGS revoke connect through martin;
User I_OWN_THINGS altered.
SQL> select
  2    *
  3  from
  4*   proxy_users;
no rows selected

Depending on your audit policies, each activity has been logged and can be traced back to an individual account. This is much better than using a generic service account, or changing the type of user to a regular one by attaching a password.