August 20, 2013 at 8:18 pm
Comments posted to this topic are about the item Always Abstract
August 20, 2013 at 11:00 pm
Not sure this is where I need to enter this comment but here goes. In reading the latest article I began thinking about all of the problems DBA's have with security and I think the problem originates with the basic premise of the DBA. As a DBA we are foremost tasked with creating a logical repository for the data we store. I believe this is the downfall we face. In order to create a truely secure environment we must introduce a bit of abstraction. Years ago performance was a large issue and the number of IO actions determined the success or failure of an application. That is no longer the biggest issue we face. To keep this short I am advocating splitting the data accross multiple database instances with some obscure key defined by the enterprise. The first name of a customer would be stored in a separate database from the last and the key would be used to locate the appropriate instance containing the associated column. This instance could also contain street names so there was no direct way to know the meaning of the data. I am going to name this Obscure Data Collection for the time being. If someone has already proposed this I appologize.
August 21, 2013 at 5:30 am
From an application point of view it doesn't matter whether the underlying object is a table or view.
From a DBA point of view I want the freedom to refactor without having to kick off a big programme of work. I worked on a small refactoring project to move tables from a line-of-business specific database into a central shared database. Due to the sprawling nature of the legacy app the most cost effective way of doing this was to move the table to the shared DB but put a view in place of the original table.
This mean we had an object called tbl_<tablename> that was actually a view.
We also found that for performance purposes one of the views needed to be turned into a physical table. This meant we now had a table called vw_<viewname> that was actually a table!
That is a real concrete example of why I don't like prefixes(or suffixes) to denote object type.
I do use views for security purposes. There are a number of downsides and challenges
Foreign key obscurity makes reverse engineering DB diagrams in Visio a pain.
August 21, 2013 at 6:22 am
Are we not allowing Entity Framework LINQ queries anymore? I know for years that security has always been to remove table-level access and go through stored procedures (which is what I usually do), but Microsoft seems to be steering us towards EF/LINQ?
August 21, 2013 at 6:43 am
In the past when we have tried to use views the performance was horrible so we stopped using them. And we avoided using stored procedures as much as possible. As a developer I find it a lot easier to find the SQL when it is not a stored procedure and it's a lot easier to test and make the needed changes. This was the philosophy of our prior DBA so that is what the rest of us learned. I haven't got a good read on the new DBA's approach to these issues.
Almost everything we do is ran as batch processing at night so this works for us. I know it makes it pretty easy to find and fix the problem in the middle of the night when I have the SSIS process tell me, through an email, what SQL failed then I look at the log file for that SQL in the SSIS process folder I can easily see where and why it failed. I come from a background of woking on a mainframe, COBOL and assembler, and dealing with the storage dumps to try and determine where it failed, so this is like heaven to me. I've heard there is a way to see a 'log' for the execution of a stored procedure, but if it's like looking at the log for an SSIS process then no thanks.
I've got almost zero knowledge of stored procedures, so feel free to enlighten me with your knowledge. 🙂
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 21, 2013 at 7:11 am
As a VB Programmer, I developed a lot of processes using disconnected recordsets. I used stored procedures to retrieve the initial sets. However, the additions and changes made by the recordset object required direct access to the tables. For that reason alone, I couldn't have used views. I've seen this technique recommended before, but personally have found very little use for views in an OLTP environment. I've used them to some extent in an OLAP environment to allow me to pull a subset or higher level from a dimension table (e.g. create a quarter hour view from a dimension table that has all the minutes, a month view from a date dimension that has days or a sub list of inspectors from technicians). In one of the Kimball books I read recently views were recommended for all the tables so that the auditing information that was of no use to the end users could be shielded. This is also intriguing, but you can't set the referetial integrity among the views in the same manner as tables.
This mean we had an object called tbl_<tablename> that was actually a view.
We also found that for performance purposes one of the views needed to be turned into a physical table. This meant we now had a table called vw_<viewname> that was actually a table!
That is a real concrete example of why I don't like prefixes(or suffixes) to denote object type.
Having prefixed my objects for 15 years now, I find the advantages outweigh these small disadvatanges. Occasionally the types change, but that is really very uncommon and not a reason to completely disregard a naming convention system.
August 21, 2013 at 7:16 am
steve.fortner 92896 (8/21/2013)
Are we not allowing Entity Framework LINQ queries anymore? I know for years that security has always been to remove table-level access and go through stored procedures (which is what I usually do), but Microsoft seems to be steering us towards EF/LINQ?
You can still use stored procs with EF/LINQ. Here's a link for the LINQ http://msdn.microsoft.com/en-us/data/gg699321.aspx
Tom
August 21, 2013 at 8:19 am
I worked on a DW for a major financial institution that had, as a basic requirement, a view for each table. This was used to apply standard filters and joins to some dimension tables. Sometimes is was great. Sometimes is caused serious performance problems, especially if too many dimensions where involved.
I have used the view approach for .NET development effectively, but I created a straight view for each table. On occasion, I had to point the view to a different data source and was able to do so without code changes.
This isn't always allowed in today's "change management" environment.
I still find that "one size" does not fit all. 😉
August 21, 2013 at 8:23 am
RonKyle (8/21/2013)
As a VB Programmer, I developed a lot of processes using disconnected recordsets. I used stored procedures to retrieve the initial sets. However, the additions and changes made by the recordset object required direct access to the tables. For that reason alone, I couldn't have used views. I've seen this technique recommended before, but personally have found very little use for views in an OLTP environment. I've used them to some extent in an OLAP environment to allow me to pull a subset or higher level from a dimension table (e.g. create a quarter hour view from a dimension table that has all the minutes, a month view from a date dimension that has days or a sub list of inspectors from technicians). In one of the Kimball books I read recently views were recommended for all the tables so that the auditing information that was of no use to the end users could be shielded. This is also intriguing, but you can't set the referetial integrity among the views in the same manner as tables.This mean we had an object called tbl_<tablename> that was actually a view.
We also found that for performance purposes one of the views needed to be turned into a physical table. This meant we now had a table called vw_<viewname> that was actually a table!
That is a real concrete example of why I don't like prefixes(or suffixes) to denote object type.
Having prefixed my objects for 15 years now, I find the advantages outweigh these small disadvatanges. Occasionally the types change, but that is really very uncommon and not a reason to completely disregard a naming convention system.
As a half-Hungarian I always like to add to any discussion on Hungarian notation (it was originally developed by Charles Simonyi). It was ideal in the world of C where there were no types in the headers but in modern statically typed languages it is unnecessary and messy in my opinion: we know or can find out what type it is either by looking at the definition or through something like Intellisense.
So for the purpose of balance of this debate: I stopped prefixing my objects 18 years ago.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 21, 2013 at 8:26 am
I like stored procedures as they both restrict the ways the database is accessed and are completely testable units themselves. Working against views makes sense to me but possibly unnecessary if access is only allowed via stored procedures. Either way (SPs or views) they provide a logical view over and above tables.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 21, 2013 at 8:36 am
I've always liked an "interface" approach, using stored procedures.
If a developer needs to access customer information, you give them a "GetCustomer" stored procedure.
This insulates them from the schema and schema changes... you can completely re-architect the underlying tables as long as their interface works the same. This also gives you excellent control over security, as you can have separate interfaces like "GetCustomer" and "GetCustomerPlusConfidentialData", each with its own permissions.
Unfortunately, this takes more work up front, and it tends to get messy over time when you end with 10 different "GetCustomer" flavors (of which you probably only need 2-3).
In my experience the view approach usually leads to performance problems. It's OK when the view is SELECT a,b,c FROM [sometable], but as soon as the views get more complicated, queries joining multiple views together get hard for the query optimizer to work with.
August 21, 2013 at 8:39 am
We have an 3rd party HR application that takes exactly this approach. Mainly to implement row based security in the app by joining in a table that contains the employee id's that staff are permitted to see. The views get used for basic CRUD operations in the app screens and also in reports. Stored procs are used when a process requires more than a single query to accomplish the task. In this way most of the business logic has been moved into the database.
August 21, 2013 at 9:31 am
The author wrote:
... I sometimes think that an investment in developer education would quickly erase any productivity losses ...
Education is always a good thing, but it applies all around, too. In my experience, I've seen numerous DBAs who needed to learn a few things about the real world challenges faced by developers.
DBAs are not the font of all wisdom. Perhaps we'd like to think so :-D, but the reality is somewhat different.
August 21, 2013 at 9:37 am
Craig-315134 (8/21/2013)
The author wrote:... I sometimes think that an investment in developer education would quickly erase any productivity losses ...
Education is always a good thing, but it applies all around, too. In my experience, I've seen numerous DBAs who needed to learn a few things about the real world challenges faced by developers.
DBAs are not the font of all wisdom. Perhaps we'd like to think so :-D, but the reality is somewhat different.
As a developer I have to agree 100%. 🙂
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 21, 2013 at 9:46 am
I like the "do everything through stored procedures" approach, and have been practising and advocating it for more than a decade. I'm surprised it isn't used more often, since it is quite certainly the surest way to get a really secure system.
The statement "I rarely see this in place" in your article surprised me too. I knew quite a few systems that had it in place in the early 2000s. I worked knew two systems that not only had the all stored procedure approach but also had quite a lot of their C++ and/or JScript code driven from SQL (some of it as extended SPs, some through activating jobs with cmd and/or ActiveX jobsteps, some using xp_comandshell).
Tom
Viewing 15 posts - 1 through 15 (of 90 total)
You must be logged in to reply to this topic. Login to reply