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.
CREATE SCHEMA [Rpt] AUTHORIZATION [dbo] GO
CREATE ROLE [Report_Reader] GO
GRANT SELECT ON SCHEMA::Rpt TO [Report_Reader] GO GRANT EXECUTE ON SCHEMA::Rpt TO [Report_Reader] GO
ALTER ROLE [Report_Reader] ADD MEMBER <<my database user>> GO
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.
Business Intelligence Architect
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
|cookielawinfo-checbox-analytics||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".|
|cookielawinfo-checbox-functional||11 months||The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".|
|cookielawinfo-checbox-others||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.|
|cookielawinfo-checkbox-necessary||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".|
|cookielawinfo-checkbox-performance||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".|
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.