Background
Story time
When I just started working as a DBA, the Senior Database Administrator was taking me through the environment and how everything was set up, including all the servers, databases, applications, and more. He was talking about one of the purchased applications that runs in production, and then explained that the vendor had everything set up almost perfectly from a security perspective. He explained that they separated all the application-owning schemas from all the application-using schemas, except for one that actually connects to the database and also happens to own some tables… and then he turned to me and asked, “Harris, do you know why this is important?” I was confused and went “uhhhhhhhh… Can you please tell me” The look on his face! I’ll leave it up to you to imagine. Obviously, I felt stupid, but it’s okay (I kinda am sometimes, you know), and as time has passed, I’ve come to understand that the lack of knowledge on this issue is way less than uncommon (in fact, people cut corners in many worse ways). He told me, “This is important, such that the user that the application connects as does not have access to make structural changes like dropping tables and/or TRUNCATE, but can only do DML”. Safe to say, that day I learned something!
It’s all about Design
As a rule of thumb, when designing applications that run against a database, always separate the connecting (or application, or using… however you call it) schema from the owning schema (the account that actually owns the objects… hopefully you get the point). The schema that owns the application’s objects should be a DBA schema; everyone else, including developers, can only access those objects and perform DML on them, but should not be able to make structural changes (CREATE, ALTER, DROP, TRUNCATE, etc.). I try to stay abreast of database-related topics: new features, Best practices, DBA experiences, Database stories, that kind of thing. I have heard stories that not only go against the rule mentioned above, but do way worse. Here’s one such story! Design your applications right! Create a separate schema for owning objects and another for the application to connect to. Please listen to me, this is good advice.
Locking is not the Answer.
For those who do know about the above and have been enforcing it in their environments, kudos to you; keep up the good work. However, there is still the issue that we have an account that owns all objects and has credentials that can be brute-forced or subject to other attacks. To mitigate these, sometimes people go the extra mile and not only separate the accounts but also lock the owning schemas so that no one can connect as them. In Oracle, the ability to lock accounts is a good feature. However, while it prevents anyone from logging in as that account, it also tells potential malicious users that the account exists. try it! Lock an account and try to connect as that account, see what error message you get! It tells you that the account even exists at all, and thus, they have confirmed that at least one thing they are doing is right. The best error message to return to a potential hacker is none at all, no clues whatsoever. Do not lock the accounts; there is a better way to prevent logons to the owning schema.
See the video for context.
Schema only Accounts
Starting with Oracle 18c, we can create schema-only accounts in Oracle. This is a type of account that can be created with no password or authentication method specified, and thus, a user (or other client) cannot log in to the database using that account unless the account is modified to accept an authentication method. However, this type of user can proxy in a single session proxy(to be demonstrated later). This is the ideal type of user account for owning schemas. The authentication_type column in the dba_users data dictionary view verifies if the account is schema only or not(Value set to NONE). These accounts can then be used to create objects such as tables, procedures, and the like, provided they have been granted the necessary privileges. They can be used in a single-session proxy, but cannot connect through database links.
Now that we know about this feature and why we should use it, let’s see how to use it.
Usage
Configuring Schema-only Accounts
Create a new account
To create an account as Schema only, use the NO AUTHENTICATION clause. This can only be done on a database instance, not on an ASM instance.
SQL> CREATE USER fh_owner NO AUTHENTICATION ;
User FH_OWNER created.
Alter an existing account.
Additionally, existing accounts can be converted to Schema-only accounts using the ALTER USER command. The existing user must have no admin privileges.
SQL>
SQL> CREATE USER fh_owner_2 IDENTIFIED BY laketrout;
User FH_OWNER_2 created.
SQL> GRANT CREATE SESSION, CREATE TABLE TO fh_owner_2;
Grant succeeded.
SQL> CONN fh_owner_2/laketrout;
Connected.
SQL>
SQL>
SQL> CREATE TABLE t1 (
2 c1 NUMBER
3* );
Table T1 created.
...
SQL>
SQL> ALTER USER fh_owner_2 ACCOUNT LOCK;
User FH_OWNER_2 altered.
SQL>
SQL> CONN fh_owner_2/laketrout;
Connection failed
USER = fh_owner_2
URL = jdbc:oracle:oci8:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREEPDB1) ) )
Error Message = ORA-28000: The account is locked; login denied.
Help: https://docs.oracle.com/error-help/db/ora-28000/
...
SQL>
SQL> ALTER USER fh_owner_2 ACCOUNT UNLOCK;
User FH_OWNER_2 altered.
SQL>
SQL>
SQL>
ASQL> ALTER USER fh_owner_2 NO AUTHENTICATION ;
User FH_OWNER_2 altered.
SQL>
SQL>
SQL> CONN fh_owner_2/laketrout;
Connection failed
USER = fh_owner_2
URL = jdbc:oracle:oci8:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREEPDB1) ) )
Error Message = ORA-01017: invalid credential or not authorized; logon denied
Help: https://docs.oracle.com/error-help/db/ora-01017/
As seen above, the created account is initially able to log on and create objects. When locked, the ability to log on is disabled despite having the create session privilege, and the error message from the attempted logon confirms the account exists. When the account is unlocked and set to schema only using the NO AUTHENTICATION clause, logins are not possible, and the error message is different, not confirming specifically that the account exists, just says invalid credential or not authorized; logon denied.
What About Objects?
At this point, you might be thinking the default way to go is to create a regular account, create objects inside it, and then alter it to schema-only (as shown in the example above) to be able to create objects inside the Schema. Well, not exactly, you can create objects inside a schema only account using a DBA or similar privileged account by making use of the <schema_name>.<object_name> syntax, or alternatively, using a proxy connection.
<schema_name>.<owner_name> syntax
To use this method, from a DBA or similar privileged user, specify the owning schema name and the object name. You can then grant privileges on those objects to application users and create synonyms for them that reference the owning schema’s account. The app user can then log on, reference, and manipulate those objects as if they were their own. The example below demonstrates this.
SQL>
SQL> show user
USER is "SYS"
SQL>
SQL> ALTER USER fh_owner QUOTA 100M ON users;
User altered.
SQL>
SQL> CREATE TABLE fh_owner.t1 (
2 c1 NUMBER
3 );
Table created.
SQL>
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON fh_owner.t1 TO HR;
Grant succeeded.
SQL> CREATE SYNONYM hr.t1 FOR fh_owner.t1;
Synonym created.
SQL>
SQL>
SQL> CONN hr/oracle
Connected.
SQL>
SQL>
SQL> DESC t1
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 NUMBER
SQL>
SQL> INSERT INTO t1
2 SELECT rownum * 2
3 CONNECT BY LEVEL <= 10;
10 rows created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> SELECT *
2 FROM t1;
C1
----------
2
4
6
8
10
12
14
16
18
20
10 rows selected.
SQL>
The method above is my preferred setup (see other setups here), including a utopian ideal. Additionally, while simplified for this example, grant object privileges to application user accounts via roles.
The application user (HR in the example above) can connect to the database and run DML on objects owned by the schema owner as if they were their own, but they’re not. Also note that the use of the conn <username>/<password> syntax is for ease of writing this blog and examples. It is not advisable to use it in your stored scripts or in a production environment.
Proxy connection
This method allows a single-way proxy connection to the schema-only account. See an example:
SQL> ALTER USER fh_owner GRANT CONNECT THROUGH hr;
User altered.
SQL> GRANT CREATE SESSION TO fh_owner;
Grant succeeded.
SQL>
SQL>
SQL> CONN HR[fh_owner]/oracle
Connected.
SQL>
SQL>
SQL> show user
USER is "FH_OWNER"
SQL>
SQL>
SQL>
SQL> CREATE TABLE T2 (
2 c2 NUMBER
3 );
Table created.Notice the ALTER USER...GRANT CONNECT THROUGH A command that is required for this to work. As shown, with the proxy connection, objects can then be created inside the owning schema.
NB: I don’t prefer this as it means logging in to the account is still possible. However, that’s just me!
Disabling a schema only Account
Schema-only accounts can be converted back to regular accounts. To do this, simply alter the account using the IDENTIFIED BY clause and provide it with a password.
SQL>
SQL> ALTER USER fh_owner_2 IDENTIFIED BY laketrout;
User altered.
SQL>
SQL>
SQL>
SQL> CONN fh_owner_2/laketrout;
Connected.
SQL>
SQL>
SQL>
Viewing Schema Only Accounts
Schema-only accounts can be viewed using the dba_users view
SQL> col username for a20
SQL>
SQL>
SQL> l
1 SELECT
2 username
3 ,account_status
4 ,authentication_type
5 FROM
6 dba_users
7 WHERE
8* username LIKE '%OWNER%'
SQL> /
USERNAME ACCOUNT_STATUS AUTHENTI
-------------------- -------------------------------- --------
FH_OWNER OPEN NONE
FH_OWNER_2 OPEN PASSWORD
The value ‘NONE‘ in the last column indicates the account is schema-only.
CONCLUSION
Hopefully, in your environment, there is at least some separation between owning schemas and user schemas; if not, as was the case for me a couple of years ago, hopefully you have learned something new today. May the lesson stick with you as much as it stuck with me, and you actually put it into practice. Make sure you go one better by using Schema-only accounts. Most importantly, when designing applications that run on an Oracle database or any system, security should be given top priority and considered from the outset. Properly securing your applications and database environment can prevent you from living a horror story yourself!
Cheers 🎊