Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2009
    Posts
    35

    Unanswered: SQL Server 2005 Roles and other Database Permissions Question

    Database db1
    Database db2

    I have created a Database Role in db1 and I have added 1 user. I have views in db1 that pull from tables in db2. In the views on db1, I have granted select on the role.

    The problem is that when I try to select from the views in db1 under the user in the Database Role on db1, I get an error that basically says the user doesn't have access to Database db2.

    I thought that the user would be able to select from the view because the user would get its permissions from the view?

    Since Database Roles are Database Specific, I cannot add SELECT permissions to the Database Role for another database.

    What is the best practice to solve this problem?

    Do I create a script on Database db2 that contains GRANT SELECT statements for all tables needed?

    Do I go to the user and GRANT SELECT on the Database db2?

    Is there a way to create a Server Role, instead of a Database Specific Role?

    For now, I will go to the user and add SELECT permissions to Database db2.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by maikeru-sama
    Do I create a script on Database db2 that contains GRANT SELECT statements for all tables needed?
    ^^This^^

    The only question one might ask is why are the views in a different database to the tables? Why not put the view in the same database?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2009
    Posts
    35
    pootle flump, Database db2 is actually the database of our ERP Package (JD Edwards).

    I don't want any of our company's custom database objects (views, stored procedures etc etc) inside of the JD Edwards Database so we can keep everything separate and not have to worry about our objects getting blown away during upgrades and ESUs (Electronic Software Updates).

  4. #4
    Join Date
    Jan 2009
    Posts
    35
    Instead of granting access to those tables to the user. I wonder if the grant select statements could be GRANT SELECT ON <TABLE> TO Database.Schema.Database Role?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes, assign the permissions to the role not the user
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2009
    Posts
    35
    Quote Originally Posted by pootle flump
    Yes, assign the permissions to the role not the user
    So are you saying create a new role in Database db2 and place the user in there?

    Would it be wrong to give it the same name as the role in Database db1?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes
    No
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2009
    Posts
    35
    Quote Originally Posted by pootle flump
    Yes
    No
    Thanks pootle.

    I went ahead and created another role in Database db2 with the same name and wrote grant select scripts for the role.

    One last question, is there a reason that "Public" is given read,write,update and delete priveledges on tables by default?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Public should not have those permissions by default.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2009
    Posts
    35
    You are right.

    I was looking at some of the tables in our ERP database and they seem to all have permissions to public.

    I am going to assume that the install of the ERP package gave those permissions to public.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That basically means every user will have those permissions.
    That's the dumbest thing I've heard in quite some time.

    If you want your role to be secure then you can DENY permissions to objects - this will over rule the permissions assigned via Public without affecting any other users that the application might rely on.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmm, Pootle, explicit DENY will overwrite any GRANT given explicitly or through another role. You should always use REVOKE in this case if the permission is explicitly GRANTed.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Jan 2009
    Posts
    35
    Quote Originally Posted by pootle flump
    That basically means every user will have those permissions.
    That's the dumbest thing I've heard in quite some time.

    If you want your role to be secure then you can DENY permissions to objects - this will over rule the permissions assigned via Public without affecting any other users that the application might rely on.
    To be fair, we won't have any users in the JD Edwards database. Users will access the data through the Software on the web, which uses one account.

    However, I agree, it is strange. I will ask the consultant why they do that.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rdjabarov
    Hmmm, Pootle, explicit DENY will overwrite any GRANT given explicitly or through another role. You should always use REVOKE in this case if the permission is explicitly GRANTed.
    Exactly - that is my point.

    If Public has lots of permissions GRANTed to objects then every user the OP creates will have full permissions to all these objects. If the OP then REVOKES the permissions granted to Public then I would expect the application to stop working.

    As such, I was suggesting leaving Public as it is. Any new roles will have all the permissions the OP wants them to have by default, plus many more. In order to secure this role but not compromise any permissions that the application relies on, the OP should then DENY these extra permissions to this new role.

    Make sense now?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by maikeru-sama
    To be fair, we won't have any users in the JD Edwards database. Users will access the data through the Software on the web, which uses one account.
    By users I meant a SQL Server User object, not the staff using the application!
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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