In Oracle database, can I track logins of different individuals who access the same schema?-CodePudding

My organisation has a number of schemas within an Oracle database that need to be accessed by a number of individual developers. In most cases, the developers can login with their own id and access these other schemas through role permissions, but for certain tasks the individual developers need to log into these schemas directly.

An example of this would be schema A which needs to be accessed by users X and Y. User X and user Y can log in with their own ids, but to fully leverage the potential of schema A, they would need the schema A password.

I am interested in tracking if and when user Y for example logs into schema A, using the schema A password. Can this be done?

My need for this is primarily in relation to password security and leaving procedures when staff depart our organisation. For example, if I have schema A and the password is known by at least some of users X, Y, Z and Q, and user Q leaves, is this a threast? If the checks show the password for schema A was not known by user Q, then I don't have a security risk, but if the checks show that user Q knew the password for schema A, then I need to change the password for schema A, potentially impacting users X, Y and Z.

Therefore, my question is, is there a method within Oracle that would enable me as DBA to identify which individuals logged into these schemas, whether it be by tracking their client identifiers or some other route?

Thanks

CodePudding user response：

For this specific scenario, use proxy authentication: it kills several problems related to development environments in a single stroke.

[Proxy authentication] allows a user to connect to a database as one user and on connection become a different user. This capability was originally deployed by Oracle as a way for applications to authenticate end users to individual database accounts through a common application account, but it works just as effectively the other way around.

In this model, the application schema is often locked down so that it cannot be accessed directly, then each developer is given a separate, personal account in the database. Most application development can now be handled – and audited – through these personal accounts. Because the login accounts are associated with individual users there is no incentive for the developer to share their credentials with anyone else. It would be relatively simple to tell if a personal account was being shared, and doing so would be grounds for termination with most companies.

To demonstrate this in action, I’ll create an application schema and configure it so that it can only be accessed by means of proxy authentication:
Connected to: Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production Version 19.3.0.0.0

SQL> create user app_schema no authentication proxy only connect;

User created.

SQL> grant connect, resource to app_schema;

Grant succeeded. 
The “no authentication” option creates the shared account without credentials. This eliminates the need to maintain a password or certificate for an account that will never be used directly. The “proxy only connect” option allows only proxy connections to the shared account. The “connect” and “resource” roles grant basic privileges to use the account and create objects. Next, I will create a sample development user:
SQL> create user dev_user identified by oracle;

User created. 
Now the application schema can be altered to allow connections from individual developer accounts, like this:
alter user app_owner grant connect through dev_user; 
Note the syntax: the APP_OWNER schema is altered to allow connection by or through the DEV_USER account. This is not a privilege that is assigned directly to the development user. Once this proxy privilege has been assigned, the developer can connect to the application schema using their personal credentials. All they need do is append the application account name in brackets to the end of their development account name, like this:
SQL> connect dev_user[app_schema]/oracle Connected. 
SQL> show user;
USER is "APP_SCHEMA" 
By connecting in this way, the developer can still perform needed actions but need never be aware of the application owner account’s real password (assuming one was even assigned). The proxy account name (the developer’s personal account) is available in the system session context, and can be automatically made visible in the v$session view through a database trigger so that the DBA can tell who is connected to shared accounts at all times.
CREATE OR REPLACE TRIGGER db_session_trig  
AFTER LOGON ON DATABASE  
v_proxy_user varchar2; 
BEGIN
  v_proxy_user := sys_context('userenv','proxy_user');
  if v_proxy_user is not null then
    dbms_session.set_identifier(v_proxy_user);
  end if; 
END; 

select username, osuser, client_identifier from v$session where username='APEX_040000';

USERNAME          OSUSER          CLIENT_IDENTIFIER 
----------------- --------------- ---------------------- 
APEX_040000       oracle          PETE 
Using the PROXY_USERS view it is easy to determine exactly which developers have access to each application owner account as well.
PROXY      CLIENT          AUTHENTICATION FLAGS 
---------- --------------- -------------- ----------------------------------- 
PETE       APEX_040000     NO             PROXY MAY ACTIVATE ALL CLIENT ROLES  
Because even basic auditing captures the OS username of the developer, the audit trail will record the actual developer behind DDL operations executed as the application owner.
Sep 4 10:04:07 testdb Oracle Audit: SESSIONID: "12345" ENTRYID: "1"
STATEMENT: "6" USERID: "APP_SCHEMA" USERHOST: "myserver" TERMINAL:
"pts/2" ACTION: "7" RETURNCODE: "0" OBJ$CREATOR: "APP_SCHEMA"
OBJ$NAME: "TEST_TABLE" SES$TID "4567" OS$USERID: "PETE" 
Using individual developer user accounts with proxy account access to application schemas, it is possible to allow developers to work in shared accounts while still maintaining account credential security, visibility of connected users, and an accurate audit history.

Full article here: https://pmdba.wordpress.com/2021/10/15/shared-application-accounts-revisited/

CodePudding user response：

You could use proxy users for this. That way there is no need to share a password and regular auditing can do its job.

An other option could be to define packages in the other schemas that can be called by the developers. The packages need to be defined with definers rights so when a dev calls the package, the execution is done using the privileges that are directly granted to the schema. Doing so avoids nasty ‘any’ privileges.