Find Procedure

To find procedure, standard HANA table: “SYS”.”PROCEDURES”, can be used. For example: Searching for Procedure, which contains specific code, the definition column can be used:

select * from "SYS"."PROCEDURES"
where definition like '%???%';

Templates

When creating SAP® HANA Procedures, I’m using SQL code and the following templates:

Standard Procedure

DROP procedure <procedure_name>;
CREATE PROCEDURE <procedure_name>
(
in IN_DATA varchar(255),
out OUT_DATA varchar(5000)
) language sqlscript
sql security invoker as
BEGIN

  -- Code

  OUT_DATA := '';
END;

Procedure for Analytic Privileges

The difference from the standard procedure is in the number of attributes: only one output attribute, and in the security definer/invoker.

DROP procedure <procedure_name>;
CREATE PROCEDURE <procedure_name>
(
out OUT_DATA varchar(5000)
)
language sqlscript
sql security definer
reads sql data as
BEGIN

  -- Code

  OUT_DATA := '';
END;

If an user need to use second person’s procedure the second person can allow it for example by following code:

GRANT EXECUTE ON SCHEMA "<second person's schema>" TO <user>;
GRANT SELECT ON SCHEMA "<second person's schema>" TO <user>;

Sometimes is necessary to grant privileges to other used schemes/objects to the user. For more details check the GRANT Statement in the HANA documentation, please (link provided below, in the Resources chapter).

In case the procedure causing the insufficient privilege error, try to grant access to the _SYS_REPO user with grant option:

GRANT EXECUTE ON SCHEMA <second person's schema> TO _SYS_REPO WITH GRANT OPTION; 
GRANT SELECT ON SCHEMA <second person's schema> TO _SYS_REPO WITH GRANT OPTION;

Resources

• GRANT Statement (Access Control)