OracleAudit

These are queries to help assist with retrieving users inside of a SQL instance and the permissions that those users are assigned. These queries are designed to only read data and will not modify or create data inside of the database.

Oracle - Version Check

The following queries may assist you in determining your version of Oracle database if you are unsure. The version of the database will impact the queries you will need to run for "Users and Account Status", listed further below.

SELECT * 
FROM v$version

SELECT * 
FROM product_component_version

Password Settings

The following query will show the password settings for all profiles in the database.

SELECT *
FROM DBA_PROFILES
WHERE RESOURCE_TYPE = 'PASSWORD'
ORDER BY PROFILE

Oracle - Users and Account Status

The following queries have been designed to NOT pull password hashes out of the DBA_USERS table. Due to differences in table structure between Oracle versions please run the query that is appropriate to your environment.

ORACLE 12c Release 2 (12.2.0.1) and newer

SELECT USERNAME, USER_ID, PROFILE, ACCOUNT_STATUS, LOCK_DATE,
    EXPIRY_DATE, CREATED, EXTERNAL_NAME, PASSWORD_VERSIONS,
    EDITIONS_ENABLED, AUTHENTICATION_TYPE, PROXY_ONLY_CONNECT,
    COMMON, LAST_LOGIN, ORACLE_MAINTAINED, INHERITED, IMPLICIT
FROM DBA_USERS

ORACLE 12 less than 12c Release 2 (12.2.0.1)

SELECT USERNAME, USER_ID, PROFILE, ACCOUNT_STATUS, LOCK_DATE,
    EXPIRY_DATE, CREATED, EXTERNAL_NAME, PASSWORD_VERSIONS,
    EDITIONS_ENABLED, AUTHENTICATION_TYPE, PROXY_ONLY_CONNECT,
    COMMON, LAST_LOGIN, ORACLE_MAINTAINED
FROM DBA_USERS

ORACLE 11

SELECT USERNAME, USER_ID, PROFILE, ACCOUNT_STATUS, LOCK_DATE,
    EXPIRY_DATE, CREATED, EXTERNAL_NAME, PASSWORD_VERSIONS,
    EDITIONS_ENABLED, AUTHENTICATION_TYPE
FROM DBA_USERS

ORACLE 10

SELECT USERNAME, USER_ID, PROFILE, ACCOUNT_STATUS, LOCK_DATE,
    EXPIRY_DATE, CREATED, EXTERNAL_NAME
FROM DBA_USERS

ORACLE 9

SELECT USERNAME, USER_ID,PROFILE, ACCOUNT_STATUS, LOCK_DATE,
    EXPIRY_DATE, CREATED, EXTERNAL_NAME
FROM DBA_USERS

Oracle - System Privilege Grants

The following query determines what System Privileges allow the user to perform system level activities.

SELECT *
FROM DBA_SYS_PRIVS

Oracle - Role Grants

The following query lists the roles granted to all users and roles in the database.

SELECT *
FROM DBA_ROLE_PRIVS

Oracle - Roles

The following query lists all roles that exist in the database.

SELECT *
FROM DBA_ROLES

Oracle - Users Granted SYSDBA or SYSOPER

The following query determines what users are granted SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM privileges.

SELECT *
FROM V$PWFILE_USERS