SQL SECURITY OWNER

Database
Enthusiast

SQL SECURITY OWNER

Hi,

 

I have two stored procedures which both have SQL SECURITY OWNER defined.

 

One SP is a nested proc in the other.

 

The nested SP generates a CREATE TABLE AS xx WITH DATA AND STATS statement and executes via DBC.SysExecSQL.

 

This produces an error: USER DOES NOT HAVE STATISTICS ACCESS TO xx

 

After granting the user (who is calling the SP) STATISTICS access the procedure works successfully.

 

Why is the SP taking the user's permissions as opposed to the SP's permissions, considering SQL SECURITY OWNER was defined? Is it related to using nested SPs?

 

e.g.

 

REPLACE PROCEDURE FDWS53_INT_ME_META.Mask_Table_SP
(
    IN	InSourceDB	VARCHAR(128),
    IN	InTable		VARCHAR(128)
)

SQL SECURITY OWNER

BEGIN

DECLARE ResultSet				VARCHAR(500);

CALL FDWS53_INT_ME_META.Mask_SP(InSourceDB,InTable,'Y',ResultSet);

END;
REPLACE PROCEDURE FDWS53_INT_ME_META.Mask_SP
(
    IN    InSourceDB    VARCHAR(128),
    IN    InTable       VARCHAR(128),    
    IN    InMaskFlag    CHAR(1),
    OUT   ResultSet     VARCHAR(500)
)

SQL SECURITY OWNER

BEGIN

    SET CrOrigTB =   'CREATE TABLE '|| WorkDB ||'.'|| _InTable ||'_ORIG AS '|| _InSourceDB ||'.'|| _InTable ||' WITH DATA AND STATISTICS;';
    SET _StatementText = CrOrigTB;
    CALL DBC.SysExecSQL(CrOrigTB);
	
END;

 

 

-- Jonny