5 Things To Consider When Dropping Database User Accounts

Dropping users in the database is a common task in the life of a DBA. Before jumping into executing the drop command, I would like you to consider a few things. Why do you drop the user? What accounts are you dealing with? What are the risks associated with the process? How can you recover?
More accurate your answers, better your process and plan is.

What questions does an AWESOME DBA ask when dropping a database user?

1. What event would trigger a drop user request?

– user leaves the company
– an application is retired
– database feature no longer in use
– audit process identifies old unused accounts

2. What type of users are in the database?

There are many ways to categories users in the database. These are three categories I use:
– System Accounts
– Schema and Application Accounts
– Named Database User Accounts

Lets look in detail to each category, to get a better understanding.

System Accounts:

These users are the internal database users that come with the database installation, and different database features or options.
These accounts do not usually run any user applications. Some of these accounts could be locked and expired. Examples: sys, system, sysman, dbsnmp.

Schema and Application Accounts:

These users own database objects such as tables, views, procedure, functions. These users are behind the applications that run in the database. Example of such users would be SYSADM for Peoplesoft, HR for the HR Application.
Other accounts that could be included here are application accounts that do not own objects, however have full access to the ojects of other schemas, and are used in reports, webserver connections (WebLogic, Crystal Reports).Examples: HRAPP, SYSADM_APP.

Named Database User Accounts:

These users are your business user accounts, developer accounts, DBA accounts. These users do not own any objects, with some exceptions (developers could own object in the dev/test environment where they develop code). Examples: drobete, dianar, jsmith.

3. What are the risks of dropping the user accounts in the database?

Depending on the type of user to drop, here are some of the risks associated with the process (I am sure there are more):
– dropping a System User could cause database corruption, or compromise of database functionality
– dropping a Schema User could cause database outage, application corruption, disruption of service
– dropping an Application User could cause application outage, disruption of service, compromise of application functionality
– dropping a Named User could compromise application functionality, even application outage, if this user was used in scripts, reports, queries, or was used to run certain features of the application (and nobody new about it).

4. Other questions to ask yourself prior to dropping the user:

– what type of user am I dealing with? (system, schema/application, named account)
– who identified the user can be dropped? (ticket from Helpdesk, Audit process)
– how was identified that the account is no longer needed or in use? (This is very tricky, especially if you are dealing with schema/app accounts, as people might be unaware that an account is used)

5. What is the rollback strategy?

How can I quickly recover in case the user was dropped in error? Trust me this actually can happen. An account is dropped and later is identified that the account is used by a report, that no longer works.

I am a big fan of rollback strategies, so this is an important question to answer, and map out a plan or process to recover.

After you answer these questions, you need a process for dropping users. Follow me next Thursday, for the Blueprint, a step by step process I follow, to drop database user accounts.

-Diana