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

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).