Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Always Abstract Expand / Collapse
Author
Message
Posted Tuesday, August 20, 2013 8:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 32,781, Visits: 14,942
Comments posted to this topic are about the item Always Abstract






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1486490
Posted Tuesday, August 20, 2013 11:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 7:19 PM
Points: 3, Visits: 24
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.
Post #1486513
Posted Wednesday, August 21, 2013 5:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 2,866, Visits: 1,708
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

  • Restrictions around indexing

  • Problems documenting views in our chosen auto-documentation tool. Specifically the tool required special tags in the header rather than sp_addextended property. Also columns did not have their extended properties resolved back to the source table.


  • [li]Foreign key obscurity makes reverse engineering DB diagrams in Visio a pain.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #1486639
Posted Wednesday, August 21, 2013 6:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 21, 2014 7:03 AM
Points: 25, Visits: 79
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?
Post #1486655
Posted Wednesday, August 21, 2013 6:43 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:47 AM
Points: 214, Visits: 722
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.
Post #1486668
Posted Wednesday, August 21, 2013 7:11 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:28 AM
Points: 788, Visits: 1,915
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.



Post #1486684
Posted Wednesday, August 21, 2013 7:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:11 PM
Points: 2,411, Visits: 3,440
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
Post #1486688
Posted Wednesday, August 21, 2013 8:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 07, 2014 12:44 PM
Points: 3, Visits: 65
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. ;)
Post #1486745
Posted Wednesday, August 21, 2013 8:23 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:20 AM
Points: 4,862, Visits: 2,243
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!!!
Post #1486750
Posted Wednesday, August 21, 2013 8:26 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:20 AM
Points: 4,862, Visits: 2,243
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!!!
Post #1486751
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse