DB Standards Reality Check
-
I would suggest that as you write the standards, you develop it as an HTML or indexed document.
In the document, you justify and give a basic guide on how to accomplish it. Such as
Security:
1. Do not use db_datareader and db_datewriter roles
A. Link to reasons why - or explanation
B. General method around it.
If you come up with standards that look like you did it just for the H@!! of it, they'll be blown off in a moment. The other one is to develop a framework database to copy out of. A consilidated database of standard info. Such as a table of state listings and abbrev, a table of branches/locations and dept id's, your employee listing and phone numbers that is generally open to the public/developers to read from instead of developing their own. Include a library of stored procedures, DTS packages etc.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Good approach Gregory.
Make a small set of high level Database Administration Standards.
Covering the setup of Sql Servers and Databases, including the following:
Security
Backup
Size, growth and placement of Data & Logfiles (not autogrow 10% until disk crammed)
Collations
Database Options
...
Naming standards for (new) databases, jobs
...
Write a script that checks a server / database if it conforms to these high level standards.
As for naming standards for individual databases. A "quick win" could be the following standard:
Each database/application must have a naming standard document approved by the DBA. ( Written by the DBA/Developers designing the database )
Before production the database/application will be screened against this document to see if it conforms to the standard.
Of course, You have a ready document for use, in case the people designing the database/application don't want to write one of their own.
/rockmoose
You must unlearn what You have learnt -
Sounds like your environment is where DBAs are also sql programmers and will work with these standards directly. I suggest you have a meeting with the most senior DBA "SDBA" (experienced and respected by peers) and ask SDBA to take ownership of this task. If you're afraid the SDBA will come back with something you don't like, it could be you are taking too much control of this simple task and are going to have a fight on your hands. I have several years experience in development and would cringe if some "architect", or "manager", or "data analyst" or "project lead" dictated to me what the standard were going to be for naming the columns of table to the procedures, views, triggers, etc that I am programming.
If you got 2 different groups that could be bent out of shape by all this
, 1) let the DBAs control the table and column names..
2) let the application programmers (sql programmers) use Views that reference the original tables.
Getting everyone to agree will go much further and last longer than coming up with even the best solution that not everyone will agree about.
my nickles worth!
-
hi all, can someone inform me of all the drawbacks of using db_datareader, db_datawriter, etc.
thanks,
Alex
-
Here is my take.
These roles allow the users in these roles to automatically get access to new objects in the database. This is a drawback if your security model dictates that users need to specifically request, get approval prior to being given access to any object in the database. Since this is our security model, we don't use these roles.
Suppose someone creates a new highly confidential table in your database, where only a very limited set of individual should have access to view the data in this new highly confidential table. If you use the db_datareader, and db_datawriter roles all individuals in the role automatically get access to this the super-secret data in this new table.
If you are using the security model I described above, then these roles would violate that security model evertime a new object in the database was created. Since we use the security model described above, this is why we don't allow the use of these roles.
One last parting comment. Using these roles are just a lazy way to implement database security, with is only one step up from having no security.
Gregory A. Larsen, MVP
-
Cheers for that Greg.
What if ourdata analyst, administrator or even CEO were to want read access to data, and hence every object.
Is the db_datareader still a bad option?
-- alex
-
In our environment, just because you are the CEO, doesn't mean you get access to everything that already exist, and anything new that comes down the road. Basically the CEO would still need to request access to data, and the data owner would have to authorize that access. Therefore in this kind of security model giving access to db_datareader, would allow the CEO access to everything new that comes along, without asking the data owner. This is why using this role in our environment violates our security policy which state: All access to data will be approved by the data owner before an individual is granted access to the data.
Gregory A. Larsen, MVP
-
I think it doesn't really mather who makes the standard at al. The only true important point is:
Is everybody happy with the standard and is willing to use it...
Nothing else mathers... If you are questioning yourself who must make the standard... Aren't you questioning:
Who is more important about this point?
And this is never a good question.
-
This is without a question a joint venture of DA and DBA.
But if you forget to have your standards supported by you top-level ceo your project is predestined to be doomed.
Keep in mind that any standards implementation has a rather long leaning curve, which has to be taken into account. Projects will suffer so the people involved learn how and when to use which rules.
Once this completed, this naming-conventions-project is not finished, but has to be reviewed every now and then
These procedures also should be described now, so there will be budget potential available for reviews.
Also describe implementation steps and guidelines for your set of rules.
IMO the best way for now is to have a development project started up with your top-level ceo ‘s approval. Prepare it well, so you won’t end up with frequent standard changes en the first year of implementation.
Define scopes very well !
Have your top-level ceo with you on stage when your project is presented for implementation to your world, so your world knows this burden is supported by the company’s top level !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply