2

I'm trying to audit the privileged users on Oracle Db and see there are users for whom the last login column appears to be blank, however, I'm aware that those user accounts are used for certain processes. Hence wanted to understand if there is a way to identify users on the Oracle database who uses OS authentication? and what is the table I should be looking at?

I See the "Authentication Type" for all the users in the DBA_Users table specify "PASSWORD"

2
  • Are there any users whose name has prefix OPS$ (in DBA_USERS)? For example, OPS$SCOTT? They might be the ones you're looking for.
    – Littlefoot
    Sep 22 at 7:51
  • "however, I'm aware that those user accounts are used for certain processes. " stored procedures can be launched with dbms_scheduler, so no logon needed, so no update of 'last_logon'.
    – EdStevens
    Sep 22 at 14:58
3

For currently logged in users:

Check the AUTHENTICATION_TYPE column of V$SESSION_CONNECT_INFO.

AUTHENTICATION_TYPE

VARCHAR2(26)

How the user was authenticated:

DATABASE - Username/password authentication

OS - Operating system external user authentication

NETWORK - Network protocol or ANO authentication

PROXY - OCI proxy connection authentication

SERVER

PASSWORD

EXTERNAL ADAPTERS

INTERNAL

GLOBAL

EXTERNAL

PASSWORD BASED GLOBAL USER

In general, you can check AUTHENTICATION_TYPE column of DBA_USERS:

AUTHENTICATION_TYPE

VARCHAR2(8)

Indicates the authentication mechanism for the user:

NONE - The user has not been configured for an authentication method

EXTERNAL - CREATE USER user1 IDENTIFIED EXTERNALLY;

GLOBAL - CREATE USER user2 IDENTIFIED GLOBALLY;

PASSWORD - CREATE USER user3 IDENTIFIED BY user3;

OS authentication users are defined as EXTERNAL. The prefix can be changed with the parameter os_authent_prefix, but the default value is ops$.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.