RE: Dormant database user accounts
From: Yong Huang <yong321_at_yahoo.com>
Date: Tue, 17 Mar 2015 15:09:34 +0000
Message-ID: <2054587469.119323.1426604974148.JavaMail.yahoo_at_mail.yahoo.com>
> 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.
Date: Tue, 17 Mar 2015 15:09:34 +0000
Message-ID: <2054587469.119323.1426604974148.JavaMail.yahoo_at_mail.yahoo.com>
> 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 - 16:09:34 CET