3 Strategies For Dropping Database User Accounts

Last week I covered the 5 Things To Consider When Dropping Database User Accounts. If you didn’t get a chance to read it, just click on the link, and you can review it now, I will wait for you…

The three categories I like to group the users, are:

-system
-schema and application accounts
-named database accounts.

Different strategies are needed for each category, when dropping users.
In order to have a safe, event-less drop, follow these guidelines. I will discuss each category in detail, always thinking what is my recovery or rollback plan. Remember this: A DBA always has a rollback plan! (no matter what the task is)

Dropping System Accounts:

When:
You drop certain system accounts when a feature of the database is identified as not in use anymore. Some System Accounts cannot be dropped, such a sys, system, and many more.

How:
1. Get informed. Login to Oracle Support, and research how to drop the user, or how to uninstall the account. Confirm that you understand the steps involved in the process. Never ever attempt to just drop the account without reviewing Oracle Support documentation!!!

2. Create your deployment plan and rollback plan.

3. Test your deployment plan and rollback plan in test environment

4. Backup your database

5. Drop the account in production following the deployment plan.

Dropping Schema and Application Accounts:

When:
You drop schema or application accounts when these are identified as not in use anymore, or an application is retired.

How confident are you that an account is no longer in use, when somebody tells you? Do you take their word for granted? I personally prefer to have proof from the database. This proof comes from auditing. There is a rule I live by, as a DBA:

A DBA trusts, never assumes and always verifies.

Let me translate, trust what others tell you (account can be dropped), do not assume the information is accurate (who is/was using this account?), and always gather proof to back the information (verify, check audit trail).

How:
1. To verify the account is no longer in use, turn on auditing or query the audit trail, if auditing is already on. For how long? Think about all the processes that could run in the
database under the account. There could be a monthly process, quarterly process or even year end process.

SQL> audit create session by USERNAME;

2. Query the audit trail frequently to determine account usage. I like to automate this process, and get notified if entries for a specific user show up in the audit trail.

SQL> select username, os_username, terminal, action, timestamp from dba_audit_trail where username = upper(‘USERNAME’)

3. Lock the user.
If the account is used afterwards, and you get notified that something is broken due to account being locked, it is very easy and fast to recover, just unlock the account.

SQL> alter user USERNAME account lock;

4. Backup the user.
You have identified that the account is not in use, and you are confident to drop it. Before proceeding with the drop, take a backup of the user: export the user, and/or extract the user DDL with dbms_metadata. Store these backups for a longer period of time, and make note of the backup location. This is your rollback.

export:
$> expdp directory=DATA_PUMP_DIR logfile=logfile_name DUMPFILE=dumpfile_name SCHEMAS=USERNAME REUSE_DUMPFILES=YES

I prefer to have meaningful names for the logfile, dumpfile, which include hostname, database name, username, date. This way it is easy to identify the dumpfile you need for recovery.

User DDL (this does not include grants):

SQL> set pagesize 200
SQL> set long 90000
SQL> select dbms_metadata.get_ddl(‘USER’,username) from dba_users where upper(username)=upper(‘USERNAME’);

5. Create the deployment and rollback plan!!! This is very important. Think about the steps to rollback, in order to recover quickly, in case the user account is needed.

6. Test the process of dropping and rolling back in your test environment. Do not drop in production environment until you test!

7. Safe to drop the account in production

SQL> drop user USERNAME;

Dropping Named User Accounts:

When:
You drop named user accounts when these accounts are no longer needed, as the user left the company, or changed roles within the company.

How:
1. My preference is to lock these accounts for 3 – 6 months. If the accounts are indeed not in use, then proceed to step 2. Some companies prefer to drop these accounts, as soon as the user leaves/changes role. In that case drop the account without locking. If you want to play it safe as well, you could extract the user DDL with dbms_metadata.

SQL> alter user USERNAME account lock;

2. Safe to drop the account.

SQL> drop user USERNAME;

Because a picture is worth a thousand words, I created a mind map for you to help remember the strategy!