Table Ownership

[Former Member]

I'm writing an sp which creates a proxy table, processes the data and then drops the table. To ensure I don't get clashes if multiple users run the sp, the proxy is created with the user as owner i.e. myuser.ptable

However, the sp fails if I use select * from ptable, it insists on the owner name (unless the owner is DBA). To get it wo work I have to build the query into a varchar ('set @mycmd = select * from ' || current user || '.ptable;') and execute it.

Not ideal, especially with long convoluted queries. I thought the system would default to the owner as current user unless otherwise directed.

Am I missing something?

Cheers,

Tim

[VolkerBarth]

Another approach: Don't qualify the table for each caller, instead build a table name that includes the caller name (or some other distinguishing string)...

[VolkerBarth]

Might there be a mismatch between the owner and the caller of the stored procedure? What username is used to create the proxy table - is that the procedure owner (I don't think so, as it would not resolve the name clashing)? If not, the procedure owner won't be able to access the proxy table without owner qualification.

With v11 and above, the SQL SECURITY INVOKER clause may be helpful.

[Former Member]

I'm using current user || '.ptable' The proxy table is definately being created with the correct owner.

[VolkerBarth]

But who the SP owner? If that is DBA, and "current user" is someone else, then even DBA won't see the table without qualification.

[Former Member]

sp owner is DBA. DBA can query the table. Obviously something a bit wonky here!

[Breck_Carter]

Seeing the table without specifying the owner is a different issue, from being able to query a table with or without the owner name.

[MarkCulp]

I think Breck is on the right track here to solving your problem. Remember that "current user" is the user that is logged into the database. If the user that owns the procedure is not the same as the user that is logged in then just using an unqualified table name in the procedure will not work. I.e. you need to make sure that the way you create the table and then reference the table is consistent. Also if the user that is logged in is not the same as the user that owns the procedure and you create the table owned by "current user" then you will need to grant select to the owner of the procedure on the table.

[VolkerBarth]

Wouldn't therefore SQL SECURITY INVOKER solve the issue:

Just create the object without qualification and access it the same way should do, as by definition the procedure's caller (instead of the procedure's owner) will be the table owner.

[MarkCulp]

Yes, using SQL SECURITY INVOKER would work (if "current user" is used as the qualifying user) if the caller is the user that has logged in. But consider the case that the caller (lets call her L) calls procedure A.foo() which then calls the procedure B.Bar() and it is B.Bar() is the procedure that is creating/using/deleting the proxy table and is declared as SQL SECURITY INVOKER - in this scenario the invoker of B.Bar is A not L.

The simplest approach should be to leave the qualification off on all create/select/drop statements and use SQL SECURITY INVOKER on the procedure.

[Former Member]

Thanks guys, it does look like a mismatch between sp owner and table owner. I'll re-think my strategy here.

[VolkerBarth]

Yes, I surely appreciate your deep thoughts - assuring the "if the caller" condition is not that easily done.

Well, the simplest approach might be to get rid of the different "owners" altogether and use a user-specific table name (I assume the according proxy table is just used inside the procedure) - see my comment on the question.

IMHO, making use of SQL SECURITY INVOKER on all procedures is somewhat error-prone, as it isn't the default, and because the default (SQL SECURITY DEFINER) is so commonly used when procedures are created to permit actions otherwise forbidden...