RE: Trigger Question
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>, <Mathias.Zarick@xxxxxxxxxxxx>, <srcdco@xxxxxxx>
- Date: Fri, 20 Feb 2009 08:09:48 -0600
Unfortunately not, "not" as in that doesn't fix the issue. I do grant all of
these SELECT accesses directly to a role, but whether you grant privileges
directly to a schema or to a role, the act of a GRANT will modify the object,
which is where the potential locking issue comes. You can see this from
DBA_OBJECTS.LAST_DDL_TIME, as this changes with any GRANT statement of that
object.
David C. Herring | DBA, Acxiom Automotive
630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax
1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. | www.acxiom.com
-----Original Message-----
From: Mercadante, Thomas F (LABOR) [mailto:Thomas.Mercadante@xxxxxxxxxxxxxxxxx]
Sent: Friday, February 20, 2009 8:00 AM
To: Herring Dave - dherri; Mathias.Zarick@xxxxxxxxxxxx; srcdco@xxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Trigger Question
Could you issue the grant to an Oracle Role and solve the issue that way?
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Herring Dave - dherri
Sent: Friday, February 20, 2009 8:34 AM
To: Mathias.Zarick@xxxxxxxxxxxx; srcdco@xxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Trigger Question
This is EXACTLY what I had to do. I was at the mercy of a poorly designed
system and had to use this type of thing as a kind of hack. The trigger is an
AFTER CREATE trigger and submits an execute of a procedure through DBMS_JOB
(obviously this was 9i), with a sysdate+1/64800 delay.
The GRANT can be locked out due to developer code performing other statements
immediately after creating the table, so in my procedure I try up to 10 times
(1 second waits inbetween) before giving up on the requested grant.
David C. Herring | DBA, Acxiom Automotive
630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax
1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. | www.acxiom.com
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Mathias Zarick
Sent: Friday, February 20, 2009 2:24 AM
To: srcdco@xxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Trigger Question
Hi Scott,
you cannot grant directly out of the trigger, but you could submit a job that
does
this for you. Set the interval to null and the job will disappear after
execution.
but take care, trigger should not fire to often as this might fill up the job
queue
intolerably.
snippet:
declare
v_job binary_integer;
begin
dbms_job.submit(v_job,'grant_select(''' || ora_dict_obj_owner || ''',''' ||
ora_dict_obj_name || ''');');
end;
/
HTH Mathias
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Scott Canaan
Sent: Thursday, February 19, 2009 10:21 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Trigger Question
I have a question on how to grant select access to a table as part of the
table create process, inside the database. I was hoping to use a system
trigger (after create on schema). In testing, I created the following trigger
and stored procedure:
create or replace trigger test_create_trig after create on schema
when (ora_dict_obj_type in ('TABLE','VIEW'))
declare
begin
grant_select(ora_dict_obj_owner,ora_dict_obj_name);
end;
/
create or replace procedure grant_select (obj_owner varchar2, obj_name varchar2)
as
pragma autonomous_transaction;
begin
execute immediate 'grant select on ' || obj_owner || '.' || obj_name ||
' to test_user2';
end;
/
When I try to create a table as test_user, which I want select permission on
for test_user2, I get the following error stack:
SQL> create table test2 (a number);
create table test2 (a number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04020: deadlock detected while trying to lock object TEST_USER.TEST2
ORA-06512: at "TEST_USER.GRANT_SELECT", line 4
ORA-06512: at line 5
SQL>
The request is to have all tables (and views) owned by one user to be
readable by another user, but not by giving "select any table" to the other
user. I granted all of the existing tables and views, but want to
automatically be able to grant future ones as they are created. It seems like
there should be a way to do this and it shouldn't be too hard, without relying
on the developers to remember to put the grants in their code. I can't believe
I'm the first one to have this issue.
We are using Oracle 10.2.0.4.
Thank you,
Scott Canaan '88 (Scott.Canaan@xxxxxxx)
(585) 475-7886
"Life is like a sewer, what you get out of it depends on what you put into it."
- Tom Lehrer.
***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.
If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.
If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.
Thank You.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts: