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

Security and process type

Expand Messages
  • Montgomerie Steve
    Hello, In PS query if a user create s query with type process we can see that the row level security class is applied as expected like below SELECT A.EMPLID,
    Message 1 of 2 , Jul 1, 2004
      Message
       
      Hello,
      In PS query if a user create s query with type process we can see that the row level security class is applied as expected like below


      SELECT A.EMPLID, A.COMPANY
      FROM PS_JOB A, PS_PERS_SRCH_QRY A1
      WHERE A.EMPLID = A1.EMPLID
      AND A1.ROWSECCLASS = 'DPSVIALL'
      AND ( A.EFFDT =
      (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
      WHERE A.EMPLID = A_ED.EMPLID
      AND A.EMPL_RCD = A_ED.EMPL_RCD
      AND A_ED.EFFDT <= SYSDATE)
      AND A.EFFSEQ =
      (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
      WHERE A.EMPLID = A_ES.EMPLID
      AND A.EMPL_RCD = A_ES.EMPL_RCD
      AND A.EFFDT = A_ES.EFFDT) )

      However, once the query is executed the security is removed. We can see this by looking in v$sql in Oracle to see the following

      SELECT A.EMPLID, A.COMPANY FROM PS_JOB A WHERE A.EFFDT = (SELECT
      MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID
      AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= SYSDATE)
      AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE A.EMPLID= A_ES.EMPLID
      AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT)
       
      Any thoughts? Also can someone give me a defintion on what type process is? I can't find one in peoplebooks
       
      Steve Montgomerie
      PeopleSoft/Oracle Administrator
    • David Kurtz
      MessageHow do you know that haven t simply have found a different query in V$SQL? Is it coming from a PSQRYSRV session? Instead of looking up the SQL in v$sql
      Message 2 of 2 , Jul 2, 2004
        Message
        How do you know that haven't simply have found a different query in V$SQL?  Is it coming from a PSQRYSRV session?
         
        Instead of looking up the SQL in v$sql I would suggest that you either use the PeopleTools client trace to see what submitted from the query, or enable Oracle SQL*Trace on the PSQRYSRV and take the SQL from there.

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

        -----Original Message-----
        From: Montgomerie Steve [mailto:steve.montgomerie@...]
        Sent: 01 July 2004 13:47
        To: 'psftdba@yahoogroups.com'
        Subject: PeopleSoft DBA Forum Security and process type

         
        Hello,
        In PS query if a user create s query with type process we can see that the row level security class is applied as expected like below


        SELECT A.EMPLID, A.COMPANY
        FROM PS_JOB A, PS_PERS_SRCH_QRY A1
        WHERE A.EMPLID = A1.EMPLID
        AND A1.ROWSECCLASS = 'DPSVIALL'
        AND ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
        WHERE A.EMPLID = A_ED.EMPLID
        AND A.EMPL_RCD = A_ED.EMPL_RCD
        AND A_ED.EFFDT <= SYSDATE)
        AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
        WHERE A.EMPLID = A_ES.EMPLID
        AND A.EMPL_RCD = A_ES.EMPL_RCD
        AND A.EFFDT = A_ES.EFFDT) )

        However, once the query is executed the security is removed. We can see this by looking in v$sql in Oracle to see the following

        SELECT A.EMPLID, A.COMPANY FROM PS_JOB A WHERE A.EFFDT = (SELECT
        MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID
        AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= SYSDATE)
        AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE A.EMPLID= A_ES.EMPLID
        AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT)
         
        Any thoughts? Also can someone give me a defintion on what type process is? I can't find one in peoplebooks
         
        Steve Montgomerie
        PeopleSoft/Oracle Administrator


        PeopleSoft for the Oracle DBA will be published by Apress in September.  The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.



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