July 12, 2017 at 5:19 am
Hello forum,
I'm developing a database for a international company, and the users are clerks in several international branches. The database will contain general objects (tables, procs etc), suitable for all branches, as well as branch-specific objects which only the local clerks should be able to access.
Sounds like a job for schemas, e.g "General", "Country X", "Country Y".
The users are bundled in a Windows AD Group, and this group is set up as login for SQL Server. Unfortunately, a user group can't get a default schema assigned. This is greyed out for groups, it's only possible for single users.
Now, the catch is: there are some users which need to have access to more than their local country. Say, user John has to be able to "act" as a "british" John as well as a "french" John. So, he has to be able to work with multiple schemas. So, I can't decide the branch (and the schema) on the user, and even not on the group.
How should I separate my users and provide them with the necessary permissions (and only those)? Do I have to rewrite each and every "General" sp for each of the local schemas? How can John work on "his" two schemas, and others on their single local schema? (The "hat" John wears is always only a single one; he may act in the French or British schema, but not at the same time.)
Additional kudos for an extra answer: at present, we use a development Server (MSSQL 2008 R2). Production will be on a MSSQL 2014 server (one day in the not-so-far future). How would the user-branch-schema-assignment work there?
Thanks for any hint
Martin
July 12, 2017 at 5:44 am
I'm wondering if you seem to be over thinking this, as AD groups actually seem like a good solution to this.
Say you have following Schemas for the following Counties:
FR - France
GB - Britain
USA - United States of America
DE - Germany
We'll also say you have a Scheme world which is effectively your "global" schema.
You also have several AD groups. let's again, say they are unsurprisingly called:
French_Users
British_Users
American_Users
German_Users
You can then set each of these AD groups as a login, and grant the SELECT/WRITE/EXEC on their relevant schemas, and on the world schema. Any users in those Ad groups will be able to use their relevant Schema and the global one.
Now, take John. John is part of both the French_Users and British_Users Ad groups. this means that he will be able to access both of the relevant schemas as well, as group permissions are inherited. Being a member of multiple groups means you inherit the permissions of all of those groups. In John's case, he can use objects in the FR, GB AND world schemas.
What you will need to do is have certain objects on all schemas. There's not a lot you can do about this. Like you said, you can't set default schemas for an AD group, as a user could be a member of multiple groups. If that is the case, what default would they use? You'll there have to make sure you always reference your schema, however, for the set up you have, I would likely recommend doing that in your SQL anyway.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 12, 2017 at 7:48 am
Thanks for your reply. But the snag is harder than that. 🙂
John may wear the british or the french hat, but not both at the same time. So, whenever he's acting "French", he shouldn't be able to interfere with "british" data. And vice versa. The combined group memberships would allow him to see and update everything the groups he's in permit him to at the same time.
If John wants to change the "french" schema he's working in, I want him to log off and log in as a "british" John. He may be the same person, but he's got different roles, and a "French" user shall under no circumstances mess around with british data.
July 12, 2017 at 8:13 am
atrus2711 - Wednesday, July 12, 2017 7:48 AMThanks for your reply. But the snag is harder than that. 🙂
John may wear the british or the french hat, but not both at the same time. So, whenever he's acting "French", he shouldn't be able to interfere with "british" data. And vice versa. The combined group memberships would allow him to see and update everything the groups he's in permit him to at the same time.
If John wants to change the "french" schema he's working in, I want him to log off and log in as a "british" John. He may be the same person, but he's got different roles, and a "French" user shall under no circumstances mess around with british data.
Then he'll need different logins. You can't change a login's permissions on what they're going to do that time they log in. If they can only have access to one part of the system when doing one thing, and only the other if they're doing something else, they will need separate accounts for the separate areas.
In your case, that will mean "John" will need 2 AD accounts.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply