HANA 存储过程

 

You can develop secure procedures using SQLScript in SAP HANA by observing the following recommendations.

Using SQLScript, you can read and modify information in the database. In some cases, depending on the commands and parameters you choose, you can create a situation in which data leakage or data tampering can occur. To prevent this, SAP recommends using the following practices in all procedures.

• Mark each parameter using the keywordsINorOUT. Avoid using theINOUTkeyword.
• Use theINVOKERkeyword when you want the user to have the assigned privileges to start a procedure. The default keyword,DEFINER, allows only the owner of the procedure to start it.
• Mark read-only procedures usingREADS SQL DATAwhenever it is possible. This ensures that the data and the structure of the database are not altered.
  Tip Another advantage to usingREADS SQL DATAis that it optimizes performance.
• Ensure that the types of parameters and variables are as specific as possible. Avoid usingVARCHAR, for example. By reducing the length of variables you can reduce the risk of injection attacks.
• Perform validation on input parameters within the procedure.

我是说HANA ，为什么每次后面都加那么一大串。  language sqlscript sql security definer reads sql data 

这个可以直接创建的SQL窗口里写也行。不用那么麻烦。

 

我都不知道这下面这个什么意思。奇奇怪怪的没明白LOGIC。 很可能是随便复制的。

create procedure "_SYS_BIC"."DEMO/DEMO_SQL_CV/proc" ( IN IP_CGDDH VARCHAR(100),  OUT var_out "_SYS_BIC"."DEMO/DEMO_SQL_CV/proc/tabletype/VAR_OUT" ) 
language sqlscript sql security definer reads sql data 

 as  
 /********* Begin Procedure Script ************/ 
 BEGIN 
 
 SELECT CURRENT_DATE,"_BIC_Z0CGDDH" AS DATE_V FROM "_SYS_BIC"."DEMO/DEMO_CV_IP"
  where "_BIC_Z0CGDDH"= 'ZCCG-1403270935500D';
 
      var_out = 
      
SELECT
     A."/BIC/Z0CGDDH" AS CGDDH,
     A."/BIC/Z0DDZT" AS DDZT,
     B."/BIC/Z0SL" AS SL, 
     B."/BIC/Z0JE" AS JE
FROM "SAPABAP1"."/BIC/AZEZJTO0100" A LEFT JOIN 
     "SAPABAP1"."/BIC/AZEZJTO0200" B ON A."/BIC/Z0CGDDH"=B."/BIC/Z0CGDDH"
      WHERE B."/BIC/Z0JE" IS NOT NULL
      AND A."/BIC/Z0CGDDH" IN(:IP_CGDDH)
      ;

END /********* End Procedure Script ************/

 

 权限过滤，有空看看。

CREATE PROCEDURE "_SYS_BIC"."Purchase::ZAUTH_DYN_FILTER" (IN AUTH_IOBJ NVARCHAR(12) , MD5_CODE NVARCHAR(50) , OUT AUTH_VALUE NVARCHAR(4000) ) 
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER 
    --DEFAULT SCHEMA <default_schema_name>
    READS SQL DATA AS
BEGIN
/***************************** 
    Write your procedure logic 
 *****************************/
--根据MD5码 得到值列表。 
    DECLARE val NVARCHAR(5000) ; 
    --7380563cdbb1061c70bc76c19ef6d9a2
    DECLARE CURSOR c_cursor FOR
    select * from  "SAPABAP1"."/BIC/OHZBWOHD2" where "USERNAME_MD5" = :MD5_CODE  and
    FILTER_COLOMN = :AUTH_IOBJ ORDER BY "VALUE_LOW" asc ;
    val:= '';
    FOR r1 AS c_cursor DO 
    --val:= :val || '''' || '''' || r1.VALUE_LOW  || '''' || '''' || ',';
    val:=  :val || '''' ||  r1.VALUE_LOW  || ''''  || ',';
    IF r1.VALUE_LOW = '*' THEN 
    val:=  '''' || '*' || '''';
    BREAK ;
    END IF ;
    END FOR;
--    AUTH_VALUE := :val ;
    select RTRIM (:val,',') INTO AUTH_VALUE from dummy;
    --AUTH_VALUE := :val || '''' ;
    CLOSE c_cursor; 
    
END;

 

这是一个权限判断的存储过程。想必还是有用，也没看懂。

CREATE PROCEDURE "_SYS_BIC"."Purchase::ZAUTH_INPUT_CHECK" (IN AUTH_IP NVARCHAR(500) , ZIP_MD5 NVARCHAR(100) ,ZIP_IOBJ NVARCHAR(20) ,
                                                    OUT VAR_OUT NVARCHAR(4000) , OUT VAR_FLAG NVARCHAR(3)) 
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER 
    --DEFAULT SCHEMA <default_schema_name>
    READS SQL DATA AS
BEGIN
/***************************** 
    Write your procedure logic 
 *****************************/
 DECLARE SNO INT := 1  ;
 DECLARE ENO INT := 1;
 DECLARE FLAG INT := 1 ;
 DECLARE SVAR NVARCHAR(4000) ;
 DECLARE SCONST NVARCHAR(10);
------------------------------------------------------------------------------------
DECLARE CUSTOMCONDITION CONDITION FOR SQL_ERROR_CODE 10001;/* Custom Error Code = 10001*/
 /*User Defined exception handler */
DECLARE EXIT HANDLER FOR CUSTOMCONDITION RESIGNAL;
 --SELECT ::SQL_ERROR_CODE AS "Error Code", ::SQL_ERROR_MESSAGE AS "Error Message" FROM DUMMY;
/*权限检查段，根据输入的MD5获取权限值*/
------------------------------------------------------------------------------------
SCONST := '''' || '*' || '''';
VAR_OUT := '';

call "_SYS_BIC"."Purchase::ZAUTH_DYN_FILTER"(:ZIP_IOBJ,:ZIP_MD5,:SVAR);

 IF :SVAR = '' THEN
 SIGNAL CUSTOMCONDITION SET MESSAGE_TEXT = '^_^权限不足，请联系管理员增加权限！^_^';
 return;
 END IF;

IF :AUTH_IP = '' OR :AUTH_IP = :SCONST  THEN  
  VAR_OUT := :SVAR ;  
ELSE 
/*对比用户输入和存储过程查到的数据*/
IF :SVAR = :SCONST THEN VAR_OUT := :AUTH_IP;
ELSE
 AUTH_IP := :AUTH_IP || ',';
 WHILE :ENO  <> 0   DO  
 ENO := LOCATE(:AUTH_IP , ',',:SNO) ;
 FLAG := LOCATE(:SVAR , SUBSTRING(:AUTH_IP , :SNO , :ENO-:SNO)) ;
 IF :FLAG = 0 THEN
 SIGNAL CUSTOMCONDITION SET MESSAGE_TEXT = '^_^权限不足，请联系管理员增加权限！^_^';
 return;
 END IF;
 SNO := :ENO+1 ;
 END WHILE;
 
 select RTRIM (:AUTH_IP,',') INTO VAR_OUT from dummy;
 END IF;
 END IF ;

 
 
 IF :VAR_OUT = :SCONST THEN
 VAR_FLAG :=  '%'  ;
 END IF ;
 
 
END;

 

转载于:https://www.cnblogs.com/sakura3/p/8670124.html