Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2009
    Posts
    35

    Unanswered: SQL Server 2005 Basic Security Question

    I want to give someone access to a View I created. I want to create a role. Place users in that role and the only access they will have on my Server is the View.

    Code:
    USE [MyDatabase]
    CREATE ROLE [MyRoleUsers] AUTHORIZATION db_datareader
    GO
    If I use the script above and then I grant SELECT ON on my View to [MyRoleUsers], is this enough security or do I need to do something else to make sure users in that role cannot do anything else on my Server besides select from my view?

    I will do alot more research on my own, I just want to see if my findings match what you all say here.

    Thanks, Mike

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    By specifying AUTHORIZATION clause you're actually just saying that the owner of this role is db_datareader. I recommend not to do that. The owner should be always DBO for users.

    And to answer your question, granting SELECT on a specific object will suffice, unless you have objects that have permissions granted to PUBLIC.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jan 2009
    Posts
    35
    Quote Originally Posted by rdjabarov
    By specifying AUTHORIZATION clause you're actually just saying that the owner of this role is db_datareader. I recommend not to do that. The owner should be always DBO for users.

    And to answer your question, granting SELECT on a specific object will suffice, unless you have objects that have permissions granted to PUBLIC.
    thanks for responding.

    I believe I have accomplished this task.

    When I created the new Database Role, I chose "dbo" as the owner

    In Database Roles, inside Securables, I choose "All objects of type" and "Database" and specifically denied UPDATE, EXECUTE, DELETE AND INSERT


    However, users in this role can still SELECT on all Views (including sys, information_schema etc etc).

    Is there a way to deny permissions on everything accept the view?

    I am hoping that I don't have to go through all views and manually deny access.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    sys and information_schema are used even in SELECTs. I don't think your users have ability to select from any other user view, table, or function.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jan 2009
    Posts
    35
    Quote Originally Posted by rdjabarov
    sys and information_schema are used even in SELECTs. I don't think your users have ability to select from any other user view, table, or function.
    Thanks for the response.

    So you are saying that I shouldn't worry about the outside user being able to select on sys and information_schema as it is harmless?

    Also, when I logged on with the user, I was able to select on views I created but they all have public. I guess I can either take off the Grant Select to Public on all my views or go to each of those views and deny SELECT for those in the role I created?

    Not strong in the DBA department, so I apologize for the questions.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You should not grant any permissions to PUBLIC. And SELECT on sys and information schema should stay on in order not to break some functionality. You can try to deny any permissions to a specific user/role, but your application should not give users a choice what object to interrogate to begin with.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jan 2009
    Posts
    35
    Thanks rdja, I am working on a pretty big project and all my views and stored procedures were granting SELECT TO Public.

    I created a new role and granted SELECT to that role on everything.

    I have never had to look at things from a DBA standpoint, but at my new job, there isn't a DBA.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You might not realize it yet, but I think you just became the DBA.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it always happens like that. on career day in school, did anyone talk about database administration? Not at mine either. Why is that?
    If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry. Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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