Asger Blond
ab-mi at post3.tele.dk
Fri Jun 21 14:45:46 CDT 2013
Hi Mark, Thanks for our reply. My reasons for exposing data to applications only through abstraction layer objects (like views, procedures, functions, triggers) and to avoid exposing tables are well covered by this video: http://www.youtube.com/watch?v=OQEwua8u2mc The video focuses on two main reasons for using abstraction layer objects: security and shielding of applications from design changes to tables. One more reason I would like to mention is performance. Abstraction layer objects make more efficient use of query plan caching than adhoc queries against tables do. SQL Server caches plans for all kinds of queries, even for the most trivial adhoc query. But these kinds of plans will only be reused if the wording of a new query *exactly* matches the wording of the cached query: tiny changes like a different case for identifiers or keywords will result in a new plan - i.e. "select * from dbo.Customers" is not equal to "Select * from dbo.Customers" or "select * from dbo.customers", and will disable plan reuse. In contrast an abstraction layer object like a view, procedure, or function will maintain wording and therefore more likely ensure plan reuse. This being so there seems to be a need to separate schemas for tables from schemas for abstraction layer objects - especially if you want to grant permissions on the schema level, which for me is quite convenient. / Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Mark Breen Sendt: 21. juni 2013 10:21 Til: Discussion concerning MS SQL Server Emne: Re: [dba-SQLServer] Schema names and security boundaries - secondtry... Hello Asger, enjoyable email, thank you for posting in such detail. I have little to add, other than to tell of my experience. 1) in the last 15 years, I have never broken down SQL server security on an object basis. Perhaps most of my app are smaller and the average user is entitled to access to most of the objects. I can imagine in a large system with hundreds of tables and tens of groups of functionality that such granular permissions are required. 2) in recent years, with a web front end, I have a few times recalled the object level permissions we read about in the past and wondered is that all gone away. Most web apps I have been have one connection string. 3) I know I should split permissions so that users cannot access the raw tables, but honestly, I rarely do. Finally, in one app I developed a few years ago, I used a different scheme than dbo. It proved for me to be a PITA, especially when I sometimes see the tables in SSMS and sometimes though RedGate. Nowadays, I only use one or two characters to cause my tables to sort based in name, eg, dbo.AD_Customers, dbo.AD_Orders. My main curiosity is should we all be apply these high levels of quality that you are writing about? It adds some complexity and overhead to a project. It is the way we should be working in the smaller apps we develop? And finally, when we develop on web app, with many roles, is there any practical to avail of SQL Server object based security? On 20 June 2013 23:58, Asger Blond <ab-mi at post3.tele.dk> wrote: > Hi all, > > Some weeks ago I posted a question about your usage of schema names but > didn't get any response. > I'll try once more: > > ----- > > How do you use schema names in SQL Server if you want to use them as > meaningful namespaces, and also intent to use them as security boundaries > but don't want to grant permissions on the base tables to any other than > admins? > > > Some background: > As you probably know Microsoft made an important change in SQL Server 2005 > separating schema names and owner names. > Prior to 2005 the schema name of an object always identified the owner of > the object. For example an object named JohnDoe.Customer would imply that > the owner of the object was a user named JohnDoe. You couldn't have an > object with a more meaningful name prefix such as Sales.Customer unless you > had a user named Sales, who would then be the owner of the object. > Quite an awkward situation which for ages led to schema names not being > used as intentional by the SQL standard: an identifier of meaningful > groupings, i.e. of "namespaces". The net effect of SQL Server not > conforming to the SQL standard was that objects most often wasn't > identified by a meaningful namespace names but was just prefixed by dbo, > implying that the object was owned by the standard database owner user dbo. > IMO it's good practice to have all objects owned by the build-in standard > database owner user named dbo. But IMO it's really bad practice to junk all > objects into the same namespace. > Fortunately Microsoft changed its practice in 2005 and brought the usage > of object prefixes in accordance with standard SQL. From 2005 and onwards > the prefix doesn't imply any ownership. You can now have an object named > JohnDoe.Customer which is actually owned by DonaldJones (but normaly I > would prefer to have it owned by the standard user dbo). And you can have > an object named Sales.Customer which is actually not owned by a user named > Sales but by a user named DonaldJones (but again normally I would prefer to > have it owned by the standard user dbo). > So far so good. But there's more to it: Security. > Schema names can be used as security boundaries: If you grant a permission > on a schema to a user or role you are granting this permission on each and > every objects in the schema. Quite effective... > But a NO GO if you are member of the school saying "Don't ever grant > missions to tables, only grant permissions on programming objects accessing > the tables (such as views, stored procedures, and functions)". I belong to > this school. > So - to repeat my question: How do you use schema names in SQL Server if > you want to use them as meaningful namespaces, and also intent to use them > as security boundaries but don't want to grant permissions on the base > tables to any other than admins? > > My practice is: Create separate schemas for tables and user-accessible > objects within each meaningful grouping of the objects. For example: > Table Customer: > > Sales_Table.Customer (for the table and only accessible for admins) > > Sales.CustomerView (for a user accessible view on the Sales_Table.Customer > table) > > Sales.CustomerProcedure (for a user accessible procedure on the > Sales_Table.Customer table) > > Sales.CustomerFunction (for a user accessible function on the > Sales_Table.Customer table) > > Note this is not about naming conventions - whether to use prefixes such > as vw for views, usp for procedures, and udf for functions. That's a > different discussion. Here I'm only applying the names CustomerView etc. as > placeholders for user accessible objects. > My question is about separating user accessible object from tables using > schema names. > > Curious to hear your thoughts on best practices. > > / Asger > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com