SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Checking on best practice roles vs schema
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NifflerX
Starting Member

29 Posts

Posted - 10/24/2012 :  12:44:30  Show Profile  Reply with Quote
Hello,

I've read a bunch about schemas and roles online but still don't quite get why both exist. From what I understand both can house database objects (tables, stored procedure, views, users, etc). Generally when I create a user I add them to the dbo schema, which has no explicit permissions, and then to give them access to objects I add the user to the proper role (dbreader, dbwriter, etc). I believe I could do the same thing with schemas by adding the correct permissions to the dbreader schema or dbwriter schema and add the user to that schema instead of a role.

I've seen examples where the use of schemas has been used to group objects for viewing in Management Studio, and I can understand that it's sometimes nice to view all HR.employees and HR.groups together, and separate from IT.computers or IT.printers. But that seems asthetic as the security separation could be easiliy done with an HR role and an IT role, and have all the objects created under the dbo schema. (dbo.employees, dbo.groups, dbo.computers, dbo.printers)

So in the end, my question is, why have both Roles and Schemas? And since both exist, is there a best practice for doling out user security by using them? Thanks so much.

-NifflerX

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 10/24/2012 :  13:21:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why? Adding the schema functionally was really to compete with Oracle. The SQL Server world isn't really adopting schemas. Some may use them, but they are in the minority.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

NifflerX
Starting Member

29 Posts

Posted - 10/25/2012 :  08:29:00  Show Profile  Reply with Quote
Thank you so much. If they were added to increase adoptability or to compete with Oracle instead of actually adding functionality that makes a lot more sense to me. Thank you so much.

-NifflerX
Go to Top of Page

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 10/25/2012 :  10:49:59  Show Profile  Visit robvolk's Homepage  Reply with Quote
I never heard schemas were added to compete with Oracle (not doubting it, just never heard it). Schemas are part of the SQL standard, if anything they make SQL Server more compliant.

Schemas have a number of interesting uses. Here's one example we use: http://www.sqlperformance.com/2012/08/t-sql-queries/t-sql-tuesday-schema-switch-a-roo

This is useful for situations that would normally use partition switching, but can work in any edition, not just Enterprise.

ALTER SCHEMA TRANSFER can also help with code deployment; you can create the procedures under a separate schema, run tests or do other things, and then swap out the new code for the old without dropping or altering. If something goes wrong, another ALTER SCHEMA TRANSFER does a rollback. This works for table data too, we use it all the time and it's damn handy.

Roles do not "house" objects, they only have permissions on objects. You could say that roles "house" users or other roles, but the proper terminology is that they are members of that role, and inherit permissions assigned to the role.

Schemas are containers for object ownership or classification, independent of the user who created the object. A role can be granted permissions to a schema, and those permissions would be inherited for objects in that schema. This can greatly simplify permission management, as any new objects would not need explicit permissions granted.

One problem with using dbo for everything is that dbo has database ownership, so tables can be dropped, altered, etc. Keeping objects in other schemas prevents this from "accidentally" occurring (as long as that schema doesn't belong to db_owner).
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 10/25/2012 :  11:26:19  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
We use both schemas and roles and it simplifies things enormously.

For example:
we'll split functional groups of tables and programmability objects into their own schema. Say a 'Climate' Schema or a 'Routes' Schema.

this makes it easier to find stuff - also they tend to form the basis for replication publications.

We use roles to make our deployments easier from environment to environment.

When allocation permissions we always allocate the permissions to a logical role.

Example -- we have a role called rlRoutesReader (which has select and execute permissions on routes objects)

this role definition is the same in each of our environments - dev, test, int, prod

we allocate specific environment specific *users* (tied to domain accounts) for each specific environment. Then associate those users with the relevant roles to handle access permissions.

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 10/25/2012 :  11:28:11  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
quote:
Originally posted by robvolk
...
Schemas have a number of interesting uses. Here's one example we use: http://www.sqlperformance.com/2012/08/t-sql-queries/t-sql-tuesday-schema-switch-a-roo
...


Interesting. We are using exactly the same trick to handle large snapshot replication articles with zero downtime. Replicate the snapshot to an offline schema -- construct all the indexes applicable and then finally start a transaction and switch out the tables.


Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 10/25/2012 :  14:40:50  Show Profile  Visit robvolk's Homepage  Reply with Quote
Yeah, it seems a lot more people are using it than I would have guessed.
Go to Top of Page

namman
Constraint Violating Yak Guru

USA
272 Posts

Posted - 10/27/2012 :  14:15:10  Show Profile  Reply with Quote
I somehow disagree with tkizer.

For small/simple DBs, using Roles and Schema is not necessary, but for big/complicated DBs, using Roles and Schema is very important, even though without those, DBs are still working.

I agree with tkizer that people using Schema are minority. But it does not mean that feature is not important. The reason is that most DBs out there are designed by web developers not DBAs nor DB developers. And their main concern is "working" not "working well". Moreover, at beginning of a project, they have other things to worry about.

robvolk and Charlie already mentioned some good things for using Roles and Schema, but I think there are much more.



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000