Re: Dormant database user accounts
Date: Mon, 16 Mar 2015 08:35:18 -0600
Message-ID: <5506EA26.2050006_at_evdbt.com>
Perhaps it would make good sense to check AWR for any logical reads against objects within the schemas to be deleted the proposed 6 month inactive grace period? If there is an object due for deletion that is being queried for any reason by anything, then it is worth investigating and resolving. It might be a foolishly mis-configured DBMS_STATS job, or it might be that a table is key to a complex and obfuscated month-end, quarter-end, or year-end process.
Almost 20 years ago, at a large credit-card processor in the US, the DBA team deleted a schema belonging to a terminated employee as they were instructed. They did their due diligence, informed everyone that the account was about to be deleted, gave several months as a grace period, and nobody said a word. Immediately after the schema was dropped, chaos ensued. It turned out that the schema included a set of tables with information stored nowhere else in the organization. Demands were made for immediate restore, against which the DBA team argued quite reasonably that the schema was deleted only after considerable due diligence. Blame was spread evenly and liberally, and in a wide fan pattern. Quickly, the schema was restored from backup after a senior VP calculated the value of the dropped schema to the company at more than US$200m. I don't know how this number was calculated nor how valid it may have been, but that was the number used for justification.
While this may sound like it should have been simple to resolve, what complicated matters was that this production DW database ran in NOARCHIVELOG mode. The database was only about 6 TB in size, but it completely consumed the resources of a 4-node OPS cluster on HP Superdome nodes, and generated about 6 TB in redo daily. So, restoring a dropped schema was non-trivial, almost impossible, but accomplished. The point is, this tempest could have been avoided by realizing that "due diligence" is insufficient in some situations.
You can't know if a person is truly dead until body and brain functions flatline over time. You can't know if a schema is truly inactive until logical reads flatline over time.
When it comes to death or dropped data, due diligence is usually insufficient, because often there is no undo for the operation.
On 3/16/15 7:35, Leroy Kemnitz wrote:
>
> Thanks for all of the good input on this.
>
> To answer some of the questions – Yes, I am auditing user logins. So
> I am querying the audit views to find the user accounts that were used
> in the last year and half, then comparing them to the actual list of
> users setup in the database. Also, these accounts are the human
> accounts – they don’t own any objects. My application owner logins
> are separate.
>
> So it does sound like 90 days is the avg time to expire a password.
> The user is then sent an email alerting them to the change. Then
> after about 6 months of inactivity, the locked accounts are deleted.
>
> That completely makes sense to me…….now to convince the security officier.
>
> LeRoy
>
> *From:*oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Jithin Sarath
> *Sent:* Friday, March 13, 2015 2:44 PM
> *To:* mcolmenares_at_newtechsistemas.com.ve
> *Cc:* mark.powell2_at_hp.com; oracle-l_at_freelists.org
> *Subject:* Re: Dormant database user accounts
>
> What we use is a mix of profiles and custom code.
>
> We have all human users assigned to a specific profile. Other
> accounts, which are used by applications / interfaces etc are assigned
> to separate profiles. The human user profile is set to expire password
> every 90 days.
>
> We then have some custom code, which runs to see if a user accounts is
> expired and has been in that state for 45 days, we lock it and
> generate an email to the user (the username and email is linked in a
> custom table). There is another process which picked up accounts
> locked for over 90 days and cleans them up.
>
> --Jithin
>
> On Fri, Mar 13, 2015 at 3:38 PM, Marcos Colmenares H.
> <mcolmenares_at_newtechsistemas.com.ve
> <mailto:mcolmenares_at_newtechsistemas.com.ve>> wrote:
>
> Im with mark on this one ... i would start sending emails about
> account closure .. then instead of deleting them i would change the
> passes for a month or two... once you change the pass people will
> either ask why its not working or its just not needed ...
>
> i would also document all the actual account data (grants and the
> such) and keep it in a document just in case you need to re-create it.
>
>
>
>
> Saludos Cordiales,
>
> Marcos Colmenares H
>
> --
>
> 2015-03-13 14:52 GMT-04:30 Powell, Mark <mark.powell2_at_hp.com
> <mailto:mark.powell2_at_hp.com>>:
>
> If you are going to notify the user I think you should send the
> email X days prior to deleting the account.
>
> *From:*oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> [mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>] *On Behalf Of *Andrew Kerber
> *Sent:* Friday, March 13, 2015 11:06 AM
> *To:* lkemnitz_at_uwsa.edu <mailto:lkemnitz_at_uwsa.edu>
> *Cc:* oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
> *Subject:* Re: Dormant database user accounts
>
> You need to be a little cautions about this. We have accounts
> that own objects that we never log in to. But the objects are
> critical.
>
> On Thu, Mar 12, 2015 at 3:05 PM, Leroy Kemnitz <lkemnitz_at_uwsa.edu
> <mailto:lkemnitz_at_uwsa.edu>> wrote:
>
> All –
>
> We are currently having a discussion in house about user accounts
> in the databases that are considered ‘dormant’ or unused. I want
> to set a limit of one year. If after one year, the account has
> not been used at all, then I want to delete the account and send
> an email to the last known email address informing the customer.
> How do other places handle this situation? Do you lock the
> accounts and then notify customers – then delete if no response in
> 2 weeks? What time limits are other people using? I see some
> people are doing 90 days of not logging in flags an account as
> ‘dormant’.
>
> LeRoy
>
>
>
>
> --
>
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 16 2015 - 15:35:18 CET