4 Replies Latest reply on Mar 21, 2019 1:22 PM by user832387

    Different DDL for trigger

    user832387

      Any idea why trigger's code is different for a user with DBA role and developer with Read-Only access using  SQL Developer 18.4?

      See attached screenshots for same trigger: WHEN condition is missing for account with SELECT grant on objects

       

      DBA view
      Developer view
        • 1. Re: Different DDL for trigger
          thatJeffSmith-Oracle

          did you know you can paste the screenshots directly into your message?

          • 2. Re: Different DDL for trigger
            user832387

            Thanks! Fixed it

            • 3. Re: Different DDL for trigger
              thatJeffSmith-Oracle

              are you logged in as the user that owns the trigger?

               

              we make this call to get the ddl - if you run it in a worksheet, what do you see?

               

              WITH src AS (

                  SELECT ROWNUM,

                         line,

                         text,

                         origin_con_id

                    FROM sys.dba_source -- when you are logged in as DBA, check the View > Log > Statements panel to see what runs when you do not have access to dba views

                   WHERE type = :type

                     AND owner  = :owner

                     AND name   = :name

              )

              SELECT text

                FROM src,

                     (

                         SELECT MAX(origin_con_id) max_orig

                           FROM src

                     )

              WHERE origin_con_id = max_orig

              ORDER BY line

              • 4. Re: Different DDL for trigger
                user832387

                > are you logged in as the user that owns the trigger?

                      No. User has only SELECT granted on the table.

                > we make this call to get the ddl - if you run it in a worksheet, what do you see?

                      For DBA user shows all lines returned.

                      For non-DBA user, I replaced DBA_SOURCE with ALL_SOURCE - No rows returned

                 

                > When user don't have access to DBA views, this code is shown in log pane

                   

                select text prefix, null trigger_body from all_source

                                where name=:NAME

                                and owner=:OWNER

                                and USER = :OWNER

                                and TYPE = :TYPE

                union all --disjoint

                select 'trigger '||description, trigger_body from all_triggers

                                where /*vacuous bind to match*/:TYPE='TRIGGER' and owner = :OWNER and trigger_name = :NAME

                                and USER != :OWNER

                 

                Mystery solved : Its missing when_clause column