Locking User's Account in Oracle

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