Password Less; Schema Only Accounts
Schema Only Accounts Overview
Oracle Database 18c introduced new feature called "Schema Only Accounts". It means that there is no password authentication on schemas.
This new functionality allows administrators to secure their databases and protect their applications.
Since schemas have no password, one cannot connect directly to the database (this is the whole purpose of the schema only account).
The only way is, connect through a proxy account.
As one cannot connect directly to the schema only accounts, the schema itself is more Secure. In other words, you don’t want application users to connect directly with the schema account.
Why and When to use?
Generally, schema is created with password and credentials might shared with different stack holders or application users.
To avoid direct usage of schema and password, Proxy user can be created but still one can login to schema if someone knows the password.
So, how to avoid miss use of schema password?
Solution is, Schema Only Accounts (in other words password less).
Since there is no password for schemas, no direct connection is allowed. And also no password management is required like password expiry or password rotation.
This feature reduces the security risk of attackers using default/regular passwords to hack into these accounts.
Configure Password less Schema Only Accounts
Following is example show that mobileapp schema not required and authentication to login. One cannot directly login to this schema as there is no password but User cbtadmin has given grants to access objects from mobileapp schema using proxy authentication.
Create Schema without password.
Syntax
CREATE USER <schema_name> NO AUTHENTICATION QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO <schema_name>;
Example
CREATE USER mobileapp NO AUTHENTICATION QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO mobileapp;
Allow application user to connect to Schema
Syntax
ALTER USER schema_owner GRANT CONNECT THROUGH <application user>;
Example
ALTER USER mobileapp GRANT CONNECT THROUGH cbtadmin;
How application user can connect to schema
Syntax
sql> conn <application user>[schema name]/password@pdb1
Example
sql> conn cbtadmin[mobileapp]/cbtadmin@pdb1
Resources
Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert
