Re: Trigger Question
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: jack@xxxxxxxxxxxx
- Date: Thu, 19 Feb 2009 22:16:57 +0000
I have to say that this looks like a requirement to use a technical method
to circumvent proper change control and design processes. Surely whoever is
responsible for writing the CREATE TABLE statement ought also be responsible
for the GRANT statements... Doing it any other way ensures that the
developer cannot be held responsible for the use of the data that is
contained in the tables they create since they do not know what it is.
Niall
On Thu, Feb 19, 2009 at 10:10 PM, Jack van Zanen <jack@xxxxxxxxxxxx> wrote:
> I just created a procedure and a database job for this. There is a delay,
> but the developers should put it intheir code to begin with so they won't
> complain too much.
>
> Jack
>
> 2009/2/20 Scott Canaan <srcdco@xxxxxxx>
>
> 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.
>>
>>
>>
>
>
>
> --
> Jack van Zanen
>
> -------------------------
> This e-mail and any attachments may contain confidential material for the
> sole use of the intended recipient. If you are not the intended recipient,
> please be aware that any disclosure, copying, distribution or use of this
> e-mail or any attachment is prohibited. If you have received this e-mail in
> error, please contact the sender and delete all copies.
> Thank you for your cooperation
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
