User Granted Access to Stored Procedure but Can't Run Query

Question

I am working on a product that runs an SQL server which allows some applications to login and their logins are granted permission to run a stored procedure- AND NOTHING ELSE. The stored procedure is owned by an admin; the stored procedure takes a query and executes it, then the results are returned to the application.

Unfortunately I can't figure out why the application can call the stored procedure to which it's granted access, but the stored procedure cannot execute the SQL statement which was passed into it.

The stored procedure executes the passed in query when I'm logged in as an admin, but when I log in as the limited user it throws an exception in the execute statement.

For example:

EXEC [Admin].[STORED_PROC] @SQL_STATEMENT = 'SELECT * FROM table_x'

the STORED_PROC looks something like this:

BEGIN TRY
   EXEC (@SQL_STATEMENT)
END TRY
BEGIN CATCH
   -- some logging when an exception is caught, and the exception is caught here!!!
END CATCH

There is nothing inside the the try catch statement except that EXEC... and the SQL_STATEMENT works when I'm logged in as the Admin, but not when I'm logged in as the User.

Can anybody help me figure out what permissions I need to set in order to allow the User to run queries through the stored proc only?

---

So there have been some comments about allowing raw SQL statements to be executed via stored proc defeats the purpose of using a stored proc... but in reality what we're actually doing is we're passing an encrypted SQL statement into the stored proc and the stored proc gets the statement decrypted and THEN it executes it.

So yes, in reality raw SQL statements are not secure and they defeat the purpose of stored procs, but I don't know how to encrypt SQL queries that are passed through ODBC and run against a pre-2005 SQL Server.

In any case, I tried to put up some minimal safeguards to at least have some basic security.

Answer 1

Users also need to have SELECT grant on the tables

Answer 2

Since you are using dynamic sql, SQL server can't tell which tables you are using, so you have to grant SELECT rights to all the tables as well

Answer 3

Allowing raw SQL to be passed into a stored procedure and then executing is the very essence of data insecurity.

SQL Server security is structured so that arbitrary bits of SQL execute in their own security context. If you don't have the permission to run the query ad hoc, you also don't have the permission to run it through a stored procedure. In this, SQL Server is saving you from yourself.

Answer 4

Since your system allows access to stored procs and nothing else (which is good for security purposes and should not be changed) then you simply cannot under any circumstances use dynamic SQL because the rights are not at the table level and your dbas are unlikely to change that. This is not only to prevent SQL Injection attacks but to prevent possible internal fraud so any workplace which has considered this important will not be willing to compromise to make life easier for you. You simply need to redesign to never do anything dynamically. You have no other choice. If you write the procs to do what you want it to do in the first place, there is no need to send encypted sql.

Answer 5

When dynamic SQL is used through EXEC or sp_executesql within an SP, the EXEC permissions on the SP do not allow you to run arbitrary code in the dynamic sql. You either need to grant SELECT (yuck), or you might be able to impersonate another user using EXECUTE AS or SETUSER.

When normal SQL is used, EXEC permissions works fine, overridding ungranted SELECT persmissions. If you have DENY, though, I believe that trumps it.

Having said that, I'm still not sure you should use EXECUTE AS when the source of the SQL is outside the SP (or outside the database). For code-generation or dynamic sql which is safe from outside influence, EXECUTE AS can be a useful tool

Answer 6

Hi,

This is most likely because of different schemas i.e. the user who logs in is not part of the Admin schema, or at least I would hope not.

The security technique that permits the type of access you are looking to achieve, i.e. to permit access to objects that are owned by the same schema, is called Ownership Chaining.

This principle is not best explained in a post.

Here is a link from Microsoft that explains the concept.

http://msdn.microsoft.com/en-us/library/ms188676(SQL.90).aspx

Here is a an outstanding article on security that provides examples and walkthroughs, for ownership chaining, amongst other techniques.

http://www.sommarskog.se/grantperm.html

I hope this is clear and assists you but please feel free to pose further questions.

Cheers, John