Prohibit the sharing of application account passwords. Easier said than done… especially when one person holds all the passwords. In many environments, any sensitive action ultimately depends on the DBA.
Result: we wait for the DBA to unlock an application agent in the middle of the night; we cannot proceed with a simple operation without him. All this increases operational pressure and bad practices become established. This poses a problem for access architecture.
CURRENT SOLUTION
In many environments, we observe:
sharing the application account password between several people
In the event of an error, incident or fraudulent access, it becomes impossible to identify the perpetrator. Changing passwords is complex and risky, as it immediately affects the application.
assigning the DBA role to technical users
The most common solution.
- This gives them full access to the database (data, security, backups).
- A simple handling error can have disastrous consequences, and any compromise becomes catastrophic.
granting excessive rights to user accounts.
Granting global privileges on all application tables is a false solution.
- Users have rights that far exceed their actual needs.
- Adding new tables to the application schema requires reassigning user rights.
These practices result in a total loss of traceability and a major risk to the entire database in the event of a breach.
RECOMMENDATION
Clearly separate roles.
The application account becomes a schema-only account.
- It is only used to carry application objects.
- No direct access is permitted.
SQL> CREATE USER app_data NO AUTHENTICATION;
User created.
SQL> GRANT CREATE SESSION, CREATE TABLE TO app_data ;
Privilege authorisation (GRANT) accepted.
SQL> ALTER USER app_data QUOTA UNLIMITED ON users;
User edits.User access is via Proxy Users.
- Each user logs in with their own personal account;
- The proxy allows users to act on behalf of the application schema without knowing its password.
SQL> CREATE USER tomy IDENTIFIED BY Password_1;
User created.
SQL> GRANT CREATE SESSION TO tomy;
Privilege authorisation (GRANT) accepted.
Identify the different authentication methods.
SQL> select USERNAME, AUTHENTICATION_TYPE from dba_users where username in ('APP_DATA','TOMY');
USERNAME AUTHENTICATION_TYPE
------------------------------ ------------------------------
APP_DATA NONE
TOMY PASSWORDConnection granted via proxy account
SQL> ALTER USER app_data GRANT CONNECT THROUGH tomy;
User edits.Login via proxy account
SQL> CONN tomy[app_data]/Password_1@pad
Connecte.
SQL> col SESSION_USER format a15
SQL> col SESSION_SCHEMA format a15
SQL> col CURRENT_SCHEMA format a15
SQL> col PROXY_USER format a15
SQL> select sys_context('userenv','session_user') as session_user,
2 sys_context('userenv','session_schema') as session_schema,
3 sys_context('userenv','current_schema') as current_schema,
4 sys_context('userenv','proxy_user') as proxy_user
5 from dual;
SESSION_USER SESSION_SCHEMA CURRENT_SCHEMA PROXY_USER
--------------- --------------- --------------- ---------------
APP_DATA APP_DATA APP_DATA TOMY
✨ BONUS
schema-level privileges
Starting with Oracle 23ai, schema-level privileges exist, allowing rights to be assigned to all objects in a schema.
SQL> GRANT SELECT ANY TABLE ON SCHEMA APP_DATA TO TOMY;
Privilege authorisation (GRANT) accepted.
Monitor proxy accounts regularly with the PROXY_USERS view.
SQL> set lines 100
SQL> col proxy format a20
SQL> col client format a20
SQL> col authentication format a20
SQL> col flags format a30
SQL> select * from proxy_users;
PROXY CLIENT AUTHENTICATION FLAGS
-------------------- -------------------- -------------- ------------------------------
TOMY APP_DATA NO PROXY MAY ACTIVATE ALL CLIENT
ROLESDelete proxy accounts when you no longer need them.
SQL> ALTER USER APP_DATA REVOKE CONNECT THROUGH TOMY;
User edits.