Authorization issue with views in schema

[patrickbachmann]

Hi folks,

I've created a shared schema where our developers create shared objects like tables.  I have full rights to the schema however one of my developers has created a view (not in content but inside the schema) over certain tables that I also have access to and he wants to allow others to use his view.  I can see his view however I can't perform data preview.  When I create my own view this person can not preview mine either.  Exactly what objects/privileges are needed to share these types of views?  It's almost like these views are PRIVATE and not PUBLIC and only the creator can use them yet I can still see them and look at their definition.  The SQL inside I can easily run myself successfully.

Thanks,

-Patrick

[patrickbachmann]

Nobody has had this issue before?  Has anybody used views inside schema (not content node but catalog node).

-Patrick

[lucas_oliveira]

Hey Patrick,

You can see the definition because you either have DATA ADMIN or CATALOG READ system privileges. That does not mean you can perform object operations on it (SELECT, INSERT, UPDATE, DROP, ...).

For your scenario what you'll need is select privilege over the view. Best practice would be to create a repository role and add the privileges to it and grant that role to your users.

BRs,

Lucas de Oliveira

[patrickbachmann]

Hi Lucas,

My example view does a simple select over a table.  So lets say schema XYZ and table BKPF.  So both myself and other user that created the view have select against XYZ.  We can both individually cut & paste the SQL from the view into SQL editor and execute successfully.  But only the creator of the view can run the preview on the view.  I tried GRANT against the view and i get message I'm not authorized.  I had the creator of the view GRANT against the view and even he was not authorized.

-Patrick

I just created a message with SAP with following steps to recreate;

STEPS TO RECREATE

1) Create a view in a schema with user A

2) Verify you can perform data preview with user A

3) Try to perform data preview with user B. Authorization error.

NOTE: Both users have exact same access to source schema and tables in select statement

[patrickbachmann]

PS here's my grant statement;

GRANT SELECT ON "SCHEMA"."VIEWNAME" TO USERNAME

[lucas_oliveira]

That's weird. Having select privilege to a test view I created allowed to Data preview it.

Here's my test:

create role auth_remove;
grant select on SYSTEM.AUTHVIEW to auth_remove;

grant auth_remove to authuser_remove;

Data preview worked nicely here when using user authuser_remove. However, you can query your view with user B right?

BRs,

Lucas de Oliveira

[patrickbachmann]

Ok it's helpful to know that it at least works for you so I have hope and will keep pursuing this.  In my case User B can not query a view that I create.  He can see my view definition but he can not preview.  He can open the view definition, copy the SQL to his clipboard and run it successfully in his SQL editor.  I know strange right.  I will keep digging and post explanation here as soon as I find it.

Thanks,

-Patrick

[lucas_oliveira]

I'd start with the jdbc trace here. Data preview is nothing more than a fancy front-end for SQL statement execution. So your version might be trying to do something else other than querying the view.

BRs,

Lucas de Oliveira