Insufficient Privilige when calling an Stored Procedure with Restricted User

[former_member216203]

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

[former_member216203 · Accepted solution]

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

[former_member216203 · Accepted solution]

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.

[Former Member · Accepted solution]

Hi Eduardo,

I'm facing the same issue. But I can not accept the solution you are providing. I think the idea of using restricted users is to deny any JDBC or ODBC access..

But if I give "RESTRICTED_USER_JDBC_ACCESS" to my users they can easily connect to the system via JDBC and perform requests on the database tables... So for me its same like non-restricted users.

Is there any other solution?

For me its very interesting that the old db interface is still working. Thank you for that insight.

[former_member216203 · Accepted solution]

Hi Mathias,

I agree with you that the solution is not acceptable but this is the one I have found so far for this issue. If you check the "RESTRICTED_USER_JDBC_ACCESS", basically it only contains "SELECT" access to system/standard tables or views:

CONSTRAINTS(SYS)

DATA_TYPES(SYS)

DUMMY(SYS)

FUNCTION_PARAMETERS(SYS)

FUNCTIONS(SYS)

...

PROCEDURE_PARAMETERS(SYS)

PROCEDURES(SYS)

...

etc

One option is that we could try granting "SELECT" privilege only to PROCEDURE_PARAMETERS(SYS) and PROCEDURES(SYS) objects and test instead of granting the whole "RESTRICTED_USER_JDBC_ACCESS" role.

Thanks and Regards

Eduardo

[michael_vogel1 · Accepted solution]

Hi Eduardo,

the $.hdb interface handles passing of table parameters to procedures in the following way:

1. It creates a local temporary table in the schema of the user with the table type of the parameter
   1. therefore the user needs the SELECT privilege on the schema of the table type
   2. furthermore the user needs a schema to create the local temporary table
   3. as restricted users have no schema, they cannot call such procedures
2. The data, passed to the procedure, is inserted into the table
3. The procedure is executed, passing a reference to the table as input

So you have the following ways with the $.hdb interface:

1. Create a procedure with only scalar input parameters
2. Use a sqlcc connection and assign it to a non restricted user (sqlcc user scenario (technical user)) in order to use the privilege of the non restricted user to execute the procedure, as this user has an own schema.

Regards,

Michael

[former_member216203 · Accepted solution]

Hi Michael.

I am using only scalar input parameters, no table parameters and a fact is that my stored procedure can be called ok via $.hdb only if the user has SELECT privileges on the system tables that define an stored procedure or related objects. Privileges included for example in "RESTRICTED_USER_JDBC_ACCESS" role.

I would expect the same access/privilege behavior when calling the stored procedure via $.db or $.hdb, but it is not.

Thanks and Regards

Eduardo

[michael_vogel1 · Accepted solution]

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