"Grant a Role"-Statement in Triggers

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Ralf
    #1

    "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
    Tags: None
    • Jim Kennedy
      #2
      Re: &quot;Grant a Role&quot;-Statement in Triggers

      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" <Miko@boehrer.d ewrote in message
      news:fa624200.0 307300725.7b4fb e38@posting.goo gle.com...
      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

        Comment

        • Ralf
          #3
          Re: &quot;Grant a Role&quot;-Statement in Triggers

          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_spamm ers@no_spam.com cast.netwrote in message news:<4wZVa.235 96$YN5.21747@sc crnsc01>...
          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
          >

            Comment

            Previous template Next
            Working...