Skip to Content

Insufficient Privilige when calling an Stored Procedure with Restricted User

Hi experts.

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    Jun 09, 2015 at 01:43 PM

    After reviewing some workarounds, unfortunately it seems that the reason is due an $.hdb authorization issue (at least for restricted users).

    The old $.db command works ok without any additional authorization.

    Example working ok:

    var conn = $.db.getConnection();

    var sqlstmt = '{CALL "<SCHEMA>"."<package>::<storedprocedure>"(parameters)}';

    var pcall = conn.prepareCall(sqlstmt);

    pcall.Set<ParType>(x,valuex);

    ..

    pcall.execute();

    conn.commit();

    I wanted to use $.hdb in order to avoid type definition of the parameters, but not possible.

    If someone knows what is the specific authorization related for $.hdb, please comment.

    Thanks

    Eduardo

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Eduardo,

      you are right, that you need at least the SELECT privilege on SYS.PROCEDURE_PARAMETERS in order to execute the procedures, as the $.hdb interface supports as well table type parameters and the $.hdb api determines the input and output parameters before execution.

      However I think that it is no big deal to assign the role RESTRICTED_USER_ODBC_ACCESS to restricted users like the role PUBLIC, which includes the privilege as well, is assigned to all non restricted users.

      Regards,

      Michael