Post reply

Elevate permissions within stored procedure

  • December 17, 2003 at 2:11 pm

    #486836

    Are views updateable in SQL Server 2000? I've never tried to use views beyond a select capability.

    Another option I could employ is to encrypt the data so that it is eseentially useless unless managed through the application.

  • December 17, 2003 at 2:39 pm

    #486849

    Yes, views are updateable and have their own sets of permissions. So long as the owners are the same between the views and the base tables, ownership chaining occurs, meaning you don't have to assign permissions against the base tables themselves. And encrypting through the app would help.

    K. Brian Kelley, GSEC

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

  • December 17, 2003 at 3:23 pm

    #486859

    SQL Server VIEWs can even limit the data that may be inserted / updated if you use the WITH CHECK OPTION in the view.

    CREATE TABLE A (ID Int, ADate DateTime)

    Then

    CREATE VIEW A_vw as

    SELECT * FROM A Where ADate >= GetDate()

    WITH CHECK OPTION

    INSERT INTO A_vw SELECT 1, GetDate()

    will work, but

    SELECT * FROM A_vw

    will not return any records.

    This is a simplified example, but you can modify it with things like WHERE @@Servername = StoredServerColumn etc.



    Once you understand the BITs, all the pieces come together

Viewing 3 posts - 16 through 17 (of 17 total)

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