Prevent access to table

[djj55]

Hello, in 2008 I remember reading something about how to only let a stored procedure edit your table.

The question is am I remembering or dreaming? If there is a way, where do I look to find information on the process?

Thank you,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[RiverGuy]

Well, standard practice for many OLTP SQL Server systems is to allow data access only through stored procedures. This means no one besides administrators can read or write to a base table. No users are in dbreader or dbwriter groups.

It's as simple as GRANT EXECUTE ON p_SomeStoredProcedure TO SomeLoginName.

SomeLoginName has no permissions other than being allowed to execute a set of stored procedures for use in your application.

 

[djj55]

Thank you, I thought I remembered something. We are going from wide open 2000 to get our act together 2008, with a major front-end rework.

Thanks again.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[RiverGuy]

If you can manage it, it might be worth looking at a whole host of things since you're doing a rework. If you are lacking any of the following, I would suggest investigating and implementing some of them, if they work for your information system:

1. Defined Primary Keys/Foreign Keys
2. Each table to include LastUpdated/LastUpdatedBy columns which are enforced via triggers.
3. Audit tables for your important tables maintained via triggers.
4. Application access only through stored procedures (your question above).
5. Proper indexing.

 

[djj55]

Thank you RiverGuy, we have been slowly moving toward your suggested items.

Of the five I have yet to tackle taming the triggers that we have created over the past few months.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!