Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
NifflerX
Starting Member
29 Posts |
 Posted - 2012-10-24 : 12:44:30
|
| 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
38200 Posts |
|
|
NifflerX
Starting Member
29 Posts |
Posted - 2012-10-25 : 08:29:00
|
| 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 |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-10-25 : 10:49:59
|
| 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-rooThis 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). |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-10-25 : 11:26:19
|
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, prodwe 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 CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-10-25 : 11:28:11
|
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 CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-10-25 : 14:40:50
|
| Yeah, it seems a lot more people are using it than I would have guessed. |
|
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2012-10-27 : 14:15:10
|
| 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. |
|
|
|
|
|
|
|
|
Checking on best practice roles vs schema