Shared developer account, expiring batch password, and a 02:40 lockout

Locking does not need a human: FAILED_LOGIN_ATTEMPTS counts authentication failures, and the usual source at 02:40 is a scheduled job or a revived pool retrying an outdated credential. The classic chain here is a password that expired under PASSWORD_LIFE_TIME 60 (grace 0, so the first attempt gets ORA-28001), the client treats it as transient and retries, five failures lock the account (ORA-28000). Because the lock is on the account, the developers are then blocked too.

Evidence:

SELECT username, account_status, lock_date, expiry_date
FROM   dba_users WHERE username = 'APP_DEV';

SELECT resource_name, limit FROM dba_profiles
WHERE  profile = 'DEV_PROFILE' ORDER BY resource_name;

SELECT event_timestamp, userhost, action_name, return_code, os_username
FROM   unified_audit_trail
WHERE  action_name = 'LOGON'
AND    return_code IN (1017, 28000, 28001)
ORDER  BY event_timestamp DESC;

USERHOST normally names the job server immediately; cross-check the CRON/WMS logs from 02:40 and the listener log for the source address. Unlock (ALTER USER app_dev ACCOUNT UNLOCK) only after you have stopped the retry loop, or it relocks within minutes and you lose the evidence.

Two separate defects: a human password policy applied to a non-interactive account, and a secret on disk.

1. Separate profile. CREATE PROFILE batch_profile LIMIT PASSWORD_LIFE_TIME UNLIMITED FAILED_LOGIN_ATTEMPTS 10; then ALTER USER fin_batch PROFILE batch_profile;. This is the correct use of profiles - policy follows the nature of the account - and it leaves DEV_PROFILE untouched.
2. Remove the file secret. Configure a Secure External Password Store on the batch host (orapki wallet create ..., mkstore -wrl ... -createCredential ORCL fin_batch) so the interface connects through a wallet-based connect identifier with no password in its configuration. The vendor changes a connect string, not a password. If the batch host is tightly controlled, OS authentication (IDENTIFIED EXTERNALLY with os_authent_prefix) removes the password entirely.
3. Compensate for the unlimited lifetime: least privilege for FIN_BATCH, a network ACL limiting it to the batch subnet, and auditing of its logons and DML.

Never solve this by setting PASSWORD_LIFE_TIME UNLIMITED in DEFAULT - that silently disables expiry for every human account in the database.

CREATE ROLE fin_read;
GRANT SELECT ON fin_app.pay_detail TO fin_read;   -- and the other read-only objects
CREATE USER jdoe IDENTIFIED BY ... DEFAULT TABLESPACE users PROFILE dev_profile;
GRANT CREATE SESSION, fin_read TO jdoe;

Not granted: SELECT ANY TABLE, CREATE ANY PROCEDURE, INSERT/UPDATE/DELETE/ALTER on production objects, GRANT ... WITH ADMIN OPTION, DBA, and no direct object grants to individuals - roles keep the model consistent and reviewable via ROLE_TAB_PRIVS. APP_DEV's DDL rights move to a deployment account that nobody uses interactively. Make fin_read a NON-DEFAULT role if you want an explicit SET ROLE fin_read step as friction before production reads, and consider exposing read-only access through an Active Data Guard standby rather than the primary.

The surprise: privileges granted through a role are not enabled inside definer's-rights PL/SQL. A procedure owned by FIN_APP runs with FIN_APP's privileges, so the role is irrelevant there - and conversely, code that compiles fine interactively can fail when executed through a procedure. Roles are also enabled at logon, so a non-default role must be set per session.

Finally, attribute privileged activity: create a unified audit policy for the deployment account and for administrative actions, and audit SYSDBA usage, so the next audit answer is a name, not a shared account.