DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Schema Only Accounts Feature in Oracle 18c and 19c

In versions prior to Oracle 18c, creating a user was only possible by specifying its AUTHENTICATION method. Generally, in these versions (12c, 11g, 10g), there are three authentication methods for users:

Password: create user usef1 identified by password;

External: create user usef2 identified externally;

Global: create user usef3 identified globally;
Enter fullscreen mode Exit fullscreen mode

When the Password method is used, the hashed password of the user is stored in a database table (the $user table). In the other two cases (External and Global), no password is stored in the database, and authentication is performed outside the database.

Oracle 18c introduced a new feature allowing the creation of a user without specifying an authentication method. Using this feature prevents direct login for this type of user.

In other words, this feature can block direct login to application schemas for security reasons and due to their high-level access. This setting is not limited to user creation time; it can also be applied to an existing user using the ALTER USER command.

For example, the following command creates the user usef_schema without specifying an authentication method:

SQL> CREATE USER usef_schema NO AUTHENTICATION;
User created.
Enter fullscreen mode Exit fullscreen mode

In this case, direct login for usef_schema will be disabled, and no password will be stored in the $user table:

SQL> conn usef_schema

ORA-01017: invalid username/password; logon denied

SQL> conn usef_schema

ORA-01005: null password given; logon denied

SQL> select name,password from user$ where name like ‘%USEF%’;
Enter fullscreen mode Exit fullscreen mode


Additionally, for this type of user, the AUTHENTICATION_TYPE field in the dba_users view will be NONE:
SQL> select USERNAME,AUTHENTICATION_TYPE from dba_users where USERNAME=’USEF_SCHEMA’;


Granting privileges and roles to these users works the same way as with other users:

SQL> grant connect,resource to usef_schema;

Grant succeeded.

SQL> alter user usef_schema quota  unlimited on users;

User altered.

SQL> alter user usef_schema default tablespace users;

User altered.
Enter fullscreen mode Exit fullscreen mode

With these grants, authorized users can create objects for the Schema Only Account or manage its objects:

SQL> grant connect,unlimited tablespace,create any table,select any table to usef;

Grant succeeded.

SQL> conn usef/a

Connected.

SQL> create table usef_schema.mytbl as select * from dual;

Table created.
Enter fullscreen mode Exit fullscreen mode

The usef user cannot drop the table created for usef_schema due to insufficient privileges:

SQL> drop table usef_schema.mytbl;
ORA-01031: insufficient privileges
Enter fullscreen mode Exit fullscreen mode

The following command lists all objects owned by USEF_SCHEMA:

SQL> select owner, object_name, object_type from dba_objects p where p.owner='USEF_SCHEMA';
Enter fullscreen mode Exit fullscreen mode


With Schema Only Accounts, a proxy connection can be used to access this type of schema. This is achieved by granting proxy authentication to another user.

For example, the following command allows usef to connect to usef_schema:

SQL> ALTER USER usef_schema GRANT CONNECT THROUGH usef;
User altered.
Enter fullscreen mode Exit fullscreen mode

Connections can be made both locally and remotely:

Local:

SQL> CONN usef[usef_schema]/a
Connected.
Enter fullscreen mode Exit fullscreen mode

Remote:

SQL> CONN usef[usef_schema]/a@//192.168.1.20:1521/noncdb
Connected.
Enter fullscreen mode Exit fullscreen mode

Using show user and the USERENV app context, we can confirm that we are connected to the usef_schema schema:

SQL> show user
USER is "USEF_SCHEMA"

SQL> select sys_context('USERENV','SESSION_USER') as session_user,
       sys_context('USERENV','SESSION_SCHEMA') as session_schema,
       sys_context('USERENV','PROXY_USER') as proxy
       from dual;
Enter fullscreen mode Exit fullscreen mode
SESSION_USER   SESSION_SCHEMA   PROXY
------------   --------------  -----
USEF_SCHEMA    USEF_SCHEMA     USEF
Enter fullscreen mode Exit fullscreen mode

Previously, the usef user could not drop mytbl. Now, under the proxy connection, the table can be dropped:

SQL> drop table usef_schema.mytbl;
Table dropped.
Enter fullscreen mode Exit fullscreen mode

The proxy authentication privilege can be granted to multiple users. For example, to grant it to nima:

SQL> ALTER USER usef_schema GRANT CONNECT THROUGH nima;
User altered.
Enter fullscreen mode Exit fullscreen mode

Question: If nima makes changes while connected to usef_schema, will it be detectable in auditing?
Answer: Yes.

Example: First, create an audit policy named mypol1:

SQL> create audit policy mypol1 roles dba;
Audit policy created.
Enter fullscreen mode Exit fullscreen mode

Enable the audit policy for usef_schema:

SQL> audit policy mypol1 by usef_schema;
Audit succeeded.
Enter fullscreen mode Exit fullscreen mode

Using nima, connect to usef_schema and create/drop a table:

SQL> conn nima[usef_schema]/a
Connected.

SQL> drop table mytbl;
Table dropped.

SQL> create table mytbl as select name from v$datafile;
Table created.
Enter fullscreen mode Exit fullscreen mode

In the unified_audit_trail view, the DBPROXY_USERNAME column will show nima:

SQL> select p.dbusername, p.dbproxy_username, p.action_name, p.event_timestamp, p.sql_text 
     from unified_audit_trail p 
     where p.unified_audit_policies='MYPOL1';
Enter fullscreen mode Exit fullscreen mode


Note: To view a list of proxy users, query the proxy_users view:

SQL> select * from proxy_users where client='USEF_SCHEMA';
PROXY   CLIENT          AUT   FLAGS
-----   --------------  ---   --------------------------------
USEF    USEF_SCHEMA     NO    PROXY MAY ACTIVATE ALL CLIENT ROLES
NIMA    USEF_SCHEMA     NO    PROXY MAY ACTIVATE ALL CLIENT ROLES
Enter fullscreen mode Exit fullscreen mode

In Oracle 18c, administrative privileges (e.g., SYSDBA, SYSOPER) cannot be granted to Schema Only Accounts:

SQL> grant sysdba to USEF_SCHEMA;
ORA-40366: Administrative privilege cannot be granted to this user.
Enter fullscreen mode Exit fullscreen mode

As a new feature in Oracle 19c, administrative privileges can be granted to Schema Only Accounts:

SQL> CREATE USER usef_schema NO AUTHENTICATION;
User created.

SQL> grant sysdba to USEF_SCHEMA;
Grant succeeded.
Enter fullscreen mode Exit fullscreen mode

After creating a user, you can change its authentication method, or define one for a Schema Only Account, using the ALTER USER command.

For example, to remove Schema Only mode for usef_schema:

SQL> alter user USEF_SCHEMA identified by a;
User altered.

SQL> conn usef_schema/a
Connected.

SQL> select USERNAME, AUTHENTICATION_TYPE from dba_users where USERNAME='USEF_SCHEMA';
USERNAME       AUTHENTICATION_TYPE
------------   -------------------
USEF_SCHEMA    PASSWORD
Enter fullscreen mode Exit fullscreen mode

Similarly, a user with a specified authentication method can be converted to a Schema Only Account:

SQL> alter user usef no AUTHENTICATION;
User altered.
Enter fullscreen mode Exit fullscreen mode

Top comments (0)