Loading ...
Sorry, an error occurred while loading the content.

Re: Design patterns for access permissions

Expand Messages
  • Enrique
    You gain maintainability which helps achieve the goal of eliminating silo production; you gain true abstraction which shields the developer from knowing
    Message 1 of 14 , Jan 24, 2011
    • 0 Attachment
      "You gain maintainability which helps achieve the goal of eliminating silo production; you gain true abstraction which shields the developer from knowing complex details of tsql (optimization, for starters);"

      Ahh... "shield" the developer from knowing complex details of SQL. Sounds tempting but in the end someone has to know those complex details, specially when performance goes down the drain.

      I support an application that relies on Hibernate and yes, development is quicker and simpler because HQL is a subset of SQL (I actually call it Handicapped Query Language). Any developer can write HQL that "works" on a tiny development database but when we test the code with a user simulation tool (say 2500 users), the code fails miserably because performance is dismal. Even with HQL, the developer needs to understand how the database works and how SQL works otherwise there is a very hefty price to pay in terms of performance and scalability.

      Enrique


      --- In agileDatabases@yahoogroups.com, Scott Ross <sr523@...> wrote:
      >
      > Well, here is another opinion on the matter. For me, Stored Procedures are not in the vocabulary for most modern systems being built today. I develop in the .Net world with SQL and Oracle backends. This discussion is applicable to most modern language's; but obviously there are instances where SP are both desired and the necessary tool. Developing a good data access strategy that fits your current needs and expectations is always more relevant that coming to broad conclusions, but this should add to the debate:
      >
      > Introduction
      > Over the years there has been a debate in the .NET community regarding the use of stored procedures vs. inline SQL. This debate has been poorly worded, to a degree, and has directly influenced programming decisions made at local shops. Most shops are currently under the self-imposed mandate that our data access layers use Stored Procedures instead of inline SQL, and thus we have completely ignored O/R Mapping tools (object-relational mapping tools) that are available. The point of this document is to debate the pros and cons of using stored procedures, and provide an argument to switching our mentality away from stored procedures to an object relational mapping tool.
      >
      > The benefits of O/R mappers are as followed:
      >
      > 1. You'll end up with less code, which results in a more maintainable system,
      > 2. You gain a true level of abstraction from the underlying data source, because your O/R mapper takes the role of creating the appropriate SQL and because you are providing mapping information between your table schemas and domain objects,
      > 3. Your code becomes simpler,
      > 4. Developing the same O/R mapping functionality across all development projects takes the place of developing standards for writing Stored Procedures, and helps in creating a more maintainable infrastructure
      >
      > Different O/R mapping frameworks:
      >
      > * LINQ to SQL
      > * ADO.NET Entity Framework
      > * LightSpeed
      > * SubSonic
      > * PLINQO
      > * NHibernate
      >
      > What is O/R Mapping
      > From wiki:
      >
      > Object-relational mapping (aka ORM, O/RM, and O/R mapping) is a programming technique for converting data between incompatible type systems in relational databases and object-oriented programming languages. This creates, in effect, a "virtual object database," which can be used from within the programming language.
      >
      >
      > Setting the debate up
      > When talking about inline SQL, we are speaking about the equivalent to this:
      >
      > string sql = "SELECT UserId FROM Users WHERE UserName = @UserName AND Password = @Password";
      >
      > using (SqlCommand command = new SqlCommand(sql))
      > {
      > command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = userName; command.Parameters.Add("@Password ", SqlDbType.VarChar).Value = password;
      >
      > return 0; //todo
      > }
      >
      >
      > Not this:
      > string sql = @"SELECT UserId FROM Users WHERE UserName = '" + userName
      > + "' AND Password = '" + password + "'";
      >
      > using (SqlCommand command = new SqlCommand(sql))
      > {
      > return 0; //todo
      > }
      >
      > *** The above is flawed code
      >
      > Stored Procedures are more secure...
      > This is one of my favorite arguments about stored procedures, SQL server and the .NET world. But the reality is the statement is false. And here's why...
      >
      > Inline SQL should be written using parameterized queries just like you do with stored procedures, and shown in first example above. These parameterized queries provide the same functionality as stored procedures, thus they eliminate most SQL injection attacks (the same level of protection SPs do). In this context, there is no security gain or loss using either method (inline sql or stored procedures). Thus using an O/R Mapping solution also provides the same security as stored procedures.
      >
      > Secondly, the argument for stored procedures being more secure relates back to the ability to manage access rights by the database to each individual stored procedure and/or tables. This is done by creating roles and users for each user accessing the database. However, we design applications using one user account to access the database, thus this security is eliminated from our design principal. In fact, the debate here is where that control over access should be done- at the application layer or at the database level.
      >
      > Stored procedures provide an abstraction to the underlying schema...
      > Admittedly, I have also fallen into the trap believe abstracting SQL code to the stored procedure layer is a good way of minimizing the impact of changes made to underlying SQL statements in regards to changes made to tables or the underlying data being return. While providing abstract layers is a solid way of looking at development (and a core principal of OO design), the error here was believing stored-procedures provided an abstract layer when in fact the opposite is true.
      >
      > Neither in-line sql nor stored procedures provide an abstraction layer, because essentially they are the same code residing in different locations. Essentially you aren't gaining any abstraction by putting the exact same code in a separate place. If you make a change to the stored procedure, it will certainly have an effect on your application.
      >
      > The ideal situation happens when you create a layer of abstraction that's configurable and implements its own query language. Therefore, changes to the data set only need to be made at the configuration layer, not throughout the entire application.
      >
      > Finally, following the stored procedure approach forces us to become experts in the database realm and in T-SQL. This puts us in a position where we should know how to optimize queries, understand the language fully, and utilize best practices. This is also true of inline sql. In that respect, we see how an O/R Mapping layer helps us by writing SQL code for us, thus relieving developers of the burden/roles meant for DBAs.
      >
      > Stored procedures are faster...
      > "Jeff Atwood called using stored procedures for the sake of performance a fairly extreme case of premature optimization" ~ Foundations of programming.
      >
      > The general consensus Jeff was driving at in this statement is to not attack optimization needs until they arise. But the argument over time has been that inline SQL is somehow slower than stored procedures. At one point, this was absolutely true. But as SQL Server has gone through multiple iterations; SQL Server 2008 (soon to be 2010); and with VMIT sitting at SQL Server 2005; it has become a fallacy to believe inline parameterized sql runs slower than stored procedures.
      >
      > Microsoft whitepaper on the issue:
      > http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
      >
      > The second argument in regards to speed related to network traffic. At one time, it was an absolute must to reduce traffic between systems because of hardware restraints. Those no longer exist, making this point irrelevant.
      >
      > Discussion on O/R Mapping
      > I mentioned earlier that O/R mapping provides four distinct benefits to the developer, the team and eventually the customer. I've tried to outline the discussion and debate over inline SQL vs. stored procedures, and attempted to tie each of those sections back to O/R mapping. In the end, the debate between inline sql and stored procedures is a trivial one. When deciding those two, just pick one, and move on. But when you throw O/R mappers into the mix, you suddenly gain signification advantages.
      >
      > You gain maintainability which helps achieve the goal of eliminating silo production; you gain true abstraction which shields the developer from knowing complex details of tsql (optimization, for starters); creates a mapping from a relational data set to an object data set creating an entirely new world of possibilities; and finally eliminates the plumbing work for each projects data access layer creating less code which improves productivity and maintainability.
      >
      > --
      > Scott Ross
      > Programmer Analyst
      >
      >
      > From: agileDatabases@yahoogroups.com [mailto:agileDatabases@yahoogroups.com] On Behalf Of awhettam@...
      > Sent: Wednesday, January 12, 2011 9:57 AM
      > To: agileDatabases@yahoogroups.com
      > Subject: Re: [agileDatabases] Design patterns for access permissions
      >
      >
      >
      > It's more fun when there are different opinions, isn't it!
      >
      > Some reasons for using Stored Procedures:
      >
      > 1. Security
      > - it makes SQL Injection much harder
      > - it prevents unrestricted access to tables and therefore limits
      > accidental or malicious alterations to data
      > - in my view it makes permissions simpler to manage, particularly
      > when one gets to differentiating permissions by column, row or filter
      > criteria
      > - I would still set permissions at a Schema level where that is
      > appropriate
      >
      > 2. Maintainability
      > - if SQL code is not in SPs than it is likely to be embedded in
      > application code
      > - any SQL code changes, however minor, require a full
      > application release
      > - performance tuning is a lot harder with embedded SQL
      > code and, as testing is often not completed until after the application is
      > released, may require further full releases
      >
      > 3. Performance
      > - some RDBMS systems cache query plans for SPs but not for single
      > statements or blocks
      > - can reduce the number of DB calls and therefore network traffic
      > in some circumstances
      > - sometimes a piece of business logic may genuinely be best kept
      > as close to the database as possible
      >
      > 4. Decoupling
      > - using a stored procedure layer separates data storage from data
      > use and presentation - table design should prioritise data integrity and
      > storage efficiency, class design is unlikely to prioritise these
      > (nor should it in most cases)
      > - "encapsulate what varies", "programme to interfaces, not
      > implementations" and "strive for loosely coupled designs" are oft cited OO
      > principles - stored procs fit well with these
      >
      > I would also advocate good development practices, such as
      > - store all code, including SQL code, in a recognised source control
      > repository
      > - peer review all code where possible
      > - unit test as much as possible, including stored procedures
      > - have a proper release procedure that covers all scenarios, including
      > urgent and emergency database changes
      > etc. etc.
      >
      > I hope this contributes to the debate!
      >
      > Andrew Whettam | Database Team Leader
      > 3M Security Printing and Systems Ltd
      > Gorse Street, Chadderton | Oldham, Lancs. OL9 9QH
      > Office: +44 0 161 683 2478
      > awhettam@...<mailto:awhettam%40mmm.com> | www.3M.com<http://www.3M.com>
      >
      > From:
      > Vasco Figueira <vasco.figueira@...<mailto:vasco.figueira%40knowledgeworks.pt>>
      > To:
      > <agileDatabases@yahoogroups.com<mailto:agileDatabases%40yahoogroups.com>>
      > Date:
      > 10/01/2011 18:17
      > Subject:
      > Re: [agileDatabases] Design patterns for access permissions
      > Sent by:
      > <agileDatabases@yahoogroups.com<mailto:agileDatabases%40yahoogroups.com>>
      >
      > Hi all,
      >
      > On 10 January 2011 12:54, <awhettam@...<mailto:awhettam%40mmm.com>> wrote:
      >
      > >
      > >
      > > Hi Gabriel
      > >
      > > If the resources in question are stored in a database, I would recommend
      > > the following best practices:
      > >
      > > 1. all access to the database should be through Stored Procedures /
      > > Functions / Views - tables should not be queried directly
      > >
      >
      > From the bottom of my short experience, I disagree.
      >
      > With all the security/authorization mechanisms available, per schema, per
      > table, per column and per-row, when you can setup different users with
      > different permissions over the same data, why would one complexify the
      > setup
      > with procedural code telling what shall be available?
      >
      > In my view, if you can model your permissions scheme only using several
      > users with different permissions and synonyms you're fine. If you
      > absolutely
      > need more, then use views which can declaratively help ease some of the
      > complexity.
      >
      > Functions are OK for data-related pervasive concepts (avoid business
      > logic).
      > I think stored procedures should be used as a last resort (and it doesn't
      > smell that good when they are really needed).
      >
      > I am happy to be shown wrong, though.
      >
      > > 2. permissions to execute Stored Procedures should be granted to roles,
      > > groups etc., not to individuals
      > > 3. permissions should prevent direct access to database tables by users
      > or
      > > applications
      > >
      >
      > Read-only for certain tables and users directly is fine. Depending on the
      > system it may well not be worth the effort. I wouldn't enforce this rule
      > thoroughly.
      >
      > > 4. changes to an individual's permissions should be managed through
      > group
      > > membership (e.g. Windows Active Directory)
      > > 5. user account management, passwords, etc. should be kept out of the
      > > database - use an industry recognised security management system (AD,
      > > Novel, etc.)
      > >
      >
      > Agree.
      >
      > Vasco Figueira
      > Consultor Especialista Java
      > KnowledgeWorks - Consultoria em Sistemas de Informação
      >
      > Av. Eng. Arantes e Oliveira, nº 11, 4º A
      > 1900-221 Olaias
      >
      > Lisboa - Portugal
      >
      > Tel : 21 247 31 34
      > Fax: 21 010 23 09
      > Tel: 91 428 28 34
      >
      > http://www.knowledgeworks.pt
      >
      > [Non-text portions of this message have been removed]
      >
      > [Non-text portions of this message have been removed]
      >
      >
      >
      > [Non-text portions of this message have been removed]
      >
    Your message has been successfully submitted and would be delivered to recipients shortly.