"Grant a Role"-Statement in Triggers

[Ralf]

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

[Jim Kennedy]

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

[Ralf]

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