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

asked 22 Mar '12, 08:08

TimC's gravatar image

TimC
1515610
accept rate: 0%

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)...

(22 Mar '12, 12:03) Volker Barth

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.

permanent link

answered 22 Mar '12, 08:32

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 22 Mar '12, 08:33

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

(22 Mar '12, 08:44) TimC
Replies hidden

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.

(22 Mar '12, 08:50) Volker Barth

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

(22 Mar '12, 10:56) TimC
Replies hidden

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

(22 Mar '12, 11:06) Breck Carter

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.

(22 Mar '12, 11:20) Mark Culp
Replies hidden
Comment Text Removed

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.

(22 Mar '12, 12:02) Volker Barth

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.

(22 Mar '12, 13:18) Mark Culp

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

(22 Mar '12, 13:44) TimC

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...

(22 Mar '12, 14:04) Volker Barth
showing 4 of 9 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×2

question asked: 22 Mar '12, 08:08

question was seen: 2,560 times

last updated: 22 Mar '12, 14:04