Loading ...
Sorry, an error occurred while loading the content.

RE: PeopleSoft DBA Forum Can I do this, and if so, how

Expand Messages
  • Duncan Davies
    Kicking a user out and displaying an explanatory message may not be that difficult. I haven t prototyped this, but assuming you are using sign-on PeopleCode,
    Message 1 of 24 , Dec 8, 2005
    • 0 Attachment
      Kicking a user out and displaying an explanatory message may not be that difficult. 
       
      I haven't prototyped this, but assuming you are using sign-on PeopleCode, you could just call 'SetAuthenticationResult' to return them to the logon page and display a message like "You cannot use that UserID from this PC".
       
      Identifying who to boot, and who to allow is going to be harder.  Perhaps a starting step would be to just boot anyone who is signing in as a user who is already signed in.  Alternatively, I don't know what the parameters are that are passed into the Insert statement David found in the trace, but sometimes PSACCESSLOG contains IP addys, and sometimes computer names.  Without investigation I couldn't say how it decides whether to insert a computer name or an IP address, but if you could force it to insert computer name, then the IP address/DHCP problem goes away.
       
      kind regards
       
      Duncan
       
      PS. as for stupid users outside the states, I think most of the help for fixing this issue is coming from outside the states :-)


      From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of Robert Ellis
      Sent: 08 December 2005 22:41
      To: psftdba@yahoogroups.com
      Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

      Even better, now how do we kick out the additional OPRID session that originates from a different IP – I think we’d need a table of valid OPRID IP combinations to check against but how to kick them out?  Would it be enough to audit the event and email the miscreant and his or her manager?

       

      By the way Clarke, don’t think you got away with that suggestion that all your stupid users are outside the US – we’ll get you for that JJJ

       

       

       

      Robert Ellis

      PSE Data Security GmbH

      http://www.psedatasecurity.com


      From: David Kurtz [mailto:info2@...]
      Sent: 08 December 2005 22:43
      To: psftdba@yahoogroups.com
      Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

       

      Ahh!  I had forgotten about that.  And 2 lines of trace later it inserts user name and IP address into PSACCESSLOG.

       

       

      PSAPPSRV.2736    1-489    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=UPDATE PSOPRDEFN SET LASTSIGNONDTTM = TO_DATE(SUBSTR(:1, 0, 19),'YYYY-MM-DD-HH24.MI.SS') WHERE OPRID = :2
      PSAPPSRV.2736    1-490    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSACCESSLOG
      PSAPPSRV.2736    1-491    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=INSERT INTO PSACCESSLOG (OPRID, LOGIPADDRESS, LOGINDTTM, LOGOUTDTTM) VALUES (:1, :2, TO_DATE(SUBSTR(:3, 0, 19),'YYYY-MM-DD-HH24.MI.SS'), TO_DATE(SUBSTR(:4, 0, 19),'YYYY-MM-DD-HH24.MI.SS'))

       

       

      regards
      _________________________
      David Kurtz
      Go-Faster Consultancy Ltd.
      tel: +44 (0)7771 760660
      fax: +44 (0)7092 348865
      web: www.go-faster.co.uk
      mailto:david.kurtz@...
      Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
      PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

      -----Original Message-----
      From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf Of Robert Ellis
      Sent: 08 December 2005 20:05
      To: psftdba@yahoogroups.com
      Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

      Indeed not, but doesn’t LASTSIGNONDTTM  on PSOPRDEFN gets updated every time a user signs in so an update trigger there could be a starting point?

       

      Robert Ellis

      PSE Data Security GmbH

      http://www.psedatasecurity.com


      From: David Kurtz [mailto:info2@...]
      Sent: 08 December 2005 19:43
      To: psftdba@yahoogroups.com
      Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

       

      What kind of trigger?  The PSAPPSRV sessions do not log in for every
      service.

      regards
      _________________________
      David Kurtz



      --
      No virus found in this incoming message.
      Checked by AVG Free Edition.
      Version: 7.1.371 / Virus Database: 267.13.12/194 - Release Date: 07/12/2005

    • David Kurtz
      I believe it only puts an IP address in if it cannot do a reverse DNS lookup. Its just a string, so it should really matter, so long as it is consistent in
      Message 2 of 24 , Dec 8, 2005
      • 0 Attachment
        I believe it only puts an IP address in if it cannot do a reverse DNS lookup.  Its just a string, so it should really matter, so long as it is consistent in its behaviour.
         

        regards
        _________________________
        David Kurtz
        Go-Faster Consultancy Ltd.
        tel: +44 (0)7771 760660
        fax: +44 (0)7092 348865
        web: www.go-faster.co.uk
        mailto:david.kurtz@...
        Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
        PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

        -----Original Message-----
        From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf Of Duncan Davies
        Sent: 08 December 2005 23:21
        To: psftdba@yahoogroups.com
        Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

        Kicking a user out and displaying an explanatory message may not be that difficult. 
         
        I haven't prototyped this, but assuming you are using sign-on PeopleCode, you could just call 'SetAuthenticationResult' to return them to the logon page and display a message like "You cannot use that UserID from this PC".
         
        Identifying who to boot, and who to allow is going to be harder.  Perhaps a starting step would be to just boot anyone who is signing in as a user who is already signed in.  Alternatively, I don't know what the parameters are that are passed into the Insert statement David found in the trace, but sometimes PSACCESSLOG contains IP addys, and sometimes computer names.  Without investigation I couldn't say how it decides whether to insert a computer name or an IP address, but if you could force it to insert computer name, then the IP address/DHCP problem goes away.
         
        kind regards
         
        Duncan
         
        PS. as for stupid users outside the states, I think most of the help for fixing this issue is coming from outside the states :-)


        From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of Robert Ellis
        Sent: 08 December 2005 22:41
        To: psftdba@yahoogroups.com
        Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

        Even better, now how do we kick out the additional OPRID session that originates from a different IP – I think we’d need a table of valid OPRID IP combinations to check against but how to kick them out?  Would it be enough to audit the event and email the miscreant and his or her manager?

         

        By the way Clarke, don’t think you got away with that suggestion that all your stupid users are outside the US – we’ll get you for that JJJ

         

         

         

        Robert Ellis

        PSE Data Security GmbH

        http://www.psedatasecurity.com


        From: David Kurtz [mailto:info2@...]
        Sent: 08 December 2005 22:43
        To: psftdba@yahoogroups.com
        Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

         

        Ahh!  I had forgotten about that.  And 2 lines of trace later it inserts user name and IP address into PSACCESSLOG.

         

         

        PSAPPSRV.2736    1-489    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=UPDATE PSOPRDEFN SET LASTSIGNONDTTM = TO_DATE(SUBSTR(:1, 0, 19),'YYYY-MM-DD-HH24.MI.SS') WHERE OPRID = :2
        PSAPPSRV.2736    1-490    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSACCESSLOG
        PSAPPSRV.2736    1-491    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=INSERT INTO PSACCESSLOG (OPRID, LOGIPADDRESS, LOGINDTTM, LOGOUTDTTM) VALUES (:1, :2, TO_DATE(SUBSTR(:3, 0, 19),'YYYY-MM-DD-HH24.MI.SS'), TO_DATE(SUBSTR(:4, 0, 19),'YYYY-MM-DD-HH24.MI.SS'))

         

         

        regards
        _________________________
        David Kurtz
        Go-Faster Consultancy Ltd.
        tel: +44 (0)7771 760660
        fax: +44 (0)7092 348865
        web: www.go-faster.co.uk
        mailto:david.kurtz@...
        Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
        PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

        -----Original Message-----
        From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf Of Robert Ellis
        Sent: 08 December 2005 20:05
        To: psftdba@yahoogroups.com
        Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

        Indeed not, but doesn’t LASTSIGNONDTTM  on PSOPRDEFN gets updated every time a user signs in so an update trigger there could be a starting point?

         

        Robert Ellis

        PSE Data Security GmbH

        http://www.psedatasecurity.com


        From: David Kurtz [mailto:info2@...]
        Sent: 08 December 2005 19:43
        To: psftdba@yahoogroups.com
        Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

         

        What kind of trigger?  The PSAPPSRV sessions do not log in for every
        service.

        regards
        _________________________
        David Kurtz



        --
        No virus found in this incoming message.
        Checked by AVG Free Edition.
        Version: 7.1.371 / Virus Database: 267.13.12/194 - Release Date: 07/12/2005

      • Robert Ellis
        Most stupid user ever - that guy at a certain weapons research organisation that was trying to signing on to PS and complaining his pc froze every time - he
        Message 3 of 24 , Dec 8, 2005
        • 0 Attachment

          Most stupid user ever – that guy at a certain weapons research organisation that was trying to signing on to PS and complaining his pc froze every time – he was trying to sign on to the jpg of the signon page that support had sent out to new users J

           

           

          Robert Ellis

          PSE Data Security GmbH

          http://www.psedatasecurity.com


          From: David Kurtz [mailto:info2@...]
          Sent: 08 December 2005 23:54
          To: psftdba@yahoogroups.com
          Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

           

          Then the problem is that users can change IP address when their DHCP lease expires. 

           

          BTW, stupid users are a universal constant

           

          regards
          _________________________
          David Kurtz
          Go-Faster Consultancy Ltd.
          tel: +44 (0)7771 760660
          fax: +44 (0)7092 348865
          web: www.go-faster.co.uk
          mailto:david.kurtz@...
          Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
          PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

          -----Original Message-----
          From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf Of Robert Ellis
          Sent: 08 December 2005 22:41
          To: psftdba@yahoogroups.com
          Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

          Even better, now how do we kick out the additional OPRID session that originates from a different IP – I think we’d need a table of valid OPRID IP combinations to check against but how to kick them out?  Would it be enough to audit the event and email the miscreant and his or her manager?

           

          By the way Clarke, don’t think you got away with that suggestion that all your stupid users are outside the US – we’ll get you for that JJJ

           

           

           

          Robert Ellis

          PSE Data Security GmbH

          http://www.psedatasecurity.com


          From: David Kurtz [mailto:info2@...]
          Sent: 08 December 2005 22:43
          To: psftdba@yahoogroups.com
          Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

           

          Ahh!  I had forgotten about that.  And 2 lines of trace later it inserts user name and IP address into PSACCESSLOG.

           

           

          PSAPPSRV.2736    1-489    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=UPDATE PSOPRDEFN SET LASTSIGNONDTTM = TO_DATE(SUBSTR(:1, 0, 19),'YYYY-MM-DD-HH24.MI.SS') WHERE OPRID = :2
          PSAPPSRV.2736    1-490    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSACCESSLOG
          PSAPPSRV.2736    1-491    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=INSERT INTO PSACCESSLOG (OPRID, LOGIPADDRESS, LOGINDTTM, LOGOUTDTTM) VALUES (:1, :2, TO_DATE(SUBSTR(:3, 0, 19),'YYYY-MM-DD-HH24.MI.SS'), TO_DATE(SUBSTR(:4, 0, 19),'YYYY-MM-DD-HH24.MI.SS'))

           

           

          regards
          _________________________
          David Kurtz
          Go-Faster Consultancy Ltd.
          tel: +44 (0)7771 760660
          fax: +44 (0)7092 348865
          web: www.go-faster.co.uk
          mailto:david.kurtz@...
          Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
          PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

          -----Original Message-----
          From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf Of Robert Ellis
          Sent: 08 December 2005 20:05
          To: psftdba@yahoogroups.com
          Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

          Indeed not, but doesn’t LASTSIGNONDTTM  on PSOPRDEFN gets updated every time a user signs in so an update trigger there could be a starting point?

           

          Robert Ellis

          PSE Data Security GmbH

          http://www.psedatasecurity.com


          From: David Kurtz [mailto:info2@...]
          Sent: 08 December 2005 19:43
          To: psftdba@yahoogroups.com
          Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

           

          What kind of trigger?  The PSAPPSRV sessions do not log in for every
          service.

          regards
          _________________________
          David Kurtz

           


        • Robert Ellis
          And I guess machine name could be added to psaccesslog - oops, there I go customising tools objects again. The signon peoplecode idea is the way to go - have
          Message 4 of 24 , Dec 8, 2005
          • 0 Attachment

            And I guess machine name could be added to psaccesslog – oops, there I go customising tools objects again.  The signon peoplecode idea is the way to go – have to see where the update happens in relation to the code or maybe add a sqlexec into the peoplecode to fire the trigger.  I just wonder if the machine info is available at this point. 

             

            Robert Ellis

            PSE Data Security GmbH

            http://www.psedatasecurity.com


            From: David Kurtz [mailto:info2@...]
            Sent: 09 December 2005 01:22
            To: psftdba@yahoogroups.com
            Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

             

            I believe it only puts an IP address in if it cannot do a reverse DNS lookup.  Its just a string, so it should really matter, so long as it is consistent in its behaviour.

             

            regards
            _________________________
            David Kurtz
            Go-Faster Consultancy Ltd.
            tel: +44 (0)7771 760660
            fax: +44 (0)7092 348865
            web: www.go-faster.co.uk
            mailto:david.kurtz@...
            Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
            PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

            -----Original Message-----
            From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf Of Duncan Davies
            Sent: 08 December 2005 23:21
            To: psftdba@yahoogroups.com
            Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

            Kicking a user out and displaying an explanatory message may not be that difficult. 

             

            I haven't prototyped this, but assuming you are using sign-on PeopleCode, you could just call 'SetAuthenticationResult' to return them to the logon page and display a message like "You cannot use that UserID from this PC".

             

            Identifying who to boot, and who to allow is going to be harder.  Perhaps a starting step would be to just boot anyone who is signing in as a user who is already signed in.  Alternatively, I don't know what the parameters are that are passed into the Insert statement David found in the trace, but sometimes PSACCESSLOG contains IP addys, and sometimes computer names.  Without investigation I couldn't say how it decides whether to insert a computer name or an IP address, but if you could force it to insert computer name, then the IP address/DHCP problem goes away.

             

            kind regards

             

            Duncan

             

            PS. as for stupid users outside the states, I think most of the help for fixing this issue is coming from outside the states :-)

             


            From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of Robert Ellis
            Sent: 08 December 2005 22:41
            To: psftdba@yahoogroups.com
            Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

            Even better, now how do we kick out the additional OPRID session that originates from a different IP – I think we’d need a table of valid OPRID IP combinations to check against but how to kick them out?  Would it be enough to audit the event and email the miscreant and his or her manager?

             

            By the way Clarke, don’t think you got away with that suggestion that all your stupid users are outside the US – we’ll get you for that JJJ

             

             

             

            Robert Ellis

            PSE Data Security GmbH

            http://www.psedatasecurity.com


            From: David Kurtz [mailto:info2@...]
            Sent: 08 December 2005 22:43
            To: psftdba@yahoogroups.com
            Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

             

            Ahh!  I had forgotten about that.  And 2 lines of trace later it inserts user name and IP address into PSACCESSLOG.

             

             

            PSAPPSRV.2736    1-489    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=UPDATE PSOPRDEFN SET LASTSIGNONDTTM = TO_DATE(SUBSTR(:1, 0, 19),'YYYY-MM-DD-HH24.MI.SS') WHERE OPRID = :2
            PSAPPSRV.2736    1-490    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSACCESSLOG
            PSAPPSRV.2736    1-491    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=INSERT INTO PSACCESSLOG (OPRID, LOGIPADDRESS, LOGINDTTM, LOGOUTDTTM) VALUES (:1, :2, TO_DATE(SUBSTR(:3, 0, 19),'YYYY-MM-DD-HH24.MI.SS'), TO_DATE(SUBSTR(:4, 0, 19),'YYYY-MM-DD-HH24.MI.SS'))

             

             

            regards
            _________________________
            David Kurtz
            Go-Faster Consultancy Ltd.
            tel: +44 (0)7771 760660
            fax: +44 (0)7092 348865
            web: www.go-faster.co.uk
            mailto:david.kurtz@...
            Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
            PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

            -----Original Message-----
            From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf Of Robert Ellis
            Sent: 08 December 2005 20:05
            To: psftdba@yahoogroups.com
            Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

            Indeed not, but doesn’t LASTSIGNONDTTM  on PSOPRDEFN gets updated every time a user signs in so an update trigger there could be a starting point?

             

            Robert Ellis

            PSE Data Security GmbH

            http://www.psedatasecurity.com


            From: David Kurtz [mailto:info2@...]
            Sent: 08 December 2005 19:43
            To: psftdba@yahoogroups.com
            Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

             

            What kind of trigger?  The PSAPPSRV sessions do not log in for every
            service.

            regards
            _________________________
            David Kurtz

             

            --
            No virus found in this incoming message.
            Checked by AVG Free Edition.
            Version: 7.1.371 / Virus Database: 267.13.12/194 - Release Date: 07/12/2005


          • Duncan Davies
            If you don t want to (or can t) get PSACCESSLOG to use the machine name, then you don t have to use it. The sign-on peoplecode will already have access to the
            Message 5 of 24 , Dec 8, 2005
            • 0 Attachment
              If you don't want to (or can't) get PSACCESSLOG to use the machine name, then you don't have to use it.  The sign-on peoplecode will already have access to the userid (%userid) and machine name (use peoplecode to expand the environment variable 'hostname').  Just compare those two to your mapping table.


              From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of Robert Ellis
              Sent: 09 December 2005 05:30
              To: psftdba@yahoogroups.com
              Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

              And I guess machine name could be added to psaccesslog – oops, there I go customising tools objects again.  The signon peoplecode idea is the way to go – have to see where the update happens in relation to the code or maybe add a sqlexec into the peoplecode to fire the trigger.  I just wonder if the machine info is available at this point. 

               

              Robert Ellis

              PSE Data Security GmbH

              http://www.psedatasecurity.com


              From: David Kurtz [mailto:info2@...]
              Sent: 09 December 2005 01:22
              To: psftdba@yahoogroups.com
              Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

               

              I believe it only puts an IP address in if it cannot do a reverse DNS lookup.  Its just a string, so it should really matter, so long as it is consistent in its behaviour.

               

              regards
              _________________________
              David Kurtz
              Go-Faster Consultancy Ltd.
              tel: +44 (0)7771 760660
              fax: +44 (0)7092 348865
              web: www.go-faster.co.uk
              mailto:david.kurtz@...
              Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
              PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

              -----Original Message-----
              From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf Of Duncan Davies
              Sent: 08 December 2005 23:21
              To: psftdba@yahoogroups.com
              Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

              Kicking a user out and displaying an explanatory message may not be that difficult. 

               

              I haven't prototyped this, but assuming you are using sign-on PeopleCode, you could just call 'SetAuthenticationResult' to return them to the logon page and display a message like "You cannot use that UserID from this PC".

               

              Identifying who to boot, and who to allow is going to be harder.  Perhaps a starting step would be to just boot anyone who is signing in as a user who is already signed in.  Alternatively, I don't know what the parameters are that are passed into the Insert statement David found in the trace, but sometimes PSACCESSLOG contains IP addys, and sometimes computer names.  Without investigation I couldn't say how it decides whether to insert a computer name or an IP address, but if you could force it to insert computer name, then the IP address/DHCP problem goes away.

               

              kind regards

               

              Duncan

               

              PS. as for stupid users outside the states, I think most of the help for fixing this issue is coming from outside the states :-)

               


              From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of Robert Ellis
              Sent: 08 December 2005 22:41
              To: psftdba@yahoogroups.com
              Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

              Even better, now how do we kick out the additional OPRID session that originates from a different IP – I think we’d need a table of valid OPRID IP combinations to check against but how to kick them out?  Would it be enough to audit the event and email the miscreant and his or her manager?

               

              By the way Clarke, don’t think you got away with that suggestion that all your stupid users are outside the US – we’ll get you for that JJJ

               

               

               

              Robert Ellis

              PSE Data Security GmbH

              http://www.psedatasecurity.com


              From: David Kurtz [mailto:info2@...]
              Sent: 08 December 2005 22:43
              To: psftdba@yahoogroups.com
              Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

               

              Ahh!  I had forgotten about that.  And 2 lines of trace later it inserts user name and IP address into PSACCESSLOG.

               

               

              PSAPPSRV.2736    1-489    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=UPDATE PSOPRDEFN SET LASTSIGNONDTTM = TO_DATE(SUBSTR(:1, 0, 19),'YYYY-MM-DD-HH24.MI.SS') WHERE OPRID = :2
              PSAPPSRV.2736    1-490    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSACCESSLOG
              PSAPPSRV.2736    1-491    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=INSERT INTO PSACCESSLOG (OPRID, LOGIPADDRESS, LOGINDTTM, LOGOUTDTTM) VALUES (:1, :2, TO_DATE(SUBSTR(:3, 0, 19),'YYYY-MM-DD-HH24.MI.SS'), TO_DATE(SUBSTR(:4, 0, 19),'YYYY-MM-DD-HH24.MI.SS'))

               

               

              regards
              _________________________
              David Kurtz
              Go-Faster Consultancy Ltd.
              tel: +44 (0)7771 760660
              fax: +44 (0)7092 348865
              web: www.go-faster.co.uk
              mailto:david.kurtz@...
              Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
              PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

              -----Original Message-----
              From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf Of Robert Ellis
              Sent: 08 December 2005 20:05
              To: psftdba@yahoogroups.com
              Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

              Indeed not, but doesn’t LASTSIGNONDTTM  on PSOPRDEFN gets updated every time a user signs in so an update trigger there could be a starting point?

               

              Robert Ellis

              PSE Data Security GmbH

              http://www.psedatasecurity.com


              From: David Kurtz [mailto:info2@...]
              Sent: 08 December 2005 19:43
              To: psftdba@yahoogroups.com
              Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

               

              What kind of trigger?  The PSAPPSRV sessions do not log in for every
              service.

              regards
              _________________________
              David Kurtz

               

              --
              No virus found in this incoming message.
              Checked by AVG Free Edition.
              Version: 7.1.371 / Virus Database: 267.13.12/194 - Release Date: 07/12/2005



              --
              No virus found in this incoming message.
              Checked by AVG Free Edition.
              Version: 7.1.371 / Virus Database: 267.13.12/194 - Release Date: 07/12/2005

            • James Blanding
              But such a user wouldn t be completely reauthenticated, would they? I guess the question is what causes an entry to appear in PSACCESSLOG... --James ... lease
              Message 6 of 24 , Dec 9, 2005
              • 0 Attachment
                But such a user wouldn't be completely reauthenticated, would they? I
                guess the question is what causes an entry to appear in PSACCESSLOG...

                --James

                --- In psftdba@yahoogroups.com, "David Kurtz" <info2@g...> wrote:
                >
                > Then the problem is that users can change IP address when their DHCP
                lease
                > expires.
                >
                > BTW, stupid users are a universal constant
                >
                > regards
                > _________________________
                > David Kurtz
                > Go-Faster Consultancy Ltd.
                > tel: +44 (0)7771 760660
                > fax: +44 (0)7092 348865
                > web: www.go-faster.co.uk
                > mailto:david.kurtz@g...
                > Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
                > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                >
                > -----Original Message-----
                > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On
                Behalf Of
                > Robert Ellis
                > Sent: 08 December 2005 22:41
                > To: psftdba@yahoogroups.com
                > Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how
                >
                >
                > Even better, now how do we kick out the additional OPRID session that
                > originates from a different IP - I think we'd need a table of valid
                OPRID IP
                > combinations to check against but how to kick them out? Would it be
                enough
                > to audit the event and email the miscreant and his or her manager?
                >
                >
                >
                > By the way Clarke, don't think you got away with that suggestion
                that all
                > your stupid users are outside the US - we'll get you for that JJJ
                >
                >
                >
                >
                >
                >
                >
                > Robert Ellis
                >
                > PSE Data Security GmbH
                >
                > http://www.psedatasecurity.com
                >
                >
                >
                ----------------------------------------------------------------------------
                > --
                >
                > From: David Kurtz [mailto:info2@g...]
                > Sent: 08 December 2005 22:43
                > To: psftdba@yahoogroups.com
                > Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how
                >
                >
                >
                > Ahh! I had forgotten about that. And 2 lines of trace later it
                inserts
                > user name and IP address into PSACCESSLOG.
                >
                >
                >
                >
                >
                > PSAPPSRV.2736 1-489 21.31.49 0.000 Cur#1.2736.F84D RC=0
                Dur=0.000
                > COM Stmt=UPDATE PSOPRDEFN SET LASTSIGNONDTTM = TO_DATE(SUBSTR(:1, 0,
                > 19),'YYYY-MM-DD-HH24.MI.SS') WHERE OPRID = :2
                > PSAPPSRV.2736 1-490 21.31.49 0.000 Cur#1.2736.F84D RC=0
                Dur=0.000
                > COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSACCESSLOG
                > PSAPPSRV.2736 1-491 21.31.49 0.000 Cur#1.2736.F84D RC=0
                Dur=0.000
                > COM Stmt=INSERT INTO PSACCESSLOG (OPRID, LOGIPADDRESS, LOGINDTTM,
                > LOGOUTDTTM) VALUES (:1, :2, TO_DATE(SUBSTR(:3, 0,
                > 19),'YYYY-MM-DD-HH24.MI.SS'), TO_DATE(SUBSTR(:4, 0,
                > 19),'YYYY-MM-DD-HH24.MI.SS'))
                >
                >
                >
                >
                >
                > regards
                > _________________________
                > David Kurtz
                > Go-Faster Consultancy Ltd.
                > tel: +44 (0)7771 760660
                > fax: +44 (0)7092 348865
                > web: www.go-faster.co.uk
                > mailto:david.kurtz@g...
                > Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
                > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                >
                > -----Original Message-----
                > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On
                Behalf
                > Of Robert Ellis
                > Sent: 08 December 2005 20:05
                > To: psftdba@yahoogroups.com
                > Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how
                >
                > Indeed not, but doesn't LASTSIGNONDTTM on PSOPRDEFN gets
                updated every
                > time a user signs in so an update trigger there could be a starting
                point?
                >
                >
                >
                > Robert Ellis
                >
                > PSE Data Security GmbH
                >
                > http://www.psedatasecurity.com
                >
                >
                >
                ----------------------------------------------------------------------------
                >
                > From: David Kurtz [mailto:info2@g...]
                > Sent: 08 December 2005 19:43
                > To: psftdba@yahoogroups.com
                > Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how
                >
                >
                >
                > What kind of trigger? The PSAPPSRV sessions do not log in for every
                > service.
                >
                > regards
                > _________________________
                > David Kurtz
                >
                >
                >
                >
                > PeopleSoft for the Oracle DBA is published by Apress - see
                > http://www.psftdba.com
                > The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk
                >
                >
                >
                >
                >
                >
                >
                ----------------------------------------------------------------------------
                > --
                > YAHOO! GROUPS LINKS
                >
                > a.. Visit your group "psftdba" on the web.
                >
                > b.. To unsubscribe from this group, send an email to:
                > psftdba-unsubscribe@yahoogroups.com
                >
                > c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of
                > Service.
                >
                >
                >
                ----------------------------------------------------------------------------
                > --
                >
              • Ty A.
                Hey Gang, I have just completed my PS HRMS 8.46 Install...I would like to make DEMO Instance a READ ONLY Does anyone has another way of doing it other than
                Message 7 of 24 , Dec 9, 2005
                • 0 Attachment
                  Hey Gang,

                  I have just completed my PS HRMS 8.46 Install...I would like to make DEMO
                  Instance a READ ONLY Does anyone has another way of doing it other than
                  make ALL Application tablespaces READ ONLY.

                  Thanks
                  TA
                • balbinder singh
                  Hi David, Finally, What is the solution for this problem. Can anyone able to get a right solution. I mean I did not understant if it is a solution to the
                  Message 8 of 24 , Feb 14, 2006
                  • 0 Attachment
                    Hi David,
                     
                    Finally, What is the solution for this problem.
                    Can anyone able to get a right solution.
                    I mean I did not understant if it is a solution to the problem.
                    we have similar problem like this .
                    One IP has many connections ,say 1700 connections.
                    How can we stop this??
                     
                    Any idea to stop many connections from a single IP will be highly appreciated.
                    Thanks,
                    Jas


                    David Kurtz <info2@...> wrote:
                    I believe it only puts an IP address in if it cannot do a reverse DNS lookup.  Its just a string, so it should really matter, so long as it is consistent in its behaviour.
                     
                    regards
                    _________________________
                    David Kurtz
                    Go-Faster Consultancy Ltd.
                    tel: +44 (0)7771 760660
                    fax: +44 (0)7092 348865
                    web: www.go-faster.co.uk
                    mailto:david.kurtz@...
                    Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
                    PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                    -----Original Message-----
                    From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf Of Duncan Davies
                    Sent: 08 December 2005 23:21
                    To: psftdba@yahoogroups.com
                    Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

                    Kicking a user out and displaying an explanatory message may not be that difficult. 
                     
                    I haven't prototyped this, but assuming you are using sign-on PeopleCode, you could just call 'SetAuthenticationResult' to return them to the logon page and display a message like "You cannot use that UserID from this PC".
                     
                    Identifying who to boot, and who to allow is going to be harder.  Perhaps a starting step would be to just boot anyone who is signing in as a user who is already signed in.  Alternatively, I don't know what the parameters are that are passed into the Insert statement David found in the trace, but sometimes PSACCESSLOG contains IP addys, and sometimes computer names.  Without investigation I couldn't say how it decides whether to insert a computer name or an IP address, but if you could force it to insert computer name, then the IP address/DHCP problem goes away.
                     
                    kind regards
                     
                    Duncan
                     
                    PS. as for stupid users outside the states, I think most of the help for fixing this issue is coming from outside the states :-)


                    From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of Robert Ellis
                    Sent: 08 December 2005 22:41
                    To: psftdba@yahoogroups.com
                    Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how

                    Even better, now how do we kick out the additional OPRID session that originates from a different IP – I think we’d need a table of valid OPRID IP combinations to check against but how to kick them out?  Would it be enough to audit the event and email the miscreant and his or her manager?
                     
                    By the way Clarke, don’t think you got away with that suggestion that all your stupid users are outside the US – we’ll get you for that JJJ
                     
                     
                     
                    Robert Ellis
                    PSE Data Security GmbH

                    From: David Kurtz [mailto:info2@...]
                    Sent: 08 December 2005 22:43
                    To: psftdba@yahoogroups.com
                    Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how
                     
                    Ahh!  I had forgotten about that.  And 2 lines of trace later it inserts user name and IP address into PSACCESSLOG.
                     
                     
                    PSAPPSRV.2736    1-489    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=UPDATE PSOPRDEFN SET LASTSIGNONDTTM = TO_DATE(SUBSTR(:1, 0, 19),'YYYY-MM-DD-HH24.MI.SS') WHERE OPRID = :2
                    PSAPPSRV.2736    1-490    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSACCESSLOG
                    PSAPPSRV.2736    1-491    21.31.49    0.000 Cur#1.2736.F84D RC=0 Dur=0.000 COM Stmt=INSERT INTO PSACCESSLOG (OPRID, LOGIPADDRESS, LOGINDTTM, LOGOUTDTTM) VALUES (:1, :2, TO_DATE(SUBSTR(:3, 0, 19),'YYYY-MM-DD-HH24.MI.SS'), TO_DATE(SUBSTR(:4, 0, 19),'YYYY-MM-DD-HH24.MI.SS'))
                     
                     
                    regards
                    _________________________
                    David Kurtz
                    Go-Faster Consultancy Ltd.
                    tel: +44 (0)7771 760660
                    fax: +44 (0)7092 348865
                    web: www.go-faster.co.uk
                    mailto:david.kurtz@...
                    Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
                    PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
                    -----Original Message-----
                    From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf Of Robert Ellis
                    Sent: 08 December 2005 20:05
                    To: psftdba@yahoogroups.com
                    Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how
                    Indeed not, but doesn’t LASTSIGNONDTTM  on PSOPRDEFN gets updated every time a user signs in so an update trigger there could be a starting point?
                     
                    Robert Ellis
                    PSE Data Security GmbH

                    From: David Kurtz [mailto:info2@...]
                    Sent: 08 December 2005 19:43
                    To: psftdba@yahoogroups.com
                    Subject: RE: PeopleSoft DBA Forum Can I do this, and if so, how
                     
                    What kind of trigger?  The PSAPPSRV sessions do not log in for every
                    service.

                    regards
                    _________________________
                    David Kurtz


                    --
                    No virus found in this incoming message.
                    Checked by AVG Free Edition.
                    Version: 7.1.371 / Virus Database: 267.13.12/194 - Release Date: 07/12/2005


                    YAHOO! GROUPS LINKS






                    Relax. Yahoo! Mail virus scanning helps detect nasty viruses!

                  • David Kurtz
                    Hello All Since Oracle completed the takeover of PeopleSoft, the user communities have been trying to get themselves organised again. In the US, the
                    Message 9 of 24 , Feb 27, 2006
                    • 0 Attachment
                      Hello All
                       
                      Since Oracle completed the takeover of PeopleSoft, the user communities have been trying to get themselves organised again. 
                      In the US, the Collaborate 06 conference in April 2006 will bring together Oracle database and apps, PeopleSoft, JDEdwards, and Siebel.  More of that another time.
                       
                      Here in Europe the UK Oracle User Group (http://www.ukoug.org/) seems to be the only focal point for the PeopleSoft and JDEdwards communities. 
                       
                      There has already been a very successful meeting of the PeopleSoft Financials SIG, but on Tuesday 21st March there will be a combined meeting of the three PeopleSoft user groups (http://www.ukoug.org/news/show_news.jsp?id=11161)
                       
                       
                      This meeting is FREE for all PeopleSoft customers!
                       
                      I firmly believe that if the PeopleSoft user base wants to have any influence at all over Oracle, its treatment of PeopleSoft, and the development of the Fusion product (that will supercede all the current applicaitons), then the users need to work together under the umbrella of the UKOUG.  UKOUG is one of the largest single Oracle user groups in the world (in the US they groups are split geographically), and they have an exceptionally effective relationship with Oracle Corp.  The PeopleSoft community would do well to tap into that!
                       
                      The technical user group (which will probably be of most interest to the audience of this forum) will continue to provide the strong technical content that we have been useful.  But, we can only do that with the support and participation of the user community
                       
                      More details will appear nearer to the event, but there are agendas available on the web now.
                       

                      regards
                      _________________________
                      David Kurtz
                      Go-Faster Consultancy Ltd.
                      tel: +44 (0)7771 760660
                      fax: +44 (0)7092 348865
                      web: www.go-faster.co.uk
                      mailto:david.kurtz@...
                      Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
                      PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

                    Your message has been successfully submitted and would be delivered to recipients shortly.