4 Things About Schema Only Accounts You Should Know
Let’s continue learning about 19c. Some of the things I’ll talk about are not new to 19c, however they are available in this release as well. That is the case with schema only accounts.
Just like me, you might be behind a bit with “new features” of database releases, since Oracle is coming up with a new release every year. It’s challenging to keep up with all the new features in each release.
Schema only accounts were introduced in 18c version of the database, thus they are available in 19c as well. If you are not familiar with the schema only accounts, then this post is for you. If you want a quick refresher about schema only accounts, then this post is for you as well!
1)What are schema only accounts? These are users (schemas) in the database, that have no password.
You heard that right no password!
Since they have no password, these schemas cannot connect directly to the database (this is the whole purpose of the schema only account).
The only way in, is through a proxy account.
Without being able to connect directly to the schema only accounts, the schema itself is more secure. In general you don’t want users, applications connecting directly with the schema account.
No more workarounds are needed to properly secure the objects and data of a schema.
2) How To Create A Schema Only Account?
The Schema only account is create the same way as a regular user account. Instead of specifying identified by, you specify NO ATHENTICATION. Below is an example of creating a schema only account:
SQL> create user diana no authentication; -- create schema only account
SQL> create user dianar identified by password123; -- create schema with password
If you try connecting with this user to the database and enter nothing for the password, as the user has no password, you will receive a standard error message:
SQL> conn diana
Enter password:
ERROR:
ORA-01005: null password given; logon denied
If you check the data dictionary, the AUTHENTICATION_TYPE column in DBA_USERS view is the one that tells you if it’s an authenticated account or not:
SQL> select USERNAME, AUTHENTICATION_TYPE from dba_users where username like 'DIANA%';
USERNAME AUTHENTICATION_TYPE
---------- -------------------
DIANA NONE
DIANAR PASSWORD
3) Things You Can Do With The Schema Only Accounts:
a) Grant privileges, system privileges, object privileges, roles to the schema only account
Grant privileges:
SQL> grant create session to diana;
Grant succeeded.
Even if this grant succeeded, I still can’t connect with this account:
SQL> conn diana
Enter password:
ERROR:
ORA-01005: null password given; logon denied
Grant roles:
SQL> grant dba to diana;
Grant succeeded.
Grant sysdba privilege:
SQL> grant sysdba to diana;
Grant succeeded.
The above grant was not allowed in 18c, looks like it is allowed in 19c
SQL select username , sysdba, authentication_type from V$PWFILE_USERS ;
USERNAME SYSDBA AUTHENTICATION_TYPE
---------- ----- -------------------
SYS TRUE PASSWORD
DIANA TRUE NONE
b) Create objects in the schema only accounts
SQL> create table diana.table1 (id number);
Table created.
c) Connect through proxy to the schema only account
SQL> alter user diana grant connect through hr;
User altered.
SQL> conn hr[diana]/hrtest123#@TESTDBPDB————- ——————— ——————-
Connected.
SQL> select user,
sys_context('USERENV','SESSION_USER') as session_user,
sys_context('USERENV','PROXY_USER') as proxy_user,
from dual;
USER SESSION_USER PROXY_USER
DIANA DIANA HR
d) Switch the account to a regular account with a password
SQL> alter user diana identified by password123#;
User altered.
SQL> alter user diana no authentication;
User altered.
4) Things you cannot do with the schema only account:
a) connect directly with the account
We have seen this above, if you don’t specify a password, you can’t connect, and if you specify an incorrect password (really any password would be incorrect, since the user has no password), you will get an invalid username password message.
b)connect through db links.
cannot create the database link without the password:
SQL> create database link diana_db_link connect to diana using 'TESTDBPDB';
create database link diana_db_link connect to diana using 'TESTDBPDB'
*
ERROR at line 1:
ORA-00954: missing IDENTIFIED keyword
or, you can create the db link but not really use it:
SQL> conn hr[diana]/hrtest123#@testdbpdb
Connected.
SQL> create database link diana_db_link using 'testdbpdb';
Database link created.
SQL> select * from dual@diana_db_link;
select * from dual@diana_db_link
*
ERROR at line 1:
ORA-25430: connected user database links are not supported in proxy session
If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!