Dropping Users- Users and Basic Security

Before you drop a user, it is recommended that you first lock the user. Locking the user prevents others from connecting to a locked database account. This allows you to better determine whether someone is using the account before it is dropped. Here is an example of locking a user:

 
Any user or application attempting to connect to this user now receives the following error:

To view the users and lock dates in your database, issue this query:
 

To unlock an account, issue this command:

Locking users is a handy technique for securing your database and discovering which users are active.

Be aware that by locking a user, you are not locking access to a user’s objects. For instance, if a USER_A has select, insert, update, and delete privileges on tables owned by USER_B, if you lock the USER_B account, USER_A can still issue DML statements against the objects owned by USER_B. Objects are audited to see if they are being used. More on auditing of objects in Chapter 20.

It is worth checking to see if there is a valid backup of the objects for the user or take a quick backup of the objects. A user cannot be dropped if they still own objects in the database.

This is why it is important that application objects are put in a different schema instead of creating all of the objects under an individual account. If an application is being decommissioned, then backups and retention policies should also be considered.

After you are sure that a user and its objects are not needed, use the DROP USER statement to remove a database account. This example drops the user hsolodba:

The prior command won’t work if the user owns any database objects. Use the CASCADE clause to remove a user and have its objects dropped:

The DROP USER statement may take a great deal of time to execute if the user being dropped owns a vast number of database objects. In these situations, you may want to consider dropping the user’s objects before dropping the user.