Users & Privileges
How to get the CREATE USER DDL for all users in Oracle
Extract the basic CREATE USER DDL for every Oracle user using DBMS_METADATA and spool it to a file.
How to get the full DDL for a user in Oracle
Extract the CREATE USER statement plus tablespace quotas, role grants, system privileges, object privileges, default role, and profile for a given user using DBMS_METADATA.
How to check password expiry and profile limits in Oracle
Check Oracle password expiry and profile limits: failed logins, password life, reuse, lock time, and days to expiry.
How to list all users in an Oracle database
List every account in an Oracle database from DBA_USERS: status, tablespaces, profile, and key dates.
How to check a user's privileges in Oracle (system, object, roles)
See a user's full privileges in Oracle: roles, system privileges, and object privileges, granted directly or through roles.
How to find roles granted to a user in Oracle
Show the roles granted to an Oracle user, including nested role-to-role grants, as an indented tree.
How to check object (table) privileges in Oracle
List the object and table privileges granted to an Oracle user, with grantor and whether the grant is grantable.
How to grant privileges to a user in Oracle
Grant system, object, or role privileges to an Oracle user, then verify what was granted.
How to create a user in Oracle
Create an Oracle user with a default tablespace, quota, and CREATE SESSION, following least privilege.
How to reset a user password in Oracle
Reset an Oracle user's password with ALTER USER IDENTIFIED BY, with an option to force a change at next login.
How to unlock a user account in Oracle
Unlock a locked Oracle user account with ALTER USER ACCOUNT UNLOCK, then verify the account status.
How to check accounts with default passwords in Oracle
Find Oracle accounts still using a known default password with DBA_USERS_WITH_DEFPWD. Oracle 11g and later.
How to find who has the DBA role in Oracle
Find who holds the DBA role and other powerful system privileges in an Oracle database, for a security review.
How to find locked and expired accounts in Oracle
Find locked, expired, and other non-open Oracle accounts that need attention, with lock and expiry dates.