469,645 Members | 1,653 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

"Grant a Role"-Statement in Triggers

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
2 5865
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
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.

Similar topics

reply views Thread by Andreas Falck | last post: by
7 posts views Thread by Christopher Murtagh | last post: by
reply views Thread by maersa | last post: by
2 posts views Thread by Ellen Manning | last post: by
5 posts views Thread by Kamil Tezduyar | last post: by
1 post views Thread by Harold Hsu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.