Results 1 to 6 of 6

Thread: Read-Only Views

  1. #1
    Join Date
    Jun 2003
    Posts
    5

    Unanswered: Read-Only Views

    I have created several read-only views of existing accounting system tables. What is the best way to make sure these views (which will be used by many users) are only read only. I don't seem to find any thing about a "Read-Only" clause in the documentation. BTW, as you can tell I am a newbee to MS-SQL

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    For each view, grant SELECT permission and deny INSERT, UPDATE, and DELETE permissions for the users you want to restrict, especially the PUBLIC role.
    -or-
    Create a trigger on the view for INSERT, UPDATE, and DELETE which rolls back any changes.
    -or-
    Have your users connect to the database only through stored procedurs which select from the view or directly from the underlying tables.

    Some of your views may be read-only anyway because of the way tables are joined.

    blindman

  3. #3
    Join Date
    Jun 2003
    Posts
    5

    Smile

    So if I create a user called 'Read.Only' and give that user only "Select" permissions then any application using that user to connect to the view will only have read-only rights?

    To "deny" means that the respective column is not checked?

    Thank you!

    Originally posted by blindman
    For each view, grant SELECT permission and deny INSERT, UPDATE, and DELETE permissions for the users you want to restrict, especially the PUBLIC role.
    -or-
    Create a trigger on the view for INSERT, UPDATE, and DELETE which rolls back any changes.
    -or-
    Have your users connect to the database only through stored procedurs which select from the view or directly from the underlying tables.

    Some of your views may be read-only anyway because of the way tables are joined.

    blindman

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Since its possible for a user to be a member of several different roles, you need to do more than just leave the box unchecked (which might allow them access through a different role). If you click on the box again, you will see a red X appear through it. This means the user will not be able to have unintended access to your data through any role.

    SQL Server security is powerful, but it can also be confusing. You need to spend some time learning about it from Books online or have a consultant come in for a day to advise you.

    blindman

  5. #5
    Join Date
    Jun 2003
    Posts
    5

    Smile

    We are planning for in-house training, but this was something I had to get done ASAP.

    Thanks, again!

    xj1inatl

    Originally posted by blindman
    Since its possible for a user to be a member of several different roles, you need to do more than just leave the box unchecked (which might allow them access through a different role). If you click on the box again, you will see a red X appear through it. This means the user will not be able to have unintended access to your data through any role.

    SQL Server security is powerful, but it can also be confusing. You need to spend some time learning about it from Books online or have a consultant come in for a day to advise you.

    blindman

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    Just to add to blindman's suggestions, try to see if you can completely deny rights on the base tables to all the users and allow the access only through stored procedures. An added level of security.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •