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;
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.
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%’;
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.
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.
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
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';
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.
Connections can be made both locally and remotely:
Local:
SQL> CONN usef[usef_schema]/a
Connected.
Remote:
SQL> CONN usef[usef_schema]/a@//192.168.1.20:1521/noncdb
Connected.
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;
SESSION_USER SESSION_SCHEMA PROXY
------------ -------------- -----
USEF_SCHEMA USEF_SCHEMA USEF
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.
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.
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.
Enable the audit policy for usef_schema:
SQL> audit policy mypol1 by usef_schema;
Audit succeeded.
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.
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';
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
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.
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.
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
Similarly, a user with a specified authentication method can be converted to a Schema Only Account:
SQL> alter user usef no AUTHENTICATION;
User altered.
Top comments (0)