4 Replies Latest reply on Apr 29, 2016 4:41 PM by Raj Jamadagni

    User Defined Reports: can we block Edit option or have password protection agaist editing?

    3004645

      Hello,

      In User Defined Reports, can we block Edit option (make report SQL as read only or not visible to end user), or can we have a password protection against editing report SQL?

      In  other words, can we prevent the end users from being able to apply any changes to the SQL built-in in the report or even seeing what SQL is built-in in the report?

      (password protection on report SQL would be great)

      Thank you,

      vr

        • 1. Re: User Defined Reports: can we block Edit option or have password protection agaist editing?
          thatJeffSmith-Oracle

          No, but my question is, why?

           

          You can't hide SQL or prevent the user from running SQL in a worksheet, so why try to lock-down a report?

           

          I suppose you could try making the reports.xml read only on the file system, but I don't know if that would make the reports functional or not, give it a try. That would only prevent changes, not hiding the SQL.

           

          View > Log > Statements will show all the SQL sent from SQLDev to the database BTW. Also if they have access to V$SESSION and associated views...

          • 2. Re: User Defined Reports: can we block Edit option or have password protection agaist editing?
            3004645

            Thank you, Jeff.

            Here is my explanation.

            I am using User Defined Reports (UDRs) to execute specific database tasks not necessarily a simple query.

            Let's take this as an example of how i am using UDRs for business purposes such as managing database vault realms :

            ---

            DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(

              realm_name    => 'HR Schema Protective Realm',

              object_owner => v_obj_owner,

              object_name => '%',

              object_type => '%' );

            ---

            INSERT into MY_LOG_TABLE values ....

            commit;

            -----


             

            So, I do all this as UDR and from the UDR menu.

            --------

            I distribute this tasks to the end user community as the XML file so the end users can easily deploy it into their personal SQL Developers and use it for the purpose of managing Realms or any other business business purposes as the so-called "Push-the-Button-Solution". End users do not have to write any code, all they have to do is just CLICK on report and pass the parameter in the pop-up window.

             

            I found that some end users, open this "report" for Edit and remove INSERT into MY_LOG_TABLE.

            This means that I am not able to verify who was running this tasks and when it was done. All logging details are gone.

             

            Now, i hope you see my point: I would like to block any changes to my SQL. Or allow the Edit option only after the password was provided for the Edit purposes.

             

            *** In short, i do not mind if the end users can see full SQL body, but i would like to block them from removing or modifying any part of the SQL body.

             

            Thank you,

            vr

            • 3. Re: User Defined Reports: can we block Edit option or have password protection agaist editing?
              thatJeffSmith-Oracle

              If it were me I'd have a talk with those folks, and if it was malicious, terminate them.

               

              But, people being people, I'd add the logging SQL to the procedure itself. That way it can't be touched.

               

              Or write a new procedure that calls DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM() and does the INSERT, and have the report call that.

              • 4. Re: User Defined Reports: can we block Edit option or have password protection agaist editing?
                Raj Jamadagni

                Jeff Smith Sqldev Pm-Oracle wrote:

                 

                Or write a new procedure that calls DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM() and does the INSERT, and have the report call that.

                 

                That probably is the best solution.