How to hide stored procedures from user?

Question

How to hide stored procedures from user?

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

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

andrewbb Old Hand Points: 368 More actions · Answer 1

EdVassie (10/5/2015)
If you think that hiding objects will make your system more secure then you are wrong. At best it will give you a warm fuzzy feeling of security, but anyone with the right skills can find what they want. They can even use your attempts at 'security' to target their attack.
If you want to implement row-level security then Google can find you methods of doing this that can work on SQL 2000 onwards. I had row-level security working back in 1995 on a project I designed. Some of the roll-your-own techniques may not perform as well as the built-in stuff, but you choose your SQL version and make the most of it.

I agree, however application developers do not understand these concepts. I think a clean separation needs to be made between application development and database development and security.

The ORMs have reflected far too much of the database into the application layer.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 2

Ideally, each application or user account should be a member of a role, and the only additional permissions for each role should be execution on only the procedures that role needs. So they can see the procedures (but not the T-SQL), execute the procedures, but can't read or modify anything outside the context of the procedure.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

andrewbb Old Hand Points: 368 More actions · Answer 3

Eric M Russell (10/5/2015)
Ideally, each application or user account should be a member of a role, and the only additional permissions for each role should be execution on only the procedures that role needs. So they can see the procedures (but not the T-SQL), execute the procedures, but can't read or modify anything outside the context of the procedure.

I agree that is a good methodology. I think it can be further improved by standardizing an external interface to the stored procedures. Part of that interface would include the logged-in user. Then, from the application developer's perspective they would simply write:

Database.Load(userID, object)

Database.Store(userID, object)

Database.Delete(userID, object)

Total security while dramatically simplifying the application.

Note: the userID might be null, depending on the application. So, it supports role-based or user-based security on either application-side or database-side, or both.