Grant Access to Stored Procedure and Hide Underlying Tables

Database
Enthusiast

Grant Access to Stored Procedure and Hide Underlying Tables

Using TD 13.10.

Is there a way to grant use to user id for a Stored Procedure and effectively HIDE the tables below it?

Goal:

  1. Give User Ability to Run Stored Procedure
  2. Hide the Objects used in the Stored Procedure from User
  3. Not allow the User to Select direct from the tables used in the stored procedure

This sounds strange, but there is a valid need to do this.  

That being we need the user to only ever see what they are eligible for, not what everyone else could potentially be eligible for.

Thanks!

Tags (2)
1 REPLY
Enthusiast

Re: Grant Access to Stored Procedure and Hide Underlying Tables

Stored proc or a View database can help you on this. But depends on the scenario how you would want to use it.

if it is jus selecting records from the table its better we can go ahead with View Database. Create as many views you want and provide access to the users to the views and the view database to have select access on the target database.

Just for selecting records SP's are not recommended. But if you want to collect stats on your target table you can have a stored proc created at a higher level database and provide execute access to the authorized users to the SP and SP can inturn have access to collect stats on the table.