Schema vs. Role Security

[Geek-Master]

In this example I'm using the AdventureWorks database in MS SQL Server 2005

I was wondering what other people thought about Schemas vs. Roles when it comes to security access. If you have an employee who just started working for Human Resources and needed access to all the database objects in HR, do you add the new hire to an existing role or do you assign that person permissions to the HumanResources schema? Either way the user can be configured to work in HR by using a Schema or Role. What are the pros and cons to either one and what are the reasons to assign a user to a Schema instead of adding them to a Role?

What I figure there are always going to be exceptions that just don't fit into what may already be available that you could use a mix of both.

Thanks

[dickersonka]

They can be used for both I suppose, but here is the way I normally think about them.

Think of schema's as groupings of tables. This way, you can always see the tables grouped together by schema that are related. Also you will probably need a common schema for ones that are shared.

I would use roles for the security portion. The roles can then have privileges to multiple schemas, and you can still see the tables regardless of the roles.

In short, schema is good for organization, roles are good for security.

[Geek-Master]

But 2005 allows you to grant users the right to run SQL statements to individual schemas. I do understand that schemas are collections of database objects, but it seems SQL 2005 is mixing the namespace and security aspects of it.

[dickersonka]

it does mix them, it is your job to separate them

users are security based (normally) meaning roles

schemas are grouping based (meaning organization)

[dickersonka]

although you are not too close, good to see another ga user on daniweb :-)

[Geek-Master]

It is rare that you would want to give someone complete access to all the objects pertaining to a schema. An example would be the HumanResources schema which has sensitive material that not all employees of HR should see. Some people in HR shouldn't even see Social Security numbers or Notes about a person. That information should be given to only select few people in HR. So giving people access to the entire schema probably isn't the best idea. I wonder why Microsoft has allowed that to be an option. At least with role based security you can create a view or stored procedure that prevents that sensitive data from being exposed to all employees and just give access to that in the role. Well any way I am glad that the user has been separated from the schema and you only have to assign an owner. Changning the namespace after the code has been built can be a pain.