Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

SELECT ANY DICTIONARY not working for VIEW

ultrak
Padawan

In 7.1.[24], the select any dictionary does not appear to be working for a user when calling a view  (A.view) in one user schema that calls a view from another schema (B.view).  The B.view runs a complex select that joins with EXA_ALL_TABLES to get the last_commit column.  User with DBA able to run A.view and the LAST_COMMIT row populates, USER with "grant select" on A.view and B.view and has SELECT ANY DICTIONARY gets the results of A.view back except the LAST_COMMIT column is null.  The same if USER runs B.view.

TIA for any clarification.

12 REPLIES 12

exa-Uwe
Moderator
Moderator

Hi Danila,

I would expect your second CREATE VIEW command to fail with "object V_AUDIT_TEST already exists".

Apart from that, I cannot reproduce this error: When I do this as sys in 7.1.6 (I have no 7.1.2 at hand) it works fine:

drop schema if exists sandbox cascade;
create schema sandbox;

create or replace view Sandbox.v_Audit_Test as
select *
from exa_dba_audit_sql limit 10;

select * from Sandbox.v_Audit_Test;

Best regards

Uwe

exa-Nico
Community Manager
Community Manager

@danila Check the permissions of the owner of the schema containing the view. The owner of the schema must have SELECT ANY DICTIONARY because when executing views, it is done with the permissions of the owner, not the permission of the user running the SELECT statement

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

danila
SQL-Fighter

Thanks. You were right. The problem was in lack of schema owner's permissions.