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,

first of all, there is no LAST_COMMIT column in EXA_ALL_TABLES. Could you provide the SQL you do for the view creation? Then we might be able to reproduce your issue.

Best regards

Uwe

ultrak
Padawan

Sorry,  It is sys.exa_all_objects LAST_COMMIT.

 

I should be stripped of my Padawan lable.

This is B.view

CREATE VIEW "REGULATE"."MajorTablesBalancingV2_vw" AS


select B.table_name as 'Exasol Table Name', B.TABLE_SCHEMA , upper(a.key) as 'Source System Table Name' , e."DAOwner", b.TargetTbl_count as 'Exasol Table Row Count' , SourceTbl_count as 'Source System Table Row Count',
a.Sourcetbl_count - b.targettbl_count as Variance, Current_timestamp as 'Source Row Count DTS' , c.LAST_COMMIT
from
(select 'TAK' as key, count(*) as SourceTbl_count from seeth.tabx nolock
union all
select 'BACKME' as key, count(*) as SourceTbl_count from seeth.tabz nolock where bigkey not in (401,402)
union all
select 'TRANS' as key, count(*) as SourceTbl_count from seeth.tabm nolock where bigkey not in (401,402)
union all
select 'Results' as key, count(*) as SourceTbl_count from seeth.tabn nolock
union all
select 'Account' as key, count(*) as SourceTbl_count from seeth.onmyaccount nolock where bigkey not in (401,402)
union all
select 'Transactions' as key, count(*) as SourceTbl_count from seeth.transactions nolock where service_area_id not in (401,402)
union all
select 'SECTION' as key, count(*) as SourceTbl_count from seeth.tablett nolock where bigkey not in (401,402)
) as a
left outer join
(
select 'TAK' as key,TABLE_SCHEMA, TABLE_NAME, TABLE_ROW_COUNT -1 as TargetTbl_count from SYS.EXA_DBA_TABLES where TABLE_NAME = 'table_r' and TABLE_SCHEMA in ('SCHEMA_X','REGULATE')
union all
select 'BAK' as key,TABLE_SCHEMA, TABLE_NAME, TABLE_ROW_COUNT -1 as TargetTbl_count from SYS.EXA_DBA_TABLES where TABLE_NAME = 'table_s' and TABLE_SCHEMA in ('REGULATE')
union all
select 'BAK1' as key,TABLE_SCHEMA, TABLE_NAME, TABLE_ROW_COUNT as TargetTbl_count from SYS.EXA_DBA_TABLES where TABLE_NAME = 'table_t' and TABLE_SCHEMA in ('SCHEMA_X_STAT')
union all
select 'Account' as key,TABLE_SCHEMA, TABLE_NAME, TABLE_ROW_COUNT as TargetTbl_count from SYS.EXA_DBA_TABLES where TABLE_NAME = 'table_u' and TABLE_SCHEMA in ('SCHEMA_X_STAT')
UNION ALL
select 'key1' as key,TABLE_SCHEMA, TABLE_NAME, TABLE_ROW_COUNT - 1 as TargetTbl_count from SYS.EXA_DBA_TABLES where TABLE_NAME = 'table_v' and TABLE_SCHEMA in ('REGULATE')
UNION ALL
select 'key2' as key,TABLE_SCHEMA, TABLE_NAME, TABLE_ROW_COUNT as TargetTbl_count from SYS.EXA_DBA_TABLES where TABLE_NAME = 'table_w' and TABLE_SCHEMA in ('SCHEMA_X_STAT','REGULATE')
union all
select 'keyx' as key,TABLE_SCHEMA, TABLE_NAME, TABLE_ROW_COUNT as TargetTbl_count from SYS.EXA_DBA_TABLES where TABLE_NAME = 'table_x' and TABLE_SCHEMA in ('SCHEMA_X_STAT','REGULATE')
union all
select 'keyy' as key,TABLE_SCHEMA, TABLE_NAME, TABLE_ROW_COUNT - 1 as TargetTbl_count from SYS.EXA_DBA_TABLES where TABLE_NAME = 'table_y' and TABLE_SCHEMA in ('SCHEMA_X_STAT','REGULATE')
union all
select 'keyz' as key,TABLE_SCHEMA, TABLE_NAME, TABLE_ROW_COUNT as TargetTbl_count from SYS.EXA_DBA_TABLES where TABLE_NAME = 'table_z' and TABLE_SCHEMA in ('SCHEMA_X_STAT','REGULATE')
) as b
on a.key = b.key
left outer join SYS.EXA_ALL_OBJECTS c
on b.TABLE_SCHEMA = c.ROOT_NAME
and b.TABLE_NAME = c.OBJECT_NAME
LEFT OUTER JOIN
REGULATE."EDIT_Data_vw" e
ON b.table_name = e."ExasolTBLNM"
order by 1,2

A.view is select * from B.view

 

 

exa-Uwe
Moderator
Moderator

Thank you for sharing the SQL! I have been able to reproduce the issue:

drop schema if exists regulate cascade; -- do as sys ...
drop schema if exists seeth cascade;
create schema regulate;
create schema seeth;
create table tabx as select * from dual;
create table regulate."table_r" as select * from dual;

CREATE VIEW "REGULATE"."MajorTablesBalancingV2_vw" AS
select B.table_name as 'Exasol Table Name', B.TABLE_SCHEMA , upper(a.key) as 'Source System Table Name' , b.TargetTbl_count as 'Exasol Table Row Count' , 
SourceTbl_count as 'Source System Table Row Count',
a.Sourcetbl_count - b.targettbl_count as Variance, Current_timestamp as 'Source Row Count DTS' , c.LAST_COMMIT
from
(select 'TAK' as key, count(*) as SourceTbl_count from seeth.tabx nolock
) as a
left outer join
(
select 'TAK' as key,TABLE_SCHEMA, TABLE_NAME, TABLE_ROW_COUNT -1 as TargetTbl_count from SYS.EXA_DBA_TABLES where TABLE_NAME = 'table_r' and TABLE_SCHEMA in ('SCHEMA_X','REGULATE')
) as b
on a.key = b.key
left outer join SYS.EXA_ALL_OBJECTS c
on b.TABLE_SCHEMA = c.ROOT_NAME
and b.TABLE_NAME = c.OBJECT_NAME
order by 1,2;
select * from "REGULATE"."MajorTablesBalancingV2_vw";

create view regulate.a as select * from "REGULATE"."MajorTablesBalancingV2_vw";

drop user if exists jim cascade;
CREATE USER jim IDENTIFIED BY "jim";
GRANT CREATE SESSION TO jim;
grant select on regulate.a to jim;
grant select any dictionary to jim;
select * from regulate.a; -- do as user jim

User sys sees a timestamp in LAST_COMMIT of view a while user jim sees NULL only, also after granting SELECT ANY DICTIONARY to him.

Not sure that's related to 7.1, though. I have no version 6 or 7.0 environment at hand to test that. Maybe someone else can try with the above test case.

ultrak
Padawan

Thank you for reproducing.  I am looking for a solution that does not involve giving the reportuser dba privileges. 

exa-Uwe
Moderator
Moderator

If I do this in the above test case, user jim (the reportuser) sees the timestamp in LAST_COMMIT:

revoke select any dictionary from jim; -- do as sys ...
grant select on regulate to jim;

Granting DBA or SELECT ANY DICTIONARY is not required. Does that help?

Best

Uwe

ultrak
Padawan

That worked! Thank you. 

drumcircle
Rising Star

This is not working for me.  DBA rights are required to return results from SYS.EXA_ALL_TABLES in a view to which user has full USAGE and SELECT access.

         INNER JOIN SYS.EXA_ALL_TABLES AS T ON (

drumcircle
Rising Star

Yep, you need select on the selected schema for this to work.  Apologies, you got it, Uwe!

danila
SQL-Fighter

I'm also experiencing such a problem on version 7.1 (7.1.2. if to be more specific).

All queries are running under SYS user, which has "SELECT ANY DICTIONARY" privilege.

I can successfully run following two queries:

 

select *
from exa_dba_audit_sql
where start_time between '2022-03-10 00:00' and '2022-03-10 00:01';
create or replace view Sandbox.v_Audit_Test as
with cte1 as (
	select *
	from exa_dba_audit_sql
	where start_time between '2022-03-10 00:00' and '2022-03-10 00:01'
)
select *
from cte1;

 

But next queries are failing with error SQL Error [42500]: insufficient privileges for accessing view EXA_DBA_AUDIT_SQL

 

create view Sandbox.v_Audit_Test as
select *
from exa_dba_audit_sql
where start_time between '2022-03-10 00:00' and '2022-03-10 00:01';
select * from Sandbox.v_Audit_Test;

 

 

So, not only I can't select from the view, but also I can't create a view without cte.

@exa-Uwe any ideas?