Post reply

Stored proc and data access

  • August 2, 2010 at 9:42 pm

    #227369

    I have a requirement where in, data from the DB needs to be accessed, only from views.

    The views represent the tables 1:1

    The developers should access only the view, not the tables. I need a mechanism to restrict the developers from accessing the data from the table.

    Following is a possible solution,

    1.Create a DDL trigger for create/alter proc

    2.In the trigger get the proc body and search for table name

    3.rollback the code, if table name found

    Is there a better way?

  • August 3, 2010 at 4:01 am

    #1201419

    Create a user role with SELECT permissions on the views and DENY SELECT on the source tables. This will be a lot easier to setup and maintain than the trigger approach...

    To test the procs run it using this restricted role.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

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