privileges to roles are getting lost

SQL-Fighter

Hi guys,

I created a role on my database for reporting purposes. 

CREATE ROLE REPORTING;

Assigned users can select on some views from our access layer. For example:

GRANT SELECT ON ACCESSLAYER.DIM_CUSTOMERS TO REPORTING; 

However, after some time they loose the right to see/select on particular views.
I have no idea why this is the case. Do you guys have any ideas how to solve this problem?
Any help would be much appreciated!
Best

Moritz

1 ACCEPTED SOLUTION

Xpert

As I stated in the previous post:

Use templates or save the DCL with your create statements so that the grants will be execute whenever you execute a SQL.

Strongly depends on how you work with the database.

If you edit views in an UI tools it's up to the tool to generate all necessary DCL statements when dropping and recreating objects.

In regards of DBeaver I can say that this is not done on purpose:
* Query on EXA_DBA_OBJ_PRIVS -> 121 seconds (with or without filter)

So if DBeaver would query these upon change of views just to check for role grants on the object would take 2 minutes.

Most likely it would be much faster in your installation as we have ~ 100k roles in the database

View solution in original post

6 REPLIES 6

Xpert
I have never heard of this kind of problem.

Do you have audit enabled for this database?

* Can you check if the view was recreated? (create or replace)
* any dcl on the view?
* was the role recreated?


SQL-Fighter

Hi Charlie,

Thanks for your quick reply.

We just found out that recration of the view results in loosing the right to see the view for reporting users.

Do you know how to solve this problem?

I am not sure whether audit is enabled or not. How can I check on this?

The role was not recreated.

I made use of dcl by granting the view to the reporting role. 

Best

Moritz

Xpert

You can see in EXAOperation in the database details:

Charlie_0-1600166121689.png


We have naming conventions for views and roles and use python script with jinja2 library for templating and view generation (all are custom written scripts that we also use for our other database systems).

SQL-Fighter

I just found out that auditing is enabled. 
How do you suggest to solve the problem with the rights?

Best

Moritz

Xpert

As I stated in the previous post:

Use templates or save the DCL with your create statements so that the grants will be execute whenever you execute a SQL.

Strongly depends on how you work with the database.

If you edit views in an UI tools it's up to the tool to generate all necessary DCL statements when dropping and recreating objects.

In regards of DBeaver I can say that this is not done on purpose:
* Query on EXA_DBA_OBJ_PRIVS -> 121 seconds (with or without filter)

So if DBeaver would query these upon change of views just to check for role grants on the object would take 2 minutes.

Most likely it would be much faster in your installation as we have ~ 100k roles in the database

View solution in original post

Moderator
Moderator

Hi Moritz,

could it be that you re-create some of the views sometimes?

Unfortunately, we lose the granted privs for views (and also tables, btw) if you do CREATE OR REPLACE VIEW.

Best regards

Uwe