Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Stored procedures, triggers, batches, and user-defined functions

 

Hiding the contents of a procedure, function, trigger, event, or view

To distribute an application and a database without disclosing the logic contained within procedures, functions, triggers, events, and views, you can obscure the contents of these objects using the SET HIDDEN clause of the ALTER PROCEDURE, ALTER FUNCTION, ALTER TRIGGER, ALTER EVENT and ALTER VIEW statements.

Prérequis

You must be the owner of the object, have the ALTER ANY OBJECT system privilege, or have one of the following privileges:

  • Procedures and functions   ALTER ANY PROCEDURE system privilege

  • Views   ALTER ANY VIEW system privilege

  • Events   MANAGE ANY EVENT system privilege

  • Triggers  

    • ALTER ANY TRIGGER system privilege
    • ALTER privilege on the underlying table and the CREATE ANY OBJECT system privilege
    • For triggers on views, you must have the ALTER ANY TRIGGER and ALTER ANY VIEW system privileges

Contexte et remarques

The SET HIDDEN clause obfuscates the contents of the associated objects and makes them unreadable, while still allowing the objects to be used. You can also unload and reload the objects into another database.

The modification is irreversible, and deletes the original text of the object. Preserving the original source for the object outside the database is required.

Debugging using the debugger does not show the procedure definition, nor does procedure profiling display the source.

Note

Setting the preserve_source_format database option to On causes the database server to save the formated source from CREATE and ALTER statements on procedures, views, triggers, and events, and put it in the appropriate system view's source column. In this case both the object definition and the source definition are hidden.

However, setting the preserve_source_format database option to On does not prevent the SET HIDDEN clause from deleting the original source definition of the object.

 Task
  • Use the appropriate ALTER statement with the SET HIDDEN clause.

    Option Action
    Hide an individual object

    Execute the appropriate ALTER statement with the SET HIDDEN clause to hide a single procedure, function, trigger, event, or view.

    Hide all objects of a specific type

    Execute the appropriate ALTER statement with the SET HIDDEN clause in a loop to hide all procedures, functions, triggers, events, or views.

Résultat

An automatic commit is executed. The object definition is no longer visible. The object can still be directly referenced, and is still eligible for use during query processing.

Exemple

Execute the following loop to hide all procedures:



BEGIN
    FOR hide_lp as hide_cr cursor FOR
        SELECT proc_name, user_name
        FROM SYS.SYSPROCEDURE p, SYS.SYSUSER u
        WHERE p.creator = u.user_id
        AND p.creator NOT IN (0,1,3)
    DO
        MESSAGE 'altering ' || proc_name;
        EXECUTE IMMEDIATE 'ALTER PROCEDURE "' ||
            user_name || '"."' || proc_name
            || '" SET HIDDEN'
    END FOR
END;

 See also