This article focuses on Oracle 18C new feature: Schema Only Account. This allows schemas can be created without a password from Oracle Database 18C.
Schema is a collection of objects, while DB user is an account to connect to instance. Till Oracle 12C, Oracle always allowed to connect to instance using schemas as well as DB users.
It’s always a bad idea to configure your applications schema credentials as this allows application to have far more privileges than required & it’s a big security risk as using schema credentials one can do almost all changes in objects under ownership of schema.
Instead, it is recommended to configure application users and grant them the necessary privileges on the schema owners objects.
So, one need to restrict direct access to a schema, preventing people accessing it using shared credentials. Instead one access it to do schema changes via a proxy connection, allowing you to audit which users performed which tasks.
ABOUT NEW FEATURE:
From 18C, Oracle now allows to create schemas without password so it cannot to be used to connect to instance & could connect using proxy user. This can allow DBA’s to further secure their databases & applications.
You can grant any system privileges and roles to schema only accounts. Schema only accounts can do all operations as per privileges assigned to them.
Create a Schema Only Account:
CREATE USER <USERNAME> NO AUTHENTICATION;
Alter existing user to Schema Only Account:
ALTER USER <USERNAME> NO AUTHENTICATION;
Find current “Schema Only Accounts” in database:
SELECT USERNAME FROM DBA_USERS WHERE AUTHENTICATION_TYPE='NONE';
Switch a Schema Only Account to normal schema:
ALTER USER <USERNAME> IDENTIFIED BY <PASSWORD>;
How to connect to Schema Only Account using proxy users:
CREATE USER <PROXYUSER> identified by <PROXYPASSWORD>;
ALTER USER <USERNAME> GRANT CONNECT THROUGH <PROXYUSER>;
Stay tuned for More articles on Oracle 18c.
Hope u will find this post very useful. 🙂
Email ID: firstname.lastname@example.org