Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Unused users for the last 30 and 60 days.

Re: Unused users for the last 30 and 60 days.

From: <Deepender.Gupta_at_newpower.com>
Date: Wed, 06 Mar 2002 11:28:36 -0800
Message-ID: <F001.00421166.20020306112836@fatcity.com>

Thanks John,

I followed the steps told by you and It is really helpful to get the required output.

Regds
deepender

                                                                                                                                      
                                                                                                                                      
                                                       To:   Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>              
                                                       cc:                                                                            
                                                       Subject:      Re: Unused users for the last 30 and 60 days.                    
                                                                                                                                      
              Ora NT DBA <orantdba_at_netscape.net>                                                                                      
                     02/28/02     04:48 PM                                                                                            
                  Please respond to ORACLE-L                                                                                          
                                                                                                                                      
           ------------------------------------------                                                                                 




Hi

That should work, I was using oracle 7 (so it wouldn't have worked then) at the time and we had varying levels that we had to deal with > 30 days we had to send an e-mail > 60 days we had to disable. This could be done with the account management features of 9 also . I don't believe the aging is that discriminating. But if you had one level and you were using 8, 8i or 9i I'm thinking I could get your idea to work as well.

John

wisernet100_at_yahoo.com wrote:

      wouldn't it be easier to create a new profile or change the default
      profile so that there was a password_life_time of 30 or 60 and then
      set
      the grace period to a day or so and then check for users whose
      accounts
      are locked?




      --- Ora NT DBA <orantdba_at_netscape.net> wrote:
            Hi Deepender,

            I know of know builtin oracle view that will tell you this
            infomation.
             At a former
            job we were required to disable accounts that had not been used
            in
            the
            last 60
            days.

            It has been a little while since I worked there (7 years to be
            exact)
            but
            basically this is what we did

            1.  created a tablecalled dbs_user_info with the fields
            (username,
            create_date, last_login_date)

            2.  copied the username and create field from dba_users into
            the
            fields,
            initially we set last_login_date to be = create;

            3.  Turned on auditing in the init.ora and audited successful
            logins

            4.  at night we went through the sys.aud$ table and for each
            user
            that
            existed updated the last_login_date  truncating sys.aud$ when
            done

            5.  weekly ran a batch job that found all users that
            last_login_date
            was

> 30 days
Beginning in 8i it might be easier to do this with an after log in trigger rather than auditing Hope this helps, John that contained the username, Deepender.Gupta_at_newpower.com wrote: Hi All, I want to have report of all the users who has not used their Oracle username for the last 30 and 60 days. Any views how can I get the report. Thanks, Deepender -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ora NT DBA INET: orantdba_at_netscape.net Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __________________________________________________ Do You Yahoo!? Yahoo! Greetings - Send FREE e-cards for every occasion! http://greetings.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Deepender.Gupta_at_newpower.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Wed Mar 06 2002 - 13:28:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US