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

SELECT INSTANCE_NAME, HOST_NAME, VERSION, STATUS, LOGINS,
    DATABASE_STATUS, INSTANCE_ROLE, CON_ID
FROM V$INSTANCE

Password Settings

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

SELECT PROFILE, RESOURCE_NAME, LIMIT
FROM DBA_PROFILES
WHERE RESOURCE_TYPE = 'PASSWORD'
ORDER BY PROFILE

Oracle - Users and Account Status

The following query has been designed to NOT pull password hashes out of the DBA_USERS table.

SELECT USERNAME, USER_ID, PROFILE, ACCOUNT_STATUS, LOCK_DATE,
    EXPIRY_DATE, LAST_LOGIN, ORACLE_MAINTAINED
FROM DBA_USERS

Oracle - System Privilege Grants

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

SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION
FROM DBA_SYS_PRIVS

Oracle - Role Grants

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

SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION
FROM DBA_ROLE_PRIVS

Oracle - Roles

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

SELECT ROLE, ROLE_ID, PASSWORD_REQUIRED, AUTHENTICATION_TYPE, ORACLE_MAINTAINED
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 USERNAME, SYSDBA, SYSOPER, SYSBACKUP, SYSKM, SYSDG, SYSASM
FROM V$PWFILE_USERS