Security

How to list all roles and privileges granted to a user in Oracle

Show every role, system privilege, and object privilege granted to one account, from DBA_ROLE_PRIVS, DBA_SYS_PRIVS, and DBA_TAB_PRIVS.

Revoke a system privilege from a user or role, after checking the current grant, then verify it is gone.

Create a Unified Auditing policy with CREATE AUDIT POLICY and turn it on with AUDIT POLICY, then verify it is enabled.

List what TDE actually protects: encrypted tablespaces (V$ENCRYPTED_TABLESPACES) and encrypted columns (DBA_ENCRYPTED_COLUMNS).

Check the TDE keystore (wallet): whether it is open, its location, and type, from V$ENCRYPTION_WALLET.

Show the traditional (pre-Unified) audit configuration: statement, privilege, and object audit options.

List which Unified Auditing policies are currently enabled and for whom, from AUDIT_UNIFIED_ENABLED_POLICIES.

Read recent activity from UNIFIED_AUDIT_TRAIL: who did what, from which host, and whether it succeeded.

Check whether Unified Auditing is linked into the instance using V$OPTION, which decides where audit records are written.

Review password and lockout limits for each profile (failed-login lockout, password lifetime, reuse, complexity) from DBA_PROFILES.

Find accounts still using a known default password with the built-in DBA_USERS_WITH_DEFPWD view, joined to account status.

List every database link, its owner, the connect user, and the target host from DBA_DB_LINKS for a security review.

Hunt for object privileges granted to PUBLIC, a common over-permission, using DBA_TAB_PRIVS filtered to grantee PUBLIC.

Find accounts holding the DBA role, SYSDBA/SYSOPER, or sweeping ANY privileges, using DBA_ROLE_PRIVS, V$PWFILE_USERS, and DBA_SYS_PRIVS.

Query Oracle SYS.AUD$ audit trail: list audit action codes, filter by ACTION# and user, and get SQL text from DBA_AUDIT_TRAIL.