By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,322 Members | 1,182 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,322 IT Pros & Developers. It's quick & easy.

"Grant a Role"-Statement in Triggers

P: n/a
Hi !

When I insert a new record to a table "table_A" I want to Grant a Role
"Role_X" to the User "User_Y".

So I made a Trigger who should do this work, but it doesn't:

When I write: "GRANT Role_X TO User_Y" in the Trigger:
--> my Trigger ist invalid (error = ora-04098)
When I declare a varchar-Variable within the trigger, fill it with:
"GRANT Role_X TO User_Y" and then : "EXECUTE IMMEDIATE <variable>"
--> my Trigger is valid, but when it fires I get the error:
ora-04092 - cannot COMMIT in a Trigger

Can anybody help me ?
Is it anyhow possible to GRANT a ROLE after inserting a record ?

Thanks in advance for any help or comments
Ralf
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
You could do
1. Autonomous transaction
2. queue it up in dbms_job.

Why would you want to grant a role in a trigger?
Jim

"Ralf" <Mi**@boehrer.de> wrote in message
news:fa**************************@posting.google.c om...
Hi !

When I insert a new record to a table "table_A" I want to Grant a Role
"Role_X" to the User "User_Y".

So I made a Trigger who should do this work, but it doesn't:

When I write: "GRANT Role_X TO User_Y" in the Trigger:
--> my Trigger ist invalid (error = ora-04098)
When I declare a varchar-Variable within the trigger, fill it with:
"GRANT Role_X TO User_Y" and then : "EXECUTE IMMEDIATE <variable>"
--> my Trigger is valid, but when it fires I get the error:
ora-04092 - cannot COMMIT in a Trigger

Can anybody help me ?
Is it anyhow possible to GRANT a ROLE after inserting a record ?

Thanks in advance for any help or comments
Ralf

Jul 19 '05 #2

P: n/a
Hi Jim,

thanks for your answer.

I want to grant a role in a trigger beacuse of this reason:
I have a database-application in Oracle.
I use an own SCHEMA-Table which owns every oracle-schema my
application is working with (within these schemas there a tables with
datas).
Then I have a USER-Table to manage these users (part of all
Oracle-Users) who have the right to join my application.

Now I want to assign user X to schema Y. So that X can work with the
datas in Y.

For this reason I created a Role which give permission to all tables
in Y.
What I want now is, when assigning user X to schema Y (this inserts a
new
record in my user2schema-table), then the 'y_role' should be granted
to user x.

I'm not sure if this approach is ok or corrupt ?

Best regards
Ralf
"Jim Kennedy" <kennedy-down_with_spammers@no_spam.comcast.net> wrote in message news:<4wZVa.23596$YN5.21747@sccrnsc01>...
You could do
1. Autonomous transaction
2. queue it up in dbms_job.

Why would you want to grant a role in a trigger?
Jim

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.