Background
Story time
When I just started Working as a DBA, the Senior Database Administrator was taking me over the environment and how things are set up with all the servers, databases, applications and all that. He was talking about one of the purchased applications that run on production and then explained that the vendor had everything set up almost perfectly with regards to security. He explained, 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 (matter of 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, do DML on them but should not be able to make structural changes(Create, ALTER, DROP,TRUNCATE etc.). I try to stay abreast of stuff about databases; New features, Best practices, DBA experiences, Database stories stuff like that in general. 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 a separate one for the application to connect as. 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 owning all objects that has login credentials and can be brute force hacked or other attacks. To mitigate these, Sometimes people go the extra mile of not only separating the accounts, but also locking 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 on 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.
Schema only Accounts
Starting from Oracle 18c, We have the ability to 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 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 to be used 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 like tables, procedures and the like granted they have been given the right 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 and not 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 logon and create objects, when locked the ability to logon 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 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 create a regular account, create objects inside it and then alter it to schema only(as shown in the example above) in order 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 the application users and create for it synonyms on those objects that will reference the owning schema’s account. The app user can then logon, reference and manipulate those objects as if they were its own. The below example 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. This is not advisable to put in your stored scripts or use in an actual work 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 command which 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 at 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 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 a schema only account.
CONCLUSION
Hopefully in your environment there is at least some separation between owning schemas and user schemas if not, then like 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 in to practice. Make sure you go one better by using Schema only accounts. Most Importantly, whenever designing applications that run on an oracle database or any system, the security should be given top priority and taken into consideration right at the design phase. Properly securing your applications and database environment can prevent you from living a horror story yourself!
Cheers 🎊