using foreign (temporary) tables

[gadeyne.bram]

Hi,

For some queries we'd like to use some physical (temporary) tables.

I found that this is supported with the create foreign temporary table syntax.

I created a MySQL database tmptables that can hold these temporary tables. I've also configured the user's role to have create, read, update, delete, execute and alter priviliges on the model. The role can also create temporary tables.

If I understand the documentation correctly it is necessary to create te table in the mysql database but a reference can be obtained using this syntax. So I created the table in the tmptables database. The I executed the following command:

create foreign temporary table tmptbl_bram_test(

     patientid inteher

     OPTIONS (UPDATABLE true)

) on tmptables;

The create foreign temporary table command executes without an error.

Now I try to add something to the table. But this fails with a message user <bram@teiid-security> is not entitled to action <CREATE> for 1 or more of the groups/elements/procedures.

insert into tmptbl_bram_test(patientid) values(1);

Is my understanding of this command correct? Where should I set this create action privilege?

With kind regards

Bram

[rareddy]

Bram,

Did you create roles just for this functionality, or you are generally using the data-roles feature? You do not have to turn on "data-roles" just for this feature. If you want to enable the temp table creation role, see this one XML Definition - Teiid 8.10 (draft) - Project Documentation Editor

Your command looks correct to me.

Ramesh..

[gadeyne.bram]

Hi Ramesh,

I do use different data-roles so that some users only have read priviliges and others have write and read priviliges on different sources.

My role does have the create temporary table privilige. I checked this in the xml file inside the vdb file.

It seems like the create foreign temporary table statement is not the problem. The problem occurs on insert.

FYI: I'm using teiid 8.7.

In the log files it says that I might get more information if I increase the log level. This is already DEBUG. What logger should this loglevel be decreased on?

[rareddy]

Right. Couple things to check

1) Enable "allow-update" role at model level. I am thinking this will work.

2) Enable "allow-update" on temp-table and see if that works. If this works, then the issue I have with this is, this temp-table table name can be defined at runtime, so the previous "allow-create-temporary-tables" should have granted this role automatically and code is not not doing that, for that you can open a JIRA.

Ramesh..

[shawkins]

I see a couple of things.  The initial permission checks that are performed with create foreign temp are:

create temporary tables

create on the target schema

The latter has a mis-reported exception message when evaluated as it will still mention create temporary tables.  The secondary check is probably unnecessary at that point as it presumes you will be performing inserts, which is not necessarily the case with the current incarnation of foreign temp tables.

The next issue I see is that without the grant all option our checks of the column permissions don't function correctly as we don't fully associate them with the containing schema.  So even if you have create on the target schema you would still get an exception.  An issue is needed to clear these up.

[shawkins]

[TEIID-3242] issues with data roles and foreign temp tables - JBoss Issue Tracker was logged for this.

[gadeyne.bram]

I can confirm that this works on 8.9.1