GRANT ROLE/PRIVILEGE IN TRIGGER

1) We have Oracle 9.2.0.1 server on windows.

2) We have requirement that on insert of a record in one table, trigger should be fired and a role/privilege should be granted to user.

3) Just grant statement is not working but execute immediate 'grant band to user scott' is working in stored procedure.

4) The same statement is not working in trigger with error 'CANNOT COMMIT/ROLLBACK in TRIGGER'. If we call procedure from trigger also, the same error is raised.

5) How to get around this problem OR are there alternative approaches. Please provide links if necessary or mail documents to kmurlikrishna@gmail.com.
LVL 17
k_murli_krishnaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pratikroyCommented:
Have you tries using the AUTONOMOUS TRANSACTION ?

Try this :

create or replace procedure pr_new_users is
pragma autonomous_transaction;
begin
execute immediate 'grant band to user scott';
commit;
end;
/

create or replace trigger triggername after insert on tablename
begin
pr_new_users;
end;
/

Let me know if it helped.
0
pratikroyCommented:
i meant have u tried ? :)
0
pratikroyCommented:
==>Let us take an example to demonstrate this :

SQL> connect mydba/mydba
Connected.
SQL> select tname from tab;

TNAME
------------------------------
TAB_SORTS

SQL> select * from tab_sorts;

no rows selected

==>So, my DBA has just one table which has no records. Let us create another table so that we could create the trigger on it.

SQL> create table new_users (uname varchar2(30));

Table created.

==>Now, let us create the user which will be granted a role if a row is inserted in the above table.

SQL> create user newscott identified by newscott;

User created.

SQL> grant connect to newscott;

Grant succeeded.

==>Now let us connect to the new user and see if he can see the data in the tables of mydba :

SQL> connect newscott/newscott;
Connected.
SQL> select * from mydba.tab_sorts;
select * from mydba.tab_sorts
                      *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from mydba.new_users;
select * from mydba.new_users
                      *
ERROR at line 1:
ORA-00942: table or view does not exist

==> So the new user cannot see the data. Ofcourse we have'nt granted him the privileges yet on the tables that are owned by mydba. So, let us now create a role that will have the privilege on the new table that we have just created.

SQL> connect mydba/mydba
Connected.
SQL> create role band;

Role created.

SQL> grant select, insert, update, delete on new_users to band;

Grant succeeded.

==> Now let us create the autonomous transaction that will be called through our trigger (which we will create after this).

SQL> create or replace procedure pr_new_users is
  2  pragma autonomous_transaction;
  3  begin
  4  execute immediate 'grant band to newscott';
  5  commit;
  6  end;
  7  /

Procedure created.

==> And the trigger on the new table :
SQL> create or replace trigger tr_new_users after insert on new_users
  2  begin
  3  pr_new_users;
  4  end;
  5  /

Trigger created.

==> Now, we should be all set. Let us insert a record in this new table, and see if the new user can see it from its own schema.
SQL> insert into new_users values('newscott');

1 row created.

SQL> connect newscott/newscott;
Connected.
SQL> select * from mydba.tab_sorts;
select * from mydba.tab_sorts
                      *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from mydba.new_users;

UNAME
------------------------------
newscott

==> Bingo. The new user could see the data in the new_users table of mydba, and could not see the data in the tab_sorts table.

==> Are you wondering if this would have worked without the Autonomous transaction ? Well let us see ....

SQL> connect mydba/mydba
Connected.
SQL> create or replace procedure pr_new_users is
  2  begin
  3  execute immediate 'grant band to newscott';
  4  end;
  5  /

Procedure created.

==> Now let us revoke the privileges before inserting another row in the new_users table and see if our new user can still see the data in new_users ?

SQL> revoke band from newscott;

Revoke succeeded.

SQL> insert into new_users values ('newscott1');
insert into new_users values ('newscott1')
            *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "OQ01DBA.PR_NEW_USERS", line 3
ORA-06512: at "OQ01DBA.TR_NEW_USERS", line 2
ORA-04088: error during execution of trigger 'OQ01DBA.TR_NEW_USERS'

==> hmmm ... we get the same problem that you were encountering .... so you know ... u could resolve it by using the autonomous transaction ... :)

==> Hope this helped !!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.