?
Solved

GRANT ROLE/PRIVILEGE IN TRIGGER

Posted on 2004-11-06
3
Medium Priority
?
2,807 Views
Last Modified: 2011-10-03
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.
0
Comment
Question by:k_murli_krishna
  • 3
3 Comments
 
LVL 9

Expert Comment

by:pratikroy
ID: 12512660
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
 
LVL 9

Expert Comment

by:pratikroy
ID: 12512663
i meant have u tried ? :)
0
 
LVL 9

Accepted Solution

by:
pratikroy earned 500 total points
ID: 12513674
==>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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month15 days, 22 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question