I have different table owners.

Table1: Owner DBA
Table2: Owner user1
Table2: Owner user2

I have a trigger on the Table1, which has a select

select @something from Table2 where id = @id

Since there is no any Table2 with DBA owner, so I need to specify the table owner. If I specify user1.Table2, in that case when the user2 is logged in, it will still select from user1.Table. So can I get use some condition there so that it selects from the Table2 of the user which is logged in? Thanks.

asked 22 Feb '13, 02:38

sam's gravatar image

sam
315141624
accept rate: 0%

edited 22 Feb '13, 02:49


Rather than use a permanent table for each user, you could use a GLOBAL TEMPORARY TABLE which has a common schema but a separate instance for each and every connection. Then you wouldn't have to qualify the table name in the trigger; you'd simply reference the global temp table in the trigger, and the contents of that table would be specific to the current connection.

If you have to use a permanent table, I don't see an alternative to constructing the statement within the trigger as a string, and then using EXECUTE IMMEDIATE to execute the constructed statement.

permanent link

answered 22 Feb '13, 09:01

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

@Glenn (Welcome back, by the way!): Does that mean that triggers do run as "SECURITY DEFINER" by design, i.e. each unqualified object will be related to the owner of the trigger table (and the posted sample "select @something from Table2 where id = @id" would lead to a failing trigger in case Table1's owner (i.e. DBA) does not own a Table2)?

(22 Feb '13, 09:50) Volker Barth
Replies hidden

The default namespace for the trigger is as SECURITY DEFINER. The idea for the global temporary table, though, is that you can reference the name of the table - even qualify it, say foo.bar - but the connection executing the trigger will only be able to access the particular instance for that global temp table within the trigger.

(22 Feb '13, 10:56) Glenn Paulley
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:

×60
×27
×19

question asked: 22 Feb '13, 02:38

question was seen: 877 times

last updated: 22 Feb '13, 10:56