Schema only accounts. There is no good reason for anyone to connect to an application schema as the owner. In Oracle 18c, we now have schema only accounts. Okay, I had to double check if this was available on 12c this morning. Yea’ it did not work.
Now in 18c, we have the schema only account. Now, just what is a schema only account. It is an account that can hold database objects and there is no password to connect to the account. Therefore, to do maintenance on the account, you need to connect through a proxy user.
To connect to the schema only account, use <proxy user>[<schema user>]. In this case, we are already connected to sqlcl, so we’re going to use conn rlockard[test_data]@orcl. Once connected, the account is limited to the privileges to what was given to the schema only account. In this case, the schema only account is granted connect and create table. So, when we try to access sys.dba_objects, we get an ORA-00942 error.
You can maintain an application schema and nobody needs to connect at the application owner.