Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Setting Permission

    HI guys, I am going to be working with a group thats going to have a SQL Database. I need to set up permissions on the database for the users, but they dont log into their computers with a user name and password. I think that the users should have passwords and should be set up in groups on SQL Server (Active directory). Because more then one office is going to be connecting to this database. What do you guys think? Any advice would be greatly appreciated. How should I handle permissions on sql server?
    Last edited by desireemm; 05-04-09 at 11:14.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Hmmm....if they do not log in at all, and have no AD accounts, then obviously there is no security whatsoever on the data. After all, if the computers can not tell one person from another, then there can't be any security checks. If this is how they want to treat the data, then you can just grant permissions to the public group, and no distinctions will be made (short of an explicit deny).

    Personally, I would prefer having the users in AD, and then those users in groups, assigned to database roles. That way, one group can not go change/see other groups' data. The group administrators can manage membership in the groups (and not bother me), and when you need to audit a permission, you just need to check the applicable database role, and be done with it.

    Other groups are a bit of a problem, depending on how these groups relate. Are they groups that have distinct yet similar data? (like two separate companies that do the same thing?). Then I would rather keep them in separate databases. If they are two groups that use the data in different ways (like an order entry group, and a reporting group), then the permissions should be relatively straightforward to plan out.

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by MCrowley
    Hmmm....if they do not log in at all, and have no AD accounts, then obviously there is no security whatsoever on the data. After all, if the computers can not tell one person from another, then there can't be any security checks. If this is how they want to treat the data, then you can just grant permissions to the public group, and no distinctions will be made (short of an explicit deny).

    Personally, I would prefer having the users in AD, and then those users in groups, assigned to database roles. That way, one group can not go change/see other groups' data. The group administrators can manage membership in the groups (and not bother me), and when you need to audit a permission, you just need to check the applicable database role, and be done with it.

    Other groups are a bit of a problem, depending on how these groups relate. Are they groups that have distinct yet similar data? (like two separate companies that do the same thing?). Then I would rather keep them in separate databases. If they are two groups that use the data in different ways (like an order entry group, and a reporting group), then the permissions should be relatively straightforward to plan out.

    My point exactly thank you Mccrowley. Thats what I thought, because these users know nothing about SQL Server. I am doing this on the side (freelance work) So they are my clients

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Apparently they have 3 offices and they will be entering in distinct yet similar data. But the otther offices we are not sure of. They do the same thing but the Management wants to introduce the database to them, because they are using an excel spreadsheet instead. So thats my big on the side project..I'm pretty excited. I am working with a non profit organization helps Tribal members get an education. Its an Indian Education Center and apparently there is allot of them.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Merging the data for yet a third group (Management) could be interesting. That makes me lean back toward a single database. It now depends how the peoons...er...the workers will be interacting with the data.

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    How the other offices will be interacting with the data is what I will need to find out. But first things first we need to get the first offices up and running on SQL Server. I will be talkign with the tech today, going to request we implement Active directory

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just so that you understand up front, implementing Active Directory isn't difficult, but it is a LOT of work for the admin as you set up AD accounts, groups, etc and start to assign permissions. This is something that should be part of the network design from the start, but rarely happens that way. At least in my experience, AD is usually forced on a company "from above" either management, auditors, or government edict.

    It sounds like this organization is like most, and has no notion of security at all right now. Implementing AD (or any form of security) is a great idea for many reasons, but it probably won't be an easy one.

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

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by Pat Phelan
    Just so that you understand up front, implementing Active Directory isn't difficult, but it is a LOT of work for the admin as you set up AD accounts, groups, etc and start to assign permissions. This is something that should be part of the network design from the start, but rarely happens that way. At least in my experience, AD is usually forced on a company "from above" either management, auditors, or government edict.

    It sounds like this organization is like most, and has no notion of security at all right now. Implementing AD (or any form of security) is a great idea for many reasons, but it probably won't be an easy one.

    -PatP
    Ok so worse case scenario, if they dont set up AD what should I do. Cause for me that just throws a huge wrench in the project

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You can impose SQL Server authentication on them. This will depend a fair amount on what sort of application they are using. Strict fat client/server works fine. Website with access to a database gets hairier.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My "Plan B" would be to:

    1. Create groups (user defined roles) within the database
    2. Grant permissions to those roles, and those roles ONLY, avoid granting permissions directly to users at any cost.
    3. Create SQL logins and the corresponding users
    4. Make the created users members of the appropriate SQL roles.

    This plan should get you the bulk of what you'd get from the AD solution without having this particular change driving your Domain Admin crazy.

    The business needs to consider this as a "warning shot" about security... They may not have to implement security today, but it will come and the longer that they put the security implementation off the more work it will be! The Domain Admin needs to start thinking about AD NOW... Once the question has been raised, it will rarely go away and if auditors or the government raise the question it can NOT be dodged!

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

  11. #11
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by MCrowley
    You can impose SQL Server authentication on them. This will depend a fair amount on what sort of application they are using. Strict fat client/server works fine. Website with access to a database gets hairier.
    Access 2007 is the front end

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    MS-Acces goes both ways for authentication. You can use either AD or SQL authentication (or both) within the application.

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

  13. #13
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    just curious, but should I set up a login page on the application side (Access)??

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You must set up a login page within the application to use SQL Authentication from either a web page or an MS-Access application.

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

  15. #15
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Ok thanks guys I will do some research on how to do that. I am going to push for Active Directory, it would make things so much easier
    Last edited by desireemm; 05-04-09 at 16:54.

Posting Permissions

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