June 9, 2015 at 2:53 pm
Hello -
I have a SQL Server 2012 Enterprise Edition install that I am needing to do the following:
- Provide db_owner access to a single user. Have the db_owner privilege apply for the user in all Schemas of the database (this is a generic AD account that is being used for an application).
They are wanting me to do this because they do not want to get so granular as to tell me all of the objects that the user will need specific access to in any of the given Schemas of the database(s).
I have 8 databases, and each one has additional Schemas. These are all contained in a Default instance.
Sounds straight forward, but I am finding that without explicitly applying permissions to the various objects in the Schemas that are not the default Schema for this user, I am a bit stuck.
Is there any way to have the db_owner permission apply to the single user for all the Schemas in a given database (without making them a Sysadmin)?
Any advice or assistance would be appreciated.
June 9, 2015 at 3:41 pm
WARNING, UNDERSTAND AND TEST YOUR SECURITY WHEN MAKING CHANGES. SECURITY IS SERIOUS BUSINESS. YOU SHOULD ASSUME THIS POST IS COMPLETE JUNK.
Heh anyways...
Just curious here, could you just let that user own the schemas?
-- maybe do this for each schema?
ALTER AUTHORIZATION ON SCHEMA::<schema name goes here> TO <your db principal here>
where <schema name goes here> is replaced by the actual schema name, and <your db principal here> is replaced by a user or role.
alternatively, you could maybe just grant permissions on those schemas to your user or role.
You really want to check the implications of your changes on possible "ownership chaining" that could give access to things that maybe you didn't really want to give that user access to!
alter authorization -> https://msdn.microsoft.com/en-us/library/ms187359.aspx
granting permissions on a schema -> https://msdn.microsoft.com/en-us/library/ms187940.aspx
ownership chaining -> https://technet.microsoft.com/en-us/library/ms188676(v=sql.105).aspx
I don't really know much about that, just throwing it out there, maybe some experts can expand on this sort of stuff!
June 9, 2015 at 3:50 pm
Honestly - I think it would just be better to give explicit perms to the designated user in this situation, because as you pointed out (and pointed out quite well), this is security and our environment involves HIPAA and PHI data that I do not want compromised in any fashion or form.
That being said - I don't know that making the AD user the schema owner is a bad idea, but I don't know what other compromises could come of such an change in ownership just yet.
If you have to choose - would you rather provide explicit perms to the account (assuming the object dependencies for the given process are not overly deep), or change the Schema owner?
June 9, 2015 at 6:49 pm
Honestly, if you have an environment that makes use of schemas to that extent, I think its well worth drilling down in a test environment and making a study of it, accompanied by a good bit of reading, regardless of your choice with this immediate issue.
I would personnaly prefer not to have to run a stack of grant statements if I could be confident in safely getting the job done in a more leveraged fashion, but I can certainly appreciate your concerns, better to be safe if your not absolutely confident in your understanding of the issues, and I'm certainly no security expert!
June 10, 2015 at 10:29 am
It's a horribly designed database, and it often times has HIPAA/PHI data in it. The thing that bothers me most is the nonchalant way that it was used in the past (they just put the sa account in their app connection string, and didn't even bother to understand their object dependencies). Trying to get them to understand (or even care) about that now is more hassle than their are willing to invest, so they just had me create a new AD account to make into a sysadmin equivalent database user.
It'd be funny if it weren't so effd up.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply