SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Schema Security


Schema Security

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)

Group: Administrators
Points: 689842 Visits: 21594
Comments posted to this topic are about the item Schema Security

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Andy Hooper
Andy Hooper
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 98
I had always stuck to [dbo] until I started architecting our data warehouse/BI strategy, and I am using schema to manage the project boundaries between the various ETL jobs that pull from different parts of the business - putting all the relevant objects in the [mfg], [bud], [inv], [sls], etc. schema (with shared objects/functions in [ref] or [dbo]) is really helping to keep developers from stepping on too many toes, and allows shorter names because the schema helps to define the object.

I'm a convert.
Aleksl-294755
Aleksl-294755
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3209 Visits: 587

Great practical topic. Thanks Steve.
Using schemas is not common in my company, but I personally use "not-dbo-only" and like using schemas for the objects grouping and implement granular security controls.


RonKyle
RonKyle
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30274 Visits: 4657
ahooper - Friday, March 1, 2019 6:13 AM
I had always stuck to [dbo] until I started architecting our data warehouse/BI strategy, and I am using schema to manage the project boundaries between the various ETL jobs that pull from different parts of the business - putting all the relevant objects in the [mfg], [bud], [inv], [sls], etc. schema (with shared objects/functions in [ref] or [dbo]) is really helping to keep developers from stepping on too many toes, and allows shorter names because the schema helps to define the object.

I'm a convert.


This is pretty much my story, too, although I use the schemas to identify the ultimate source of the data. For things that are created in the data warehouse to support the cubes, the schema is datawarehouse. I rarely assign anything to dbo now.



Jason-
Jason-
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2364 Visits: 618
I'm a proponent of schema for both security and organization.
Our design for data access and manipulation is fairly straight forward. the R part of CRUD is done via views and the CUD part of CRUD is done through stored procedures.

Schemas facilitate this nicely, our tables sit in different schemas than our views, procs, functions...
i.e. We create tables in a data schema (typically named [data], [config], [audit] and others relevant to the solution such as [customer], [engineering] etc), then we create views, procs, synonyms etc in "role" schemas such as [app], [service], [bi], [security] etc). We don't assign any permission to the data schemas, rather, using AD Groups, we assign SELECT, EXECUTE and VIEW DEFINITION (as needed) to the appropriate schema(s).

This has been very effective and it's encouraging that you're seeing more usage in the schema direction.


-
Nancy Guntert
Nancy Guntert
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1616 Visits: 417
I am in the early phases of building the data warehouse. I put all my translation tables into the etl schema and kept the facts and dimensions in dbo.
RonKyle
RonKyle
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30274 Visits: 4657
I put all my translation tables into the etl schema and kept the facts and dimensions in dbo.


Consider using a more elaborate schema for the facts and dimensions. One suggestion above was by functional area. I identify via schema the source database for the tables. As I use views for all the dim and fact tables (a suggestion from Kimball's 3rd edition book) the schema for those identifies the supported cube. Works great, although sometimes the DSV is a little fiddly when working with views versus tables.




Tom Uellner
Tom Uellner
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1958 Visits: 2201
We have several internal applications that make use of the same database. We use schemas to separate data and code specific to one application or another into their own schemas. This makes it much easier to organize the associated objects. It didn't take long to get the development team used to the idea.
Nancy Guntert
Nancy Guntert
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1616 Visits: 417
RonKyle - Friday, March 1, 2019 8:18 AM
I put all my translation tables into the etl schema and kept the facts and dimensions in dbo.


Consider using a more elaborate schema for the facts and dimensions. One suggestion above was by functional area. I identify via schema the source database for the tables. As I use views for all the dim and fact tables (a suggestion from Kimball's 3rd edition book) the schema for those identifies the supported cube. Works great, although sometimes the DSV is a little fiddly when working with views versus tables.
Thanks, Ron. Along that line, I was thinking of putting aggregate tables or a presentation layer into another schema. My tables have multiple sources, such as all customers into one customer dimension.

RonKyle
RonKyle
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30274 Visits: 4657
nguntert - Friday, March 1, 2019 8:27 AM
RonKyle - Friday, March 1, 2019 8:18 AM
I put all my translation tables into the etl schema and kept the facts and dimensions in dbo.


Consider using a more elaborate schema for the facts and dimensions. One suggestion above was by functional area. I identify via schema the source database for the tables. As I use views for all the dim and fact tables (a suggestion from Kimball's 3rd edition book) the schema for those identifies the supported cube. Works great, although sometimes the DSV is a little fiddly when working with views versus tables.
Thanks, Ron. Along that line, I was thinking of putting aggregate tables or a presentation layer into another schema. My tables have multiple sources, such as all customers into one customer dimension.


I have some tables with multiple, but there's usually a primary source that provides most of the data. You might consider "common" or some such. Just suggesting minimizing the use of the dbo schema.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search