To Schema or not to Schema

As databases are becoming more portable, and security becoming ever critical, there is a basic feature in relational databases that can be used to make life easier. The old reliable Schema definition can be a godsend. What do you say? We put everything under dbo, and life is good. If you do not do much with your database, that is true. If you have a large number of requirements, large number of objects, high level of access, and as you scale, it can seem you have an episode of hoarders going on. Well, why do I just want to put a prefix on my tables? Now, I have a well-named mess of objects. Professional version of A.D.D., or a new development team doing work down the road, and it most likely not be adhered to consistently for very long. People will give new names that make sense to them at the moment, and there goes your standards. Gee, that never happens. To help support manageability and security, the use of schemas can provide to you the ability to logically segment your database and allow you to manage your environment more effectively. In the past, using different schemas has been cumbersome, but it has gotten better. Let’s take a look.**

** SQL Server is being used as the example DB platform for the article. 

Manageability

Better to segment your database

• If I have different tables that are used for different purposes, such as processing phases and data access/data export, for instance, I can create separate schemas to manage. If I have schemas to support pre-stage, stage, lookups, the production area, an extract layer, and report access, the objects and code aligned to their corresponding schema can be easier to follow and manage.

You only need one database.

• If I had a case where I needed two databases, a stage and production database, it can allow you to logically segment the database without creating multiple databases. A common scenario for a data warehouse environment. Ever try to uplift multiple, dependent databases to the cloud? Not fun and will often times force you into a more costly configuration (I.e. VM cloud instance). Better if you only have to worry about uplifting one database, instead of multiple. The elastic nature of the cloud allows you to grow your resources when needed, when in the past you had go through the capacity planning and hardware provisioning to be able to optimize the performance your environment, especially I/O, when it was on-premise.
• Let’s say I only wanted to spin up a SQL Azure DB instead of a managed instance, or a full VM with SQL Server on it. Or better yet, you only have the resources to pay for a lesser option. One self-contained database is perfectly configured to uplift from on-premise to the cloud or be able to develop directly on.

Security

Isolate your data/limit exposure

• The concept is a layer of abstraction. Only allow access to those objects, or access layer you wish to expose. What better way than to create a separate schema and only give user accounts and groups permissions to it? Keep the underlying tables and objects un-accessible. Access will be off by default. Dbo and other schemas will be hidden and unavailable for user access.

More granular permissions.

• You do not need to provide a higher level of access, such as fixed database roles; db_owner, db_datareader, or db_datawriter. What I want to do is to set up user-defined database roles, using the schema you want to expose, and assign permissions accordingly.
  • You should create a security model and define what user access and privilege levels are required up front, or at least do some basic anticipation of what access you would allow.
  • Avoid global permissions, unless the privilege level warrants it. Set up user-defined database roles, assign permissions to the role, and then assign database users to those roles. Also, use Active Directory groups as logins and database users, avoid individual accounts.
  • Specific granular privileges. An example is to GRANT EXECUTE to a subset of stored procedures, and not all the stored procs under dbo. I do not like nested SQL coming in from the reporting tools, so I like to use reporting stored procedures. Granting access without different schemas is the equivalent of herding cats. Best of luck managing the permissions months down the road.
• Less chance of error in defining permissions. Schemas are far easier to follow and are a DBAs friend. Permissions can get buried very quickly and difficult to maintain, if not clearly delineated. Keep it simple. God be with you if ever needed to change user permissions in the past, and they were comingled. Better option is to avoid headaches by design.
• As an example, let us set up a reporting schema for access. Here are the goals.
  • Let us create a rpt schema for report access (define the owner of the rpt schema as dbo. Newer versions SQL Server default to dbo as the owner).
  • Create a user-defined database role.
  • Assign select and execute permission to the role for that schema. Notice that I did not mention the dbo schema, which I recommend having your production tables defined under. I have not given access to the default dbo schema, and a db user will not be able to access the dbo directly but will be able to access through the reporting schema via views or stored procedures.
  • Assign users the database role.
• Let me provide some SQL Script that you can use.
  • Create schema.

CREATE SCHEMA [Rpt] AUTHORIZATION [dbo]
GO

• • • Newer version of SQL Server (2016 and higher) has the owner as dbo by default and you do not need the AUTHORIZATION clause. NOTE: for ownership chain, dbo needs to be the owner so you do not have to GRANT permissions directly to dbo for the schema usage to work properly.
  • Create database role

CREATE ROLE [Report_Reader]
GO

• • Grant permissions to the role (not to the user). Both select and execute permissions to objects defined under that schema.

GRANT SELECT ON SCHEMA::Rpt TO [Report_Reader]
GO

GRANT EXECUTE ON SCHEMA::Rpt TO [Report_Reader]
GO

• • Assign users to the role

ALTER ROLE [Report_Reader]  ADD MEMBER  <<my database user>>
GO

• • CREATE your views, stored procs (with the Rpt schema) that reference the dbo tables/views you want.
    • Example:

CREATE VIEW Rpt.Fund AS
       SELECT * FROM dbo.Fund
GO

In general, make your life easier whenever possible. We don’t have time to waste on maintenance or troubleshooting an environment that is poorly designed, not designed to grow in functionality/scale, or allow us to move to the cloud. Without a doubt, the cloud environments will force the issue if you do not exercise some due diligence in the design phase. How are your databases setup?

Lastly, if your organization is still having trouble setting up its databases, please take a moment to check out our Customer Experience Analytics Kickstart. Together with our strategists, we’ll ensure your organization is armed with the data you need to deliver a great customer experience across all channels in just two weeks.