In Oracle Database administration, which SQL statements correctly lock and unlock a user account such as SCOTT?

Introduction / Context:
Database security and account management are central tasks for Oracle administrators. Sometimes a user account needs to be temporarily disabled, for example when an employee leaves or when security policies require locking an account after repeated failed login attempts. Oracle provides specific SQL commands to lock and unlock accounts. This question asks you to identify the correct syntax for locking and unlocking a user such as SCOTT.

Given Data / Assumptions:

• We are using Oracle Database.
• We want to lock an existing user account named scott.
• We later want to unlock the same account.
• We have the necessary administrative privileges to alter user accounts.

Concept / Approach:
In Oracle, account status is controlled through the ALTER USER command. To lock an account, the DBA issues ALTER USER username ACCOUNT LOCK. To unlock it, the DBA issues ALTER USER username ACCOUNT UNLOCK. This updates the account status in the data dictionary and prevents or allows logins accordingly. A correct answer must show this exact syntax or its equivalent. Other verbs such as LOCK USER or DISABLE USER are not recognized by Oracle, and ALTER SYSTEM is used for system wide settings rather than individual account control.

Step-by-Step Solution:
Step 1: Recall the standard Oracle syntax: ALTER USER scott ACCOUNT LOCK; and ALTER USER scott ACCOUNT UNLOCK; Step 2: Examine Option A, which uses exactly this syntax. It aligns with Oracle documentation and real world practice. Step 3: Examine Option B, which uses LOCK USER and UNLOCK USER; these are not valid SQL statements in Oracle. Step 4: Examine Option C, which uses DISABLE USER and ENABLE USER; again, these are not Oracle commands. Step 5: Examine Option D, which uses ALTER SYSTEM; this statement controls parameters and system behaviors, not individual account lock status. Step 6: Examine Option E, which creates and drops users rather than locking and unlocking them; this is not what the question asks for. Step 7: Conclude that Option A is the only correct pair of statements.

Verification / Alternative check:
If you connect as a DBA and run ALTER USER scott ACCOUNT LOCK; and then query DBA_USERS, you will see that the ACCOUNT_STATUS column for SCOTT changes to LOCKED or a related state. Running ALTER USER scott ACCOUNT UNLOCK; returns the status to OPEN, and SCOTT can log in again. Attempts to run the commands shown in the other options generate syntax errors, which confirms that they are not valid Oracle statements.

Why Other Options Are Wrong:

• Option B is wrong because Oracle does not support bare LOCK USER or UNLOCK USER commands.
• Option C is wrong because DISABLE USER and ENABLE USER are not recognized SQL statements in Oracle.
• Option D is wrong because ALTER SYSTEM operates at the instance level rather than on individual user accounts.
• Option E is wrong because creating and dropping users is a different operation and deletes user objects rather than temporarily disabling logins.

Common Pitfalls:
One pitfall is to drop a user instead of locking it, which can lead to loss of objects if the user owns tables or other schema objects. Another pitfall is forgetting to unlock an account after maintenance or security investigations, leading to user frustration and support tickets. Administrators should document when and why accounts are locked and have a clear process for unlocking them once the issue is resolved.

Final Answer:
The correct syntax is Option A: ALTER USER scott ACCOUNT LOCK; and ALTER USER scott ACCOUNT UNLOCK;