Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Data permissions for reporting user

    Hi all,

    Looking for some advice here about how to approach my current conundrum.

    I have a server with a dozen or so databases created by a 3rd party for some software that we've purchased. Because of the mess involved I've created a separate database for reporting purposes contains a large number of objects that reference the other databases using 3-part naming.

    Now I'm on to securing this stuff up.
    - I don't want my users having any write permissions so have added them to the db_denydatawriter role.
    - I don't want them having access to certain schemas within the reporting database so have granted select and execute permissions to the specific schemas.

    I like simple.

    Unfortunately because of the 3 part naming they require access to the other databases... but I would love to not have to do this as I want to force them in to using the objects I have created.

    What are my options?
    George
    Home | Blog

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    I'm not a big fan of having the reporting database on the same server as production, so I usually break them up and have the reporting database elsewhere. I know it's a bit of work, but I create the ELT processes and this way I'm keeping the users away from using the production server. Not much help in your situation.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It might go that way in the end but for now I don't have the time to write the necessaries and we're going for a replicated set up any way so 95% of the reports will be pointed at the replicated server anyway. We have a need for some real-time reporting and I will be attacking those personally.

    Still, I am just not comfortable letting my other users (non-IT report writers) seeing the schema of the other databases and getting any of their "bright" ideas...


    Am I being too much of an ogre?
    George
    Home | Blog

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    I'm not so sure I'd put a lot of effort into using the new database for reporting because once replication is inplace then won't they have to rewrite the new reports? Better to focus on replication and build the reports once.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    They shouldn't have to re-write anything - just re-point the connection(s). I think they'll mostly be using DSN's so should be easy-peasy to do.

    I won't have the replicated server until next year and the timescales don't allow such a delay on reports development.
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How difficult would it be to create a script to grant the users only exactly the SELECT permissions that they need? That would be my first choice.

    Second option, CREATE SYNONYM so that everything the users have access to ends up in one schema of one database. Permissions might get a bit wonky to set up, I've never done this on the scale that you imply.

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

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hmmm, that might just give me what I want. Will give it a test tomorrow if I can.
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Shame, hit the same problem. Going to have to grant db_datareader role in each database.

    Not ideal but will have to do. Just have to get a bigger stick to deter the report writers from stepping outside the lines

    If anyone has any other bright ideas I'd love to hear them
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Why grant db_datareader? Once you identify the objects needed for the synonyms you can tweak the script to grant SELECT permissions on only those objects.

    First of all, PowerShell is your friend. You can make a set of PowerShell scripts that will automate EVERYTHING you need simply.

    Build two lists, users to create/run reports and the objects (views, tables, sprocs, functions, etc.) needed for reporting. Put these into separate tables for easy reference.

    Create a new reporting group in each of the source databases (from the objects table). Instead of using db_datareader, grant this group the necessary permissions on all of the base objects (from the objects table).

    Add the reporting users to each source database, and put the new users into the Reporting Groups you created (to get the permissions right/consistent).

    If you see fit, create a new shell database with synonyms for all of the source objects. This adds a step, but it keeps the users from needing to wander all over creation to find the things that you want them to use!

    Be sure to write the create source user script so that it can either be run incrementally or can take a command line argument so that it is easy to add new users later.

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

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Thanks Pat, very good points made.

    Would definitely be better to not blindly use the db_datareader role. You meantion creating a new "group", do you mean a new role in each database with SELECT permissions to the necessary objects?

    Why would you use PS? I can't see anything in there that can't be done using T-SQL at this moment.

    I don't think the synonyms are going to add anything at this stage. I had considered them before but that's because I wanted to give a database an alias... I hate inconsistent naming conventions!

    I'll try have a play with this on Friday.
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As long as everything resides on a single server, there isn't much need for PowerShell. PowerShell does make things simpler when you have to scale out to include many servers and non-SQL tasks.

    Yes, I meant Role instead of Group. I flit back and forth between products and can't remember who calls a widget what anymore!

    The synonyms bring one factor that is important to me, and that is simplicity for the users... They only need to know to go one place and you can instill a consistent naming standard that helps them make the right choices intuitively... They don't get frustrated remembering which dohicky is called what or stored where. If everything is in one place, with consistent names then the users can get the answers they need quicker, easier, and simpler.

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

Posting Permissions

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