Insufficient Privilige when calling an Stored Procedure with Restricted User
I have created a restricted database user in order HANA assigning only a custom role (No PUBLIC, HCP_SYSTEM, HCP_PUBLIC roles assigned in order to avoid any unnecessary access to the repository and/or SQL console).
The assigned custom role has EXECUTE privilege to an stored procedure and has read and write access to all the tables included in the stored procedure and EXECUTE privilege on the related schema.
The stored procedure has "SQL SECURITY INVOKER" command.
The stored procedure is called from an XSJS file like this:
// Get the DB connection
var connection = $.hdb.getConnection();
// Load the Delete Procedure
var procDelete = connection.loadProcedure("<schema>","<package>::<storedprocedure>");
// Execute the Delete Procedure
procDelete( parameters ) ;
I get the following error:
"Error: $.hdb.Connection.executeProcedure: SQL error. NR: 258, ERROR: insufficient privilege: Not authorized at ptime/query/checker/query_check.cc:3289"
Could you please comment what specific access/privilege is missing for executing the stored procedure correctly by this restricted user?
Thanks and advance.
Eduardo Espinosa replied
I could call the stored procedure via "$.hdb" with the restricted user, without issues, by granting "RESTRICTED_USER_JDBC_ACCESS" role.
This role contains a bunch of system tables with "SELECT" privilege. The insufficient privilege error message that I received before seems was related to a lack of authorization when "$.hdb" objects was reading references in system tables.