locked
Are EXEC/EXECUTE a security risk for locked-down read-only DB users? RRS feed

  • Question

  • In our product we have a way for users to specify a SQL query and we execute it for them. This, obviously, could be a massive security hole, and we've done a bunch of stuff to protect against this, sufficiently so to pass any security audit we've been put through. Basically, we've created a read-only user in SQL Server that only has permissions to a limited set of tables, no access to sp_, sys., master., xp_. etc. anywhere. We're also blocking off any SQL function that we deem a potential problem too (blacklisting at when they try and create the report, way before execution...).

    My question is, should EXEC/EXECUTE be blocked off too. There are legitimate reasons for our users to have a simple select query that uses EXEC, but I don't want to open that up if it still would allow them to get at any data they shouldn't or perform an operation that would be unsafe. I can't come up with anything, but that's why I'm asking!

    Thanks for any help,

    Andy.

    Wednesday, October 28, 2015 6:49 PM

Answers

  • You can't ACL the EXECUTE command. It's like trying to prevent a user from running the SELECT command. You can prevent the user from doing anything useful or harmful by locking down the underlying securables/resources (which it looks like you're already doing) but the EXEC/EXECUTE command itself cannot be locked down outside of preventing connection completely.

    For instance, a user can run EXEC dbo.spSomeSproc but if he does not have exec permissions on the SP, it will fail. If he does have exec permissions on the SP but not on the underlying tables the SP calls, it will still fail (ignoring EXECUTE AS for now). He could even try EXEC ('USE myDB; SELECT * FROM myDBTable1') and the same permissions check will apply on the securables. If he does not have permissions to connect to myDB, it will fail to switch to myDB. If he has connect permissions to myDB but no READ permissions on myDBTable1, it will fail on that statement.

    Don't worry about blocking of EXECUTE (you can't anyway). Focus on locking down your securables and making sure the queries they run are legitimate. Remember that a query that passes security checks might still cause you grief. For instance, running a cross-join on 2 tables with a billion rows each can DOS your instance by consuming lots of memory and hogging IO if you don't have resource governor policies in effect.


    No great genius has ever existed without some touch of madness. - Aristotle

    Thursday, October 29, 2015 9:04 PM

All replies

  • Hi Andy,

    EXEC permission only applies to stored procedures. Either you can grant this at database level which grants exec rights on all the stored procs in the database or you can grant EXEC permissions on stored procedures individually if required. 

    GRANT EXEC ON [Sp_Name] TO [User_Name]

    My question is, should EXEC/EXECUTE be blocked off too. There are legitimate reasons for our users to have a simple select query that uses EXEC

    I  didn't understand this, Can you post a sample script of the select statement with exec in it?

    Thanks

    Bhanu

     
    • Edited by bhanu_nz Wednesday, October 28, 2015 8:00 PM
    Wednesday, October 28, 2015 8:00 PM
  • Hi Bhanu,

    Thanks for replying. I was not referring to the EXEC permission but rather the EXEC/EXECUTE command. Sometimes our reports need to dynamically create the query to be executed, so something like this:

    DECLARE @qry varchar(300);
    SET @qry = 'select * from Projects';
    EXEC @qry;

    I don't think being able to use EXECUTE allows users to anything more than they could just be running a standard select, but I just wanted to be sure!

    Thanks,

    Andy

    Thursday, October 29, 2015 10:51 AM
  • If you parse the query yourself, and ensure that it contains a single SELECT then you have an extra layer of protection, instead of relying on your properly configured database security.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, October 29, 2015 1:57 PM
  • Hi Andy,

    To my knowledge, there is no issue if EXEC it is being used in dynamic sql. I have tested this. There are no explicit permissions required to execute nor there is a way to deny this action to a specific user when being used  in dynamic sql. Under covers SQL Server checks the permission while executing 'select * from Projects' and only returns the result set if the user has access to this table.

    You can deny EXEC permissions on the database level for users that restricts them from executing all stored procs on that database. This wont have any effect on the EXEC @qry

    You can test this your self by creating a test user and observing the behaviour

    Hope this helps

    Thanks

    Bhanu


    • Edited by bhanu_nz Thursday, October 29, 2015 10:07 PM
    Thursday, October 29, 2015 7:53 PM
  • You can't ACL the EXECUTE command. It's like trying to prevent a user from running the SELECT command. You can prevent the user from doing anything useful or harmful by locking down the underlying securables/resources (which it looks like you're already doing) but the EXEC/EXECUTE command itself cannot be locked down outside of preventing connection completely.

    For instance, a user can run EXEC dbo.spSomeSproc but if he does not have exec permissions on the SP, it will fail. If he does have exec permissions on the SP but not on the underlying tables the SP calls, it will still fail (ignoring EXECUTE AS for now). He could even try EXEC ('USE myDB; SELECT * FROM myDBTable1') and the same permissions check will apply on the securables. If he does not have permissions to connect to myDB, it will fail to switch to myDB. If he has connect permissions to myDB but no READ permissions on myDBTable1, it will fail on that statement.

    Don't worry about blocking of EXECUTE (you can't anyway). Focus on locking down your securables and making sure the queries they run are legitimate. Remember that a query that passes security checks might still cause you grief. For instance, running a cross-join on 2 tables with a billion rows each can DOS your instance by consuming lots of memory and hogging IO if you don't have resource governor policies in effect.


    No great genius has ever existed without some touch of madness. - Aristotle

    Thursday, October 29, 2015 9:04 PM