k_murli_krishna
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.
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.
i meant have u tried ? :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.