insufficient privilege to select from database view

[Former Member]

Dear experts,

I cannot execute sql select from database view, below is my steps.

1. logon HANA studio with SYSTEM user, create 4 users USER_A, USER_B, USER_C and USER_D;

     CREATE USER USER_A PASSWORD Initial1;

     CREATE USER USER_B PASSWORD Initial1;

     CREATE USER USER_C PASSWORD Initial1;

     CREATE USER USER_D PASSWORD Initial1;

2. logon HANA studio with user USER_A and create table T_TEST in schema USER_A as following:

     CREATE TABLE USER_A.T_TEST (ID INTEGER);

3. logon HANA studio with USER_A and grant select privilege on schema USER_A to USER_B,USER_C and USER_D;

     GRANT SELECT ON SCHEMA USER_A TO USER_B;

     GRANT SELECT ON SCHEMA USER_A TO USER_C;

     GRANT SELECT ON SCHEMA USER_A TO USER_D;

4. logon HANA studio with USER_B and grant select and create any privilege on schema USER_B to USER_C and USER_D;

     GRANT SELECT ON SCHEMA USER_B TO USER_C;

     GRANT SELECT ON SCHEMA USER_B TO USER_D;

     GRANT CREATE ANY ON SCHEMA USER_B TO USER_C;

     GRANT CREATE ANY ON SCHEMA USER_B TO USER_D;

5. logon HANA studio with USER_C and create view V_TEST in schema USER_B as following:

     CREATE VIEW USER_B.V_TEST AS SELECT * FROM USER_A.T_TEST;

6. logon HANA studio with USER_D and execute below SQL:

     SELECT * FROM USER_B.V_TEST;

     then I receive error says: "insufficient privilege"

Question: step 4 already assign select privilege on schema USER_B to USER_D, why step 6 still show error? However, USER_C can execute 'SELECT * FROM USER_B.V_TEST' successfully because USER_C is owner of USER_B.V_TEST. Will appreciate much if you can explain how the authorization check during user USER_D executing 'SELECT * FROM USER_B.V_TEST'

Regards,

Guoliang Zhou

[Former Member · Accepted solution]

Hi Guoliang,

In step 3, please use GRANT SELECT ON SCHEMA USER_A TO USER_C WITH GRANT OPTION; instead.

Best regards,

Wenjun

[Former Member · Accepted solution]

Yeah, you said it. And I already know this is correct solution to solve this issue. I just don't understand, why? How HANA check authorization when USER_D perform select * from V_TEST

[Former Member · Accepted solution]

Hi Guoliang,

Please check this Object Privileges - SAP HANA Developer Guide - SAP Library

"

In case of views, the SAP HANA database implements the standard SQL behavior. A user has the authorization for an operation on a view if the following is true:

• The privilege for operations on the view has been granted to the user or a role assigned to the user.
• The owner of the view has the corresponding privileges on the underlying objects with the option to grant them to others.

"

Best regards,

Wenjun

[Former Member · Accepted solution]

Regarding your scenario,

• The privilege for operations on the view has been granted to the user or a role assigned to the user.

-> GRANT SELECT ON SCHEMA USER_B TO USER_D;

• The owner of the view has the corresponding privileges on the underlying objects with the option to grant them to others.

-> GRANT SELECT ON SCHEMA USER_A TO USER_C WITH GRANT OPTION;

[Former Member]

Hi Guoliang,

I wrote a blog to explain the problem in details. If you are interested, you can have a look.

Best regards,

Wenjun

[KulDeepJain]

Hi,

User_C is owner of

     CREATE VIEW USER_B.V_TEST AS SELECT * FROM USER_A.T_TEST;

but you have not provided "GRANT SELECT ON SCHEMA USER_C TO USER_D;"

Just provide this access and see it should execute.

Thanks,

KDJ

[Former Member]

Hi KDJ,

USER_D execute SELECT * FROM USER_B.V_TEST, why need select privilege on scheme USER_C?

BTW, I have tried GRANT SELECT ON SCHEMA USER_C TO USER_D, still not solved

Regards,

Guoliang Zhou