Would we use Schemas as a Security Container?

Question

Would we use Schemas as a Security Container?

Banana-823045

SSCommitted

Points: 1689
More actions
September 11, 2011 at 8:43 am

#244630

I'm trying to find the best practices on using schemas for securing a large class of objects. Someone else asked same question but got no answer. The MSDN article, SQL Server Best Practices – Implementation of Database Object Schemas doesn't specifically answer my question. The article talks about using schemas to separate objects between different groups of users but doesn't say whether one would want to use schema to grant a broad set of permissions for groups.
Have you or would you do something like this:
CREATE SCHEMA ReadOnly;
GRANT SELECT, EXECUTE ON SCHEMA::ReadOnly TO [ReadOnlyUsers], [Users];
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::ReadOnly TO [Admins];
CREATE SCHEMA Userland;
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::UserLand TO [Users], [Admins];
CREATE SCHEMA Administration;
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::Administration TO [Admins];
The best practices articles talks about schemas being used for different group of users but the above is not so much about keeping objects partitioned between groups as it is about using schemas to define a broad permission policy for more than one groups so we don't have to specify permissions on each individual tables; just drop them in the right schema, boom, the permission is taken care of.
The fact that I'm having hard time finding a definite answer makes me worry that the above is actually a bad idea, and I'd like to find out. This thread @ StackOverFlow is probably the closest.
Your inputs are always appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 1

I'm definitely the wrong one to ask on this. My belief is that users should only have 2 privs on a production box... PUBLIC and the EXECUTE priv on certain stored procedures.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Banana-823045 SSCommitted Points: 1689 More actions · Answer 2

Thanks, Jeff. Your reply makes me think I should add a bit of background:

The "users" are actually applications that need to have some ad-hoc querying capabilities for better or worse. Hopefully my examples make clear that I have no intention of using any built-in schemas and therefore inadvertently exposing any system objects. The "Administration" schema would actually have nothing to do with SQL Server administration but rather store data pertaining to application administration. So, all data in those schemata would be understood to be users' and the responsibility of managing data would fall on the users.

Whether this extra bit of info will make difference, I don't know. I"m really interested in knowing what others would have done and whether I've missed any potential problems with this approach.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

Jeff Moden (9/11/2011)
I'm definitely the wrong one to ask on this. My belief is that users should only have 2 privs on a production box... PUBLIC and the EXECUTE priv on certain stored procedures.

As a clarification, I tend to agree with Jeff, but I would say that users should have public and a user defined role. That role should have execute on stored procedures.

I do know that's not always practical or possible, in which case I would still move with a role having the insert/select/update/delete permissions on objects. I have seen people use schemas for this, and I think it works since schemas are a type of container, but it only works if you can accept those permissions for users. As soon as you have a second set of permissions needed, then you have to add another role, and then you've mixed security.

In my mind, schemas can make some sense for object grouping. So maybe you have an Orders table in the ETL schema and an Orders table in the Apps schema, both with the same structure, but they're used for different purposes, perhaps the ETL.Orders is for staging. However I would set permissions separately. I don't think it adds (much) more effort, it's a one time setting, and it keeps all permissions in roles, not mixed in schemas and roles.

Banana-823045 SSCommitted Points: 1689 More actions · Answer 4

Steve Jones - SSC Editor (9/12/2011)
I do know that's not always practical or possible, in which case I would still move with a role having the insert/select/update/delete permissions on objects. I have seen people use schemas for this, and I think it works since schemas are a type of container, but it only works if you can accept those permissions for users. As soon as you have a second set of permissions needed, then you have to add another role, and then you've mixed security.

Thank you for your feedback. I did think about the cases where an object has more complex permission than what can be represented via a broad permission policy set by respective schema. From what I've read, views are generally to be preferred over column-level permissions and if I had such object, it seems to me that I could throw the object in say, dbo schema and give users access to a vertically filtered view created in their schema. It may work for horizontal filtering, too.

But what I'm taking away from this is that if I'm finding myself needing to create a new schema to represent a similar but different in one little aspect permission policy, then it's likely I'm using wrong solution. Is that what you're thinking?

In my mind, schemas can make some sense for object grouping. So maybe you have an Orders table in the ETL schema and an Orders table in the Apps schema, both with the same structure, but they're used for different purposes, perhaps the ETL.Orders is for staging. However I would set permissions separately. I don't think it adds (much) more effort, it's a one time setting, and it keeps all permissions in roles, not mixed in schemas and roles.

Good point about not wanting to mix permissions between schemas and roles. I can see how it'd end up counter-productive if we were setting permissions to both objects and schemas to roles. I was initially not touching any objects directly but dropping them in the appropriate schema with the suitable permission for a given role. I also thought that using dbo schema as a container for objects that has more complex permission would at least give visual feedback that those objects are not quite like other objects in their schema, in terms of permissions.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 5

It's not so much needing a new schema, but more the complexity from two security implementations.

I think we're on the same page, but let me put an example out there.

I have 3 tables, Sales, Products, and Customers. Regardless of the columns I want someone to see (which is best handled in views or stored procedures), I have 2 users that need permissions: Bill and Joe.

I assign Bill permissions to the App schema, which contains these objects, and set those permissions. Now I want Joe to see Products and Customers for data cleanup, but not Sales. I can't move Sales to another schema. So what I end up doing is either granting Joe rights to those tables (bad) or creating a role for permissions to those tables (good).

However if I quit and the next DBA/developer comes, they now have to determine where permissions exist. If Sally starts in either Bill or Joe's job, then where do her permissions go? It's possible that someone might not like/get the schema permissioning and assign Sally to a new role and create permissions there. That's not necessarily bad from a security standpoint, but it does create complexity, and could leave room for holes if I had 200 tables instead of 3. And potentially 3 or 4 different "jobs" that need different permissions.

Suppose I add a new table, Employees. Now I need some of my people to read this table. If it's in the Apps schema, which makes sense, I am automatically granting rights. I could use a role and DENY to separate out my users, but DENY always strikes me as a last resort, and a place where I've fundamentally designed security poorly. I could make a second schema, but I'm sure in any decent sized company, I'll have exceptions. I could not grant permissions for the schema and use a role, but I'm creating complexity and confusion again, IMHO.

I stick with roles, not because it's better, but because it's simpler. The security issues aren't often with the different objects, but because you have different rules for different users.

Banana-823045 SSCommitted Points: 1689 More actions · Answer 6

Thank you very much Steve. That helps me see more clearly why the idea of schema permissioning isn't so common.

If I may, a side question - if we're going to use the model of assigning permissions on objects (ideally just routines but could be views/tables with security caveats not withstanding) directly to the roles, how do you track the logical security grouping of objects? We both agree that there's always exceptions to the rule but in my experience so far, good numbers of objects happen to have same permission policy. In a single-schema where all permissions are at object level, there's no way to discern which objects fall in what permission policy. It seems that multiple schemas is a self-documenting way to show how security is structured for groups of objects whereas single-schema would mean I'd have to manually document the logical group outside the SQL Server and cross my finger that my descendant will have the wisdom and foresight to look in the cabinet drawer for that documentation. But maybe I just don't know about a better way of documenting it?

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 7

In terms of documentation, I think that roles provide some level of self documentation. You name them something related, then group objects/users and match permissions with them. Typically you have 2-3 of them. If a new DBA doesn't see any permissions for users, I'd hope they would check for roles. If not, I'm not sure what else you can do.

Whether you use roles or schemas, you'd have to leave a document outlining things somewhere. Even with schemas, you have limited abilities since an object can't exist in two schemas.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

This article from MVP Dan Guzman may be helpful. I refer people to it often for questions like this. It is a generic approach to using schemas for object organization and provides the ancillary benefit if being able to grant permissions at the schema level while not destroying your ability to leverage ownership chaining:

http://weblogs.sqlteam.com/dang/archive/2008/02/03/Keep-Schema-and-Ownership-Simple.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato