From 18c onwards,  now you can create schema without a password. These are called “Schema Only Accounts”.  These account don’t allow direct connections but can proxy in a single session proxy. This new feature can  allow administrators to further secure their databases by not allowing direct connections to application schemas for any reason. There are few points to note:

  • Schema only accounts can be used for both administrator and non-administrator accounts.
  • You can grant system privileges ( create ant table)  and admin roles (like DBA) to schema only accounts.  But note that administrative privileges like sysdba/sysoper/sysasm   can’t be granted to schema only accounts.
  • Schema only accounts can’t connect through db links.
  • Schema only accounts can be created for database instance only. Same is not valid for ASM environment.

A schema only account can be created with the NO AUTHENTICATION clause. Basic set up is really simple as below. I connected to Multitenant environment.

The authentication type is listed as NONE in the DBA_USERS view.

If we try to connect to our newly created “NO AUTHENTICATION” user we get the standard ORA-01017 error.

Now grant system privileges to schema only account.

Now set up a proxy user and allow a proxy connection to the SCHEMA_OWNER as below.

This feature was first introduced with Oracle Database 10g, has many other advantages and proper way to create objects in schema only accounts.

In releases prior to Oracle Database 18c, we could able to create the proxy user with passwords only. So if someone know the password, direct connections was possible.  If you try “NO AUTHENTICATION” option in 12c, you’ll get this error.

Altering a Schema Only Account

We can easily switch between schema only and normal account via “ALTER USER” Sql statement.But there is a catch. As I mentioned earlier, schema only account can’t be granted with administrative privileges like “SYSDBA/SYSASM” so you need to revoke these privileges prior to converting to schema only account. Here is an explanation.

You can also create schema only account at container level. There is really no difference if the user is common or local in the multitenant architecture.

Thanks

Mandy

Leave a Reply