Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009

    Question Unanswered: Trigger privilege issue -- SQL Server 2005

    Pardon me if this is a newbie question; Im an Oracle DBA and relatively new to SQL Server.

    In Oracle, I created and tested the following scenario:

    As user U1, I created table U1.TEST and granted INSERT privileges on it to user U2;
    As user U2, I created an identical table U2.TEST and granted INSERT privileges on it to user U3;
    As user U2, I inserted a row into U1.TEST to confirm that he had INSERT privileges;
    As user U2, I created an INSERT trigger on U2.TEST that performs an INSERT into U1.TEST;
    As user U2, I inserted a row into U2.TEST and confirmed that my trigger inserted a row into U1.TEST;
    As user U2, I granted INSERT privileges on U2.TEST to user U3;
    As user U3, I inserted a row in U2.TEST and confirmed that that the row got inserted **and** that a corresponding row got inserted, by the INSERT trigger on U2.TEST, into U1.TEST.

    This is, as far as I know, standard DBMS behavior across a number of DBMSs; if you have an INSERT trigger on U2.TEST, it fires by default with the privileges of the trigger definer (U2, in this case, who has INSERT privileges on U1.TEST), and you dont have to grant any additional privileges to user U3 beyond INSERT privileges on U2.TEST. I replicated this test in MySQL and in PostgreSQL, among others.

    However, I dont see the same behavior in SQL Server. I carried out the same steps (after the usual stumbling around in a relatively new to me DBMS), and I received this error:

    Msg 916, Level 14, State 1, Procedure TEST_TRIGGER, Line 9
    The server principal "U3" is not able to access the database "U1" under the current security context.

    I did some web research and discovered some database-specific settings that seemed to promise success, but they did not work. I should add that its important to me, both from a security standpoint and a workflow standpoint, not to have to grant privileges on U1.TEST to user U3, etc. Among other reasons, other users (e.g., user U4, who doesnt yet exist) that I cant know about at present may be granted INSERT privileges on U2.TEST in the future. Based on the behavior of other DBMSs, I think I should be able to grant U4 et al INSERT privileges on U2.TEST and do nothing else.


  2. #2
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    I think it is behaving as designed. It, also, works the same in DB2 as what you describe here for SQL Server. Also, I would tend to agree with the behavior.
    I tend to think it is the same in life in general. In most places if you were to rent a dwelling, the contract states that you cannot in turn rent that dwelling out to someone else as that other person was not vetted by the owner as being an eligible/satisfactory tenant. Rental cars are the same way, they would rent the car to you, but you are supposed to be the only driver of that vehicle, unless you make prior arrangements with that rental company (an additional fee, a copy of the other driver's license, etc...).
    I think what you have done is describe a security flaw in these other DBMS systems. Perhaps if you report it to the appropriate vendors, they will resolve the issue?
    Dave Nance

  3. #3
    Join Date
    Nov 2002
    I once met an Indian DBA when I didn't know SQL Server, and only DB2 OS/390

    I designed a pretty good process to do some identifying of some data anomaly issues...worked pretty good...until it stopped

    We all got in a this was a head dba mind you...and I asked why that would be? DB2 wouldn't have a problem with this

    ...." Why Ask Why...Life is full of mystery's"

    I almost dropped right then and there

    Oracle and SQL Server are COMPLETELY different animals

    Do you have a very specific question?

    Maybe you can help us help you, and just ask us a "Starting Gate" question about SQL Server

    First, what platform are you on? are you working with a server or an express version?

    And how do you connect to the SQL Server (instance)?

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Dec 2009

    Trigger privilege issue -- SQL Server 2005

    Thanks for the responses. I really wasn't looking to spark a holy war between SQL Server acolytes and Oracle advocates. I do understand that the two are different. That's the reason I posted a question here, looking for help.

    The real-life application that my example was based on is a before/after value auditing facility. In that context, when U2.TEST is chosen to be audited, a U1.TEST audit table is created by the application, INSERT privileges on it are granted to U2, and the DML trigger is created on U2.TEST. When a user -- any user with the appropriate privileges -- performs a DML operation on U2.TEST, one or more rows should be inserted by the trigger into U1.TEST reflecting before/after column values.

    The universe of users who may perform DML operations on U2.TEST is not necessarily known nor complete at the time of audit setup; as I said earlier, users U4...Un may be granted DML privileges on U2.TEST at some unknown future date, after the audit setup has taken place.

    Furthermore, this being an auditing application, users (other than U2, the table owner) who are granted privileges to perform DML operations on U2.TEST should ideally not even be aware that a record of their operations is being created.

    Since the table owner is creating the trigger during the setup of auditing for that table, and has been granted INSERT privileges on the audit table (U1.TEST in my example), I think it's reasonable behavior for the DBMS to perform such INSERTs without forcing the application to also grant INSERT privileges on U1.TEST to future users U4...Un. Certainly I understand that you could argue the other case, but both approaches have some validity.

    In any event, I do understand that, by default, SQL Server does not work that way. So, specifically, I am asking whether some property(ies) of the database(s), or configuration settings, or another method can be manipulated to make it behave the way I described.

    And as for me reporting Oracle's default behavior in this situation as a security risk so that they can "fix" it, I think we can all pretty much guess what their response would be, can't we? There is, after all, an ANSI standards committee for relational databases, and it's safe to say that Oracle has had, uh, "input" on this topic.

  5. #5
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Quote Originally Posted by cmcclain View Post
    There is, after all, an ANSI standards committee for relational databases, and it's safe to say that Oracle has had, uh, "input" on this topic.
    Triggers are not described by the standard, as far as I know, so the implementation is left to the vendor.

    I think you can change the security context of the trigger, at least in the 2008 version, by specifying EXECUTE AS SELF in the trigger definition.
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    how are these extra users getting access to insert to u2.test? wouldn't giving insert access to the audit table in the same manner resolve your issue? That is the way we handle this type task on both SQL server and DB2.
    Also, I was not attempting to start any holy war, but merely suggesting why it is not advisable and giving real life reasons as well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts