Re: Dormant database user accounts
Date: Tue, 17 Mar 2015 10:32:09 -0600
Message-ID: <55085709.4010801_at_evdbt.com>
Thank you, Yong! My father was naval shore patrol during WWII, then a prison guard in Washington DC for 8 years, then a police officer in New Jersey for 30 years, not an unusual career path for someone of Irish ancestry in the US at the time.
He was a terrific storyteller andhad a lot of great stories to tell about ... hmm ... ah ... varieties of the human condition.
Working in IT for the past 31 years, my stories aren't as compelling or interesting, but I find them personally instructive.
Happy St Patrick's Day to everyone! Or as dad would say, "Merry Amateur Night!"
On 3/17/15 9:09, Yong Huang wrote:
> > Perhaps it would make good sense to check AWR for any logical reads
> > against objects within the schemas to be deleted
>
> That's the most non-intrusive way to prepare for account
> decommissioning. Let's call it step 1. The next step is to "disable"
> (in general sense) the account and its objects. The way to disable
> them depends on the types of the "things" to disable. For tables and
> indexes, you can rename them, usually just tables. For accounts, you
> lock them. What about PL/SQL objects? We haven't yet come to the last
> step, actual decommissioning. Since you can't rename PL/SQL objects,
> the only way I can think of is to invalidate them (using
> dbms_utility.invalidate) and check dba_objects.status later. Compare
> this with checking v$object_usage after you turn on index monitoring.
> In fact, turning PL/SQL objects to invalid and monitoring their status
> is part of step 1, because they're not disabled.
>
> After a week or so, the account may be dropped, if no application is
> found broken. Of course this is not foolproof. I found searching email
> folders and Intranet portals really helps when it comes to
> deleting/dropping/decommissioning anything.
>
> > When accounts get deleted, or worse, userids are reused, the
> attributions
> > in the audit data will probably stop working properly
>
> When the account is dropped, records in dba_audit_trail or
> dba_audit_session remain. When the username is reused, these views
> won't tell you the username is actually two users, even though their
> user IDs (the sequence numbers, i.e. dba_users.user_id) are different.
> Unfortunately, the underlined sys.aud$ records user names, not user
> id's, in the confusingly named userid column. Even 12c unified
> auditing does that. If a username has been reused only once, you can
> of course tell from when the audit records are about the second person
> named such, according to the current dba_users.created.
>
> <offtopic>
>
> > 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
>
> I truly enjoy this and many other stories Tim told. Thank you! Once at
> a local Oracle Users Group, some company's CIO talked about the
> importance of something I don't remember. He was an excellent speaker
> in terms of general speech skills. I interrupted him with a very
> polite request to add real-life examples, but there was absolutely
> none in his talk.
>
> </offtopic>
>
> Yong Huang
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 17 2015 - 17:32:09 CET