Provide access to View Vs Tables

  • Dears,

    Hope this thread finds you well

    I have been creating DALs ( Data Acess Points) in SQL using Views not tables. This because I heard that views are better for it due to:

    1 - Tables behind can be changed without the need to change the View it self to which the user connects to to import information

    2 - Better from a security perspective as the permissions are given to the view but not to the table it self

    Do you agree with the above statement and reasons? Do you see any other reasons I can point as well in terms of using Views instead of tables for direct access of information?


    Thank you,





  • If the table changes, the view doesn't change, this could cause you issue.

    So you had this table

    CREATE TABLE Table1 (
    ID INT,
    Firstname VARCHAR(50),
    Lastname VARCHAR(50)

    You create a view over this.

    Then you add a new column


    The view still thinks it has ID, Firstname, Lastname.

    You try to insert into that view giving it ID, Firstname, Lastname, DOB it will fail.

    But then say you wanted to change the size of the VARCHAR columns


    The view metadata still thinks the column is a VARCHAR(50), but the table is now actually a VARCHAR(10).

    Try inserting a string longer than 10 and you'll end up with truncation issues.


    So point 1 is mute, if you change the table you must also ensure you refresh the view(s) that those tables touch otherwise you end up with all sorts of behaviour issues.



    For a security perspective yeah having an abstraction layer is good, as you move the security else where, but you need to ensure the abstraction layer keeps up with the metadata of the underlying tables.


    Not saying views are bad, just watch out for when the metadata changes, may be one to get into a habit of running "sp_refreshview" to update the metadata when you change the table definitions to ensure things are always aligned with the base tables.

  • I prefer giving access to views, for the reasons you give in 1 & 2.

    View definition only needs to change when it is no longer compatible with its tables

  • We create a separate schema.  Permissions are provided to the schema.

    Then for every object that needs to be exposed, we create a synonym under the exposed schema that maps to that maps to the underlying object.

  • On one of our systems we don't give any permissions to any tables or views to users, users only get permissions to a specific list of stored procedures.

  • Post withdrawn.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply