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

Design patterns for access permissions

Expand Messages
  • Gabriel Tanase
    Hello everybody, Could you please be so kind and recommend good - in your opinion - resources for patterns useful in modeling *user access permissions to
    Message 1 of 14 , Jan 6, 2011
    • 0 Attachment
      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]
    • berthooyman
      Interesting to see your definition of a resource. Normally end users are not much concerned with classes at all, since classes are a result of software design,
      Message 2 of 14 , Jan 10, 2011
      • 0 Attachment
        Interesting to see your definition of a resource. Normally end users are
        not much concerned with classes at all, since classes are a result of
        software design, not functional design. The concept may still apply to
        the restricted notion of "domain model classes" where the domain model
        is an object-oriented representation of the business problem.

        I normally think of access control as two problem areas:
        One is 'traditional' access control:
        given a user's role or identity or group membership, what tasks is he or
        she allowed to perform. The tasks you could think of are reflected in
        the use cases (if you have a very complete use case model of your
        problem) or as CRUD entitlements against your data objects. This is a
        course-grained model, it is not at the entity instance level but at the
        entity type level (you may replace those terms by object and class).


        The other one is data entitlements:
        This comes over and above access control as defined in the previous
        paragraph. Once a user has access to a particular entity type, say "read
        access" to "sales orders", the data entitlements further restrict the
        visibility. These entitlements are criteria (or where clauses) for
        example "totalOrderValue <= USD 45,000" would imply that this particular
        user can only see sales orders up to 45000 USD.
        You can take this one step further and apply it at the attribute level
        so that a sales rep in one region can see the sales orders from other
        regions; he/she can see the order quantities and order values, but not
        the customer details.


        --- 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]
      • awhettam@mmm.com
        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
        Message 3 of 14 , Jan 10, 2011
        • 0 Attachment
          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
          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
          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.)

          The above would be generally regarded as good security practice.

          If you follow the above principles, the data visible to a user can be
          controlled at the Stored Procedure level - if using Oracle, the ability to
          overload packages might be useful.

          I hope this helps




          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:
          Gabriel Tanase <gabtanase@...>
          To:
          <agilemodeling@yahoogroups.com>, <dm-discuss@yahoogroups.com>,
          <agileDatabases@yahoogroups.com>
          Date:
          06/01/2011 14:46
          Subject:
          [agileDatabases] Design patterns for access permissions
          Sent by:
          <agileDatabases@yahoogroups.com>




          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]
        • Vasco Figueira
          Hi all, ... From the bottom of my short experience, I disagree. With all the security/authorization mechanisms available, per schema, per table, per column and
          Message 4 of 14 , Jan 10, 2011
          • 0 Attachment
            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]
          • kerrykimbrough
            Looking at this topic from a security (access control) perspective, I like Andrew Whettam s recommendations. At least in Oracle, encapsulating access via
            Message 5 of 14 , Jan 10, 2011
            • 0 Attachment
              Looking at this topic from a security (access control) perspective, I like Andrew Whettam's recommendations. At least in Oracle, encapsulating access via stored procedure makes the grants simpler and more manageable. You can grant "execute P" to A without granting A all the privileges needed to do what P does.

              But then that leads to another perspective on this topic. What exactly are the privileges that P needs to do its work? Does it need to access data in other schemas? In a multi-schema system, what are all the privilege relationships needed? That's more of a system architecture perspective.

              I've found that it's an architectural view that deserves to be modeled and considered early in the dev cycle. Otherwise, you can stumble into a system with a complex, fragile, and insecure network of privilege relationships.

              Regards,
              Kerry
            • 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 6 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 7 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 8 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 9 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 10 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 11 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 12 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 13 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 14 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.