Locking User's Account in Oracle

To manage user accounts in Oracle Database 19c, you can lock a user's account or temporarily disable their ability to log in. These actions are often required for security reasons, maintenance, or compliance with organizational policies. Below are detailed steps for both processes:
Locking a User's Account:
Locking a user's account immediately prevents the user from logging into the database. The account remains locked until it is explicitly unlocked.
Steps to Lock a User's Account:

1. Connect with Administrative Privileges: You need to connect to the database as a user with the necessary administrative privileges, such as `SYSDBA` or a user with the `ALTER USER` system privilege.
   

   sqlplus / as sysdba
   

   
   or
   

   sqlplus admin_username/admin_password@dbname
   

2. Execute the `ALTER USER` Command**
   Use the `ALTER USER` statement with the `ACCOUNT LOCK` clause to lock the user account.
   Syntax:
   

   ALTER USER username ACCOUNT LOCK;
   

   
   Example: To lock the account of a user named `john_doe`:
   

   ALTER USER john_doe ACCOUNT LOCK;
   

3. Verify the Account Status (Optional)
   You can confirm that the account is locked by querying the `DBA_USERS` view.
   

   SELECT username, account_status FROM dba_users WHERE username = 'JOHN_DOE';
   

   
   The `ACCOUNT_STATUS` column should display `LOCKED`.

If you need to prevent a user from logging in temporarily without permanently altering their account status, you have a few options:

1. Option A: Lock and Unlock the Account**
   Lock the account when you need to disable access and unlock it when access should be restored.
   To Lock the Account:
   

   ALTER USER john_doe ACCOUNT LOCK;
   

   
   To Unlock the Account Later:
   

   ALTER USER john_doe ACCOUNT UNLOCK;
   

2. Option B: Set an Expiry Date
   Set the user's account to expire, preventing login until the account is reactivated.
   Syntax:
   

   ALTER USER username PASSWORD EXPIRE;
   

   
   Example:
   

   ALTER USER john_doe PASSWORD EXPIRE;
   

   
   This forces the user to change their password upon next login, effectively disabling access until the password is reset.
3. Option C: Assign a Restrictive Profile
   Create a profile that limits login capabilities and assign it to the user.
   1. Step 1: Create a Profile with Restrictions
      

      CREATE PROFILE temp_disable LIMIT
        SESSIONS_PER_USER 0;
      

   2. Step 2: Assign the Profile to the User
      

      ALTER USER john_doe PROFILE temp_disable;
      

   3. Step 3: Restore the Original Profile When Ready
      

      ALTER USER john_doe PROFILE default;
      

Unlocking the User's Account
When it's time to restore the user's access, you can unlock their account using the `ACCOUNT UNLOCK` clause.
Syntax:

ALTER USER username ACCOUNT UNLOCK;

Example:

ALTER USER john_doe ACCOUNT UNLOCK;

• Active Sessions: Locking an account does not terminate existing sessions. To disconnect active sessions, use the `ALTER SYSTEM KILL SESSION` command.
  Example:
  

  SELECT sid, serial#, status FROM v$session 
  WHERE username = 'JOHN_DOE';
  

  
  Then kill the session:
  

  ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
  

• Auditing: Enable auditing to track account lock and unlock activities for compliance purposes.
  Example:
  

  AUDIT ALTER USER;
  

• Security Policies:
  Ensure that locking accounts complies with your organization's security policies and procedures.

You can check the status of user accounts and other relevant information by querying the `DBA_USERS` view.
Example:

SELECT
  username,
  account_status,
  lock_date,
  expiry_date,
  profile
FROM
  dba_users
WHERE
  username = 'JOHN_DOE';

• Lock a User's Account:
  

  ALTER USER username ACCOUNT LOCK;
  

• Unlock a User's Account:
  

  ALTER USER username ACCOUNT UNLOCK;
  

• Temporarily Disable Login by Expiring Password:
  

  ALTER USER username PASSWORD EXPIRE;
  

• Temporarily Disable Login with a Profile:
  • Create a restrictive profile.
  • Assign the profile to the user.
  • Reassign the original profile when ready.

Note: Always exercise caution when modifying user accounts to prevent unintended access issues. It's good practice to inform users before locking their accounts unless it's for security reasons.

If you need to temporarily suspend a user's access to the database, you can do that by locking the account. Later, when you want to allow the user in again, you can unlock the account. The advantage of locking accounts, as opposed to other methods of temporarily denying access, is that everything about the accounts remains untouched. The passwords remain the same. The users do not have to be dropped, and their privileges remain the same. The command to lock a user's account is:

ALTER USER username ACCOUNT LOCK;

Once locked, a user will not be able to log in again until you unlock the account with the following command:

          
ALTER USER username ACCOUNT UNLOCK;

Accounts also can become locked if you are enforcing a password change interval. When a user does not change a password within the grace period, the user's account will be locked automatically.

Database administrators can use the DBA_NETWORK_ACL_PRIVILEGES data dictionary view to query network privileges that have been granted to or denied from database users and roles in the access control lists, and whether those privileges take effect during certain times only. Using the information provided by the view, you may need to combine the data to determine if a user is granted the privilege at the current time, the roles the user has, the order of the access control entries, and so on. To simplify this privilege evaluation, you can use the following DBMS_NETWORK_ACL_ADMIN functions to check the privilege granted to a user in an access control list:

1. CHECK_PRIVILEGE: Checks if the specified privilege is granted to or denied from the specified user in an access control list. This procedure identifies the access control list by its path in the XML DB Repository.
   Use CHECK_PRIVILEGE if you want to evaluate a single access control list with a known path.
2. CHECK_PRIVILEGE_ACLID: Similar to the CHECK_PRIVILEGE procedure, except that it enables you to specify the object ID of the access control list. Use CHECK_PRIVILEGE_ACLID if you need to evaluate multiple access control lists, when you query the DBA_NETWORK_ACLS data dictionary view. For better performance, call CHECK_PRIVILEGE_ACLID on multiple access control lists rather than using CHECK_PRIVILEGE on each one individually.

Users without database administrator privileges do not have the privilege to access the access control lists or to invoke those DBMS_NETWORK_ACL_ADMIN functions. However, they can query the USER_NETWORK_ACL_PRIVILEGES data dictionary view to check their privileges instead.
Both database administrators and users can use the following DBMS_NETWORK_ACL_UTILITY functions to generate the list of domains or IP subnet a host belongs to and to sort the access control lists by their order of precedence according to their host assignments:

1. DOMAINS: Returns a list of the domains or IP subnets whose access control lists may affect permissions to a specified network host, subdomain, or IP subnet
2. DOMAIN_LEVEL: Returns the domain level of a given host