Link to home
Start Free TrialLog in
Avatar of k_murli_krishna
k_murli_krishnaFlag for India

asked on

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.
Avatar of pratikroy
pratikroy

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.
i meant have u tried ? :)
ASKER CERTIFIED SOLUTION
Avatar of pratikroy
pratikroy

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial