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

Re: [agileDatabases] Design patterns for access permissions

Expand Messages
  • awhettam@mmm.com
    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
    Message 1 of 14 , Jan 12, 2011
    • 0 Attachment
      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@... | www.3M.com






      From:
      Vasco Figueira <vasco.figueira@...>
      To:
      <agileDatabases@yahoogroups.com>
      Date:
      10/01/2011 18:17
      Subject:
      Re: [agileDatabases] Design patterns for access permissions
      Sent by:
      <agileDatabases@yahoogroups.com>




      Hi all,

      On 10 January 2011 12:54, <awhettam@...> 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]
    • Scott Ross
      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
      Message 2 of 14 , Jan 12, 2011
      • 0 Attachment
        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]
      • Peter Schott
        I ve been following this discussion and wanted to ask a question for clarification. Is the original intent of the question seeking a way to design a way to use
        Message 3 of 14 , Jan 13, 2011
        • 0 Attachment
          I've been following this discussion and wanted to ask a question for
          clarification. Is the original intent of the question seeking a way to
          design a way to use tables that store information about what various users
          can do/access or seeking a way to design the entire system in a secure
          manner relying on DB-level user security on DB objects? Those are two
          different questions.

          For us, we've tended to have a little of both. We can't easily just add
          someone to a certain role or grant DB-level permissions on each user. We use
          a multi-tenant DB model so all of our users share the same database. We use
          various tables that are read by our code to determine what the logged in
          user sees. One design used a windows pass-through authentication to
          determine the current user and looked up their Feature/Application usage
          permissions inside the data-driven model. Some apps store a "users" table
          of some sort inside the DB which then maps to various permissions inside the
          data tables. Still others do rely purely on SQL/Windows permissions and DB
          roles to determine their permissions. We've tended towards a data-driven
          model in our current usage because when the apps we code understand that,
          the end-users can easily control their department/user/customer permissions
          without relying on us to make security changes.

          For other systems where it's purely internal, we're able to use more of a
          server-level permission, but we've still found cases where driving what the
          end-users can see/do off data-driven security models make sense. Yes, we can
          better secure the actual database, but sometimes it's better overall to let
          the users control new people, what they can do, internal
          moves/promotions/etc, and then let the system grant/deny/hide access based
          on tables within the DB.

          To the original poster - can you clarify your question a little? That may
          better drive this conversation. For my part, I'd love to know if anyone
          else uses data-driven security and how they handle it. How do you apply
          that to reporting solutions using standard reporting tools? Do you even
          bother at that point or is your reporting system wide open to all data or
          consist purely of canned reports with no/little flexibility?

          -Peter


          [Non-text portions of this message have been removed]
        • bobcorrick@btopenworld.com
          Hi Gabriel, With particular regard to define, attach and apply permissions to objects as close as possible to how end-users naturally perceive and conceive
          Message 4 of 14 , Jan 14, 2011
          • 0 Attachment
            Hi Gabriel,

            With particular regard to "define, attach and apply permissions to objects as close as possible to how end-users naturally perceive and conceive resources and permissions in their business environment" I suggest:

            Aim to learn and use the fundamental permission capability of wherever you store the data.

            In Oracle for example, you can...

            (1) grant and revoke the ability to insert and update specific data items (columns, not simply a whole table) to roles that are then granted to appropriate users.

            Then ensure that the user identity is provided whenever data is created or updated (for example, in the context of a stored procedure as already suggested in this thread). This prevents accidental changes to data by people who do not intend it (or should not do it).

            (2) create database views that only select specific data items relevant to people's roles (instead of granting access to whole tables to roles/people)

            The views will ensure that people only see data items that are relevant.

            Hope that helps - you may be able to make an analogy from this example to your actual persistence mechanisms.

            Bob.
            --- In agileDatabases@yahoogroups.com, Gabriel Tanase <gabtanase@...> wrote:
            >
            > Hello everybody,
            >
            > Could you please be so kind and recommend good - in your opinion - resources
            > for patterns useful in modeling *user access permissions to "resources" in
            > an application*.
            >
            > The business requirements are to create and manage access profiles (which
            > can be combined, also overridden at individual user level) that then govern
            > - what a user can see from among various bundles of data items in the
            > application
            > - what a user can do with / what operations a user is allowed to apply to
            > those bundles of data items
            >
            > Examples:
            > - users in a particular geographic region are not allowed to see the age /
            > birthday attributes of people whose details are stored in the application's
            > database
            > - only users in a particular group are allowed to approve certain types of
            > modifications to financial records of a specific type
            >
            >
            > Technically, in an application's object model, by "resource", (I think) I
            > mean
            > - an instance of a class
            > - or some part of an instance of a class, which part can be either one or
            > more data attributes of the class or one or more methods (and most often the
            > two are intertwined...)
            > - or several classes taken together
            >
            > Of course the above kind of assumes that those permissions would be enforced
            > in the business objects layer of an application. Technically, some of it may
            > also be delegated to the database layer, however the requirements are to
            > define, attach and apply permissions to objects as close as possible to how
            > end-users naturally perceive and conceive resources and permissions in their
            > business environment.
            >
            > I am of course also interested in the translation of such design patterns
            > into a data design.
            >
            >
            > Thank you very much in advance for any pointers!
            >
            > Best regards,
            > Gabriel
            > ----------
            > http://ie.linkedin.com/in/gabrieltanase
            >
            >
            > [Non-text portions of this message have been removed]
            >
          • Gabriel Tanase
            Peter, Good questions, thank you for asking. I do believe that the answer to your second part ... or seeking a way to design an entire system in a secure
            Message 5 of 14 , Jan 14, 2011
            • 0 Attachment
              Peter,

              Good questions, thank you for asking.

              I do believe that the answer to your second part " ... or seeking a way to
              design an entire system in a secure manner relying on DB-level security on
              DB objects" must be "No". I believe that such design is insufficient for the
              application I am working on.
              The access and operation permissions in this application make more sense
              being defined, existing and being manageable at the level of a 'business
              object', not at DB object level (note I said both "access" AND "operation"
              permissions).

              The answer to the first part of your question "seeking a way to design a way
              to use tables that store information about what various users can do/access"
              is therefore "Yes", however only after at a conceptual level I have figured
              out a good (generic enough) design pattern for permissions against
              application-level resources.
              Should I need to design and implement database object-level permissions in
              support of the application object-level permissions, I will do that too,
              however not as an exclusive way of designing to requirements.


              Kind regards,
              Gabriel
              ----------
              http://ie.linkedin.com/in/gabrieltanase


              On 13 January 2011 18:15, Peter Schott <paschott@...> wrote:

              >
              >
              > I've been following this discussion and wanted to ask a question for
              > clarification. Is the original intent of the question seeking a way to
              > design a way to use tables that store information about what various users
              > can do/access or seeking a way to design the entire system in a secure
              > manner relying on DB-level user security on DB objects? Those are two
              > different questions.
              > [...]
              >
              > -Peter
              >
              >
              > [Non-text portions of this message have been removed]
              >
              >
              >
              >
              >


              [Non-text portions of this message have been removed]
            • Suresh . K
              Hi, is this grants to roles helping when lot of PL/SQL interface are present or is it more of hindrance at that point, just curious Thanks,Suresh K. To:
              Message 6 of 14 , Jan 14, 2011
              • 0 Attachment
                Hi,
                is this grants to roles helping when lot of PL/SQL interface are present or is it more of hindrance at that point, just curious
                Thanks,Suresh K.

                To: agileDatabases@yahoogroups.com
                From: bobcorrick@...
                Date: Fri, 14 Jan 2011 11:43:09 +0000
                Subject: [agileDatabases] Re: Design patterns for access permissions
































                Hi Gabriel,



                With particular regard to "define, attach and apply permissions to objects as close as possible to how end-users naturally perceive and conceive resources and permissions in their business environment" I suggest:



                Aim to learn and use the fundamental permission capability of wherever you store the data.



                In Oracle for example, you can...



                (1) grant and revoke the ability to insert and update specific data items (columns, not simply a whole table) to roles that are then granted to appropriate users.



                Then ensure that the user identity is provided whenever data is created or updated (for example, in the context of a stored procedure as already suggested in this thread). This prevents accidental changes to data by people who do not intend it (or should not do it).



                (2) create database views that only select specific data items relevant to people's roles (instead of granting access to whole tables to roles/people)



                The views will ensure that people only see data items that are relevant.



                Hope that helps - you may be able to make an analogy from this example to your actual persistence mechanisms.



                Bob.

                --- In agileDatabases@yahoogroups.com, Gabriel Tanase <gabtanase@...> wrote:

                >

                > Hello everybody,

                >

                > Could you please be so kind and recommend good - in your opinion - resources

                > for patterns useful in modeling *user access permissions to "resources" in

                > an application*.

                >

                > The business requirements are to create and manage access profiles (which

                > can be combined, also overridden at individual user level) that then govern

                > - what a user can see from among various bundles of data items in the

                > application

                > - what a user can do with / what operations a user is allowed to apply to

                > those bundles of data items

                >

                > Examples:

                > - users in a particular geographic region are not allowed to see the age /

                > birthday attributes of people whose details are stored in the application's

                > database

                > - only users in a particular group are allowed to approve certain types of

                > modifications to financial records of a specific type

                >

                >

                > Technically, in an application's object model, by "resource", (I think) I

                > mean

                > - an instance of a class

                > - or some part of an instance of a class, which part can be either one or

                > more data attributes of the class or one or more methods (and most often the

                > two are intertwined...)

                > - or several classes taken together

                >

                > Of course the above kind of assumes that those permissions would be enforced

                > in the business objects layer of an application. Technically, some of it may

                > also be delegated to the database layer, however the requirements are to

                > define, attach and apply permissions to objects as close as possible to how

                > end-users naturally perceive and conceive resources and permissions in their

                > business environment.

                >

                > I am of course also interested in the translation of such design patterns

                > into a data design.

                >

                >

                > Thank you very much in advance for any pointers!

                >

                > Best regards,

                > Gabriel

                > ----------

                > http://ie.linkedin.com/in/gabrieltanase

                >

                >

                > [Non-text portions of this message have been removed]

                >


















                [Non-text portions of this message have been removed]
              • Luiz Esmiralha
                Gabriel, There is some work done on security patterns for web applications: http://www.scrypt.net/~celer/securitypatterns/final%20report.pdf Regards, Luiz
                Message 7 of 14 , Jan 15, 2011
                • 0 Attachment
                  Gabriel,

                  There is some work done on security patterns for web applications:
                  http://www.scrypt.net/~celer/securitypatterns/final%20report.pdf

                  Regards,
                  Luiz

                  2011/1/6 Gabriel Tanase <gabtanase@...>
                  >
                  >
                  >
                  > Hello everybody,
                  >
                  > Could you please be so kind and recommend good - in your opinion - resources
                  > for patterns useful in modeling *user access permissions to "resources" in
                  > an application*.
                  >
                  > The business requirements are to create and manage access profiles (which
                  > can be combined, also overridden at individual user level) that then govern
                  > - what a user can see from among various bundles of data items in the
                  > application
                  > - what a user can do with / what operations a user is allowed to apply to
                  > those bundles of data items
                  >
                  > Examples:
                  > - users in a particular geographic region are not allowed to see the age /
                  > birthday attributes of people whose details are stored in the application's
                  > database
                  > - only users in a particular group are allowed to approve certain types of
                  > modifications to financial records of a specific type
                  >
                  > Technically, in an application's object model, by "resource", (I think) I
                  > mean
                  > - an instance of a class
                  > - or some part of an instance of a class, which part can be either one or
                  > more data attributes of the class or one or more methods (and most often the
                  > two are intertwined...)
                  > - or several classes taken together
                  >
                  > Of course the above kind of assumes that those permissions would be enforced
                  > in the business objects layer of an application. Technically, some of it may
                  > also be delegated to the database layer, however the requirements are to
                  > define, attach and apply permissions to objects as close as possible to how
                  > end-users naturally perceive and conceive resources and permissions in their
                  > business environment.
                  >
                  > I am of course also interested in the translation of such design patterns
                  > into a data design.
                  >
                  > Thank you very much in advance for any pointers!
                  >
                  > Best regards,
                  > Gabriel
                  > ----------
                  > http://ie.linkedin.com/in/gabrieltanase
                  >
                  > [Non-text portions of this message have been removed]
                  >
                  >
                • bobcorrick@btopenworld.com
                  My post should have included a justification: To secure your data from unwanted addition, viewing, alteration, or deletion; and to protect people from
                  Message 8 of 14 , Jan 16, 2011
                  • 0 Attachment
                    My post should have included a justification:

                    "To secure your data from unwanted addition, viewing, alteration, or deletion; and to protect people from accidentally seeing or doing things that they do not intend; I suggest that you always use the underlying capability of the storage (persistence) mechanism.

                    "This can be supplemented at the application level, for example so that people can choose a level of security less than the maximum to which they are entitled, as a way of avoiding mistakes in usage.

                    "If the underlying security capability is *not* used, your stored data will be vulnerable in cases of: mistakes in development and usage at the application level; and deliberate usage of the stored data by something other than your application level mechanisms."

                    Hope that helps, Gabriel and all.
                    Bob
                    --- In agileDatabases@yahoogroups.com, "Suresh . K" <sureindia@...> wrote:
                    >
                    >
                    > Hi,
                    > is this grants to roles helping when lot of PL/SQL interface are present or is it more of hindrance at that point, just curious
                    > Thanks,Suresh K.
                    >
                    > To: agileDatabases@yahoogroups.com
                    > From: bobcorrick@...
                    > Date: Fri, 14 Jan 2011 11:43:09 +0000
                    > Subject: [agileDatabases] Re: Design patterns for access permissions
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    > Hi Gabriel,
                    >
                    >
                    >
                    > With particular regard to "define, attach and apply permissions to objects as close as possible to how end-users naturally perceive and conceive resources and permissions in their business environment" I suggest:
                    >
                    >
                    >
                    > Aim to learn and use the fundamental permission capability of wherever you store the data.
                    >
                    >
                    >
                    > In Oracle for example, you can...
                    >
                    >
                    >
                    > (1) grant and revoke the ability to insert and update specific data items (columns, not simply a whole table) to roles that are then granted to appropriate users.
                    >
                    >
                    >
                    > Then ensure that the user identity is provided whenever data is created or updated (for example, in the context of a stored procedure as already suggested in this thread). This prevents accidental changes to data by people who do not intend it (or should not do it).
                    >
                    >
                    >
                    > (2) create database views that only select specific data items relevant to people's roles (instead of granting access to whole tables to roles/people)
                    >
                    >
                    >
                    > The views will ensure that people only see data items that are relevant.
                    >
                    >
                    >
                    > Hope that helps - you may be able to make an analogy from this example to your actual persistence mechanisms.
                    >
                    >
                    >
                    > Bob.
                    >
                    > --- In agileDatabases@yahoogroups.com, Gabriel Tanase <gabtanase@> wrote:
                    >
                    > >
                    >
                    > > Hello everybody,
                    >
                    > >
                    >
                    > > Could you please be so kind and recommend good - in your opinion - resources
                    >
                    > > for patterns useful in modeling *user access permissions to "resources" in
                    >
                    > > an application*.
                    >
                    > >
                    >
                    > > The business requirements are to create and manage access profiles (which
                    >
                    > > can be combined, also overridden at individual user level) that then govern
                    >
                    > > - what a user can see from among various bundles of data items in the
                    >
                    > > application
                    >
                    > > - what a user can do with / what operations a user is allowed to apply to
                    >
                    > > those bundles of data items
                    >
                    > >
                    >
                    > > Examples:
                    >
                    > > - users in a particular geographic region are not allowed to see the age /
                    >
                    > > birthday attributes of people whose details are stored in the application's
                    >
                    > > database
                    >
                    > > - only users in a particular group are allowed to approve certain types of
                    >
                    > > modifications to financial records of a specific type
                    >
                    > >
                    >
                    > >
                    >
                    > > Technically, in an application's object model, by "resource", (I think) I
                    >
                    > > mean
                    >
                    > > - an instance of a class
                    >
                    > > - or some part of an instance of a class, which part can be either one or
                    >
                    > > more data attributes of the class or one or more methods (and most often the
                    >
                    > > two are intertwined...)
                    >
                    > > - or several classes taken together
                    >
                    > >
                    >
                    > > Of course the above kind of assumes that those permissions would be enforced
                    >
                    > > in the business objects layer of an application. Technically, some of it may
                    >
                    > > also be delegated to the database layer, however the requirements are to
                    >
                    > > define, attach and apply permissions to objects as close as possible to how
                    >
                    > > end-users naturally perceive and conceive resources and permissions in their
                    >
                    > > business environment.
                    >
                    > >
                    >
                    > > I am of course also interested in the translation of such design patterns
                    >
                    > > into a data design.
                    >
                    > >
                    >
                    > >
                    >
                    > > Thank you very much in advance for any pointers!
                    >
                    > >
                    >
                    > > Best regards,
                    >
                    > > Gabriel
                    >
                    > > ----------
                    >
                    > > http://ie.linkedin.com/in/gabrieltanase
                    >
                    > >
                    >
                    > >
                    >
                    > > [Non-text portions of this message have been removed]
                    >
                    > >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    > [Non-text portions of this message have been removed]
                    >
                  • Enrique
                    You gain maintainability which helps achieve the goal of eliminating silo production; you gain true abstraction which shields the developer from knowing
                    Message 9 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.