1

I am trying to lock down a SQL Server database server and I want to revoke execute access from all users on specific builtin functions such as LEFT, RIGHT SUBSTRING, etc.

When I try to execute:

REVOKE EXECUTE ON LEFT from appuser;

I get an error:

Cannot find the object 'LEFT', because it does not exist or you do not have permission.

In addition, are there any other functions that can perform the same operation on SQL server, i.e. be used to extract portions of a static string ?

Thanks

4
  • Locking down sql server means granting minimum permissions to authorized users. You can't revoke rights on built-in functions.
    – Kin Shah
    Commented Mar 24, 2016 at 13:15
  • @Kin its semantics but I would say after you remove all you deem unnecessary for the usage profile of that database server then you start thinking of minimum permissions. Same way we were removing xp_cmdshell in past versions of SQL server.
    – Zach
    Commented Mar 24, 2016 at 13:43
  • Agree with @Kin, you're going about this the wrong way. The process of securing aims at minimizing the risk of users exposing or damaging the data or the server. You wouldn't accomplish this by restricting the use of LEFT(). Commented Mar 24, 2016 at 16:48
  • @daniel-hutmacher You are correct on the aims of the process of securing. An app will be used to access the data stored on the database. I want to prevent malicious application of SQL injecting the database. A common SQL injection strategy is blind sql injection where the data are extracted one byte at a time by using LEFT, RIGHT, REVERSE, SUBSTRING, STUFF etc. Hence I would accomplish what I am after.
    – Zach
    Commented Mar 29, 2016 at 6:50

2 Answers 2

2

You cannot restrict permissions to LEFT built-in function. You can control permissions that are listed by the output of this command:

SELECT *
FROM fn_builtin_permissions(default)
5
  • Hi Serge, understood. Is there any other way to restrict execution of the LEFT built-in procedure ?
    – Zach
    Commented Mar 29, 2016 at 6:45
  • I am not aware how it would be possible. But even if you managed to succeed, it's not fool-proof. For example, a user could say SELECT RIGHT(REVERSE('abcde'), 3), or SELECT SUBSTRING('abcde', 1, 3). Even prohibiting access to all String Functions won't be enough. For example, a user could do this SELECT CONVERT(VARCHAR(100), CONVERT(VARBINARY(3), 'abcdef')). What are you trying to achieve?
    – Serge
    Commented Mar 29, 2016 at 7:40
  • I want to prevent SQL injecting the database through an insecurely developed application. A common SQL injection strategy is blind sql injection where the data are extracted one byte at a time by using mainly SUBSTRING but also LEFT, RIGHT, REVERSE, STUFF etc. I just used LEFT as an example.
    – Zach
    Commented Mar 29, 2016 at 8:59
  • @Zach, a secure database design cannot protect against poorly/insecurely developed applications. Simply put, data available to such applications will be vulnerable. You can improve security using authentication/authorisation and limiting access to particular operations/objects. You may convey to the application developers the importance of good/secure design and request/require that they interact with the DBMS in a secure way (eg ask them to read stackoverflow.com/a/601309/1110897)
    – Serge
    Commented Mar 29, 2016 at 23:57
  • you are correct, as is @daniel-hutmacher below and I completely understand it can't substitute correct development practices. However there are things you control and there are things you do not control. The only way devs pay attention is after the SQL injection exploitation, anything before that and you are treated as if you are calling their baby ugly and believe me I have been in my share of discussions like that. What I am trying to convey is that I am thinking defensively and operating under an assumption that stuff happens so what can we do to minimise impact.
    – Zach
    Commented Mar 30, 2016 at 19:41
1

Like others have said before me, there is no way to restrict the use of built-in functions in SQL Server, and there really would be no point to it. Instead, good security practices dictate restricting access to data rather than programmatic functionality.

In essence, if you can't use RIGHT() in SQL Server, you could extract the data and perform RIGHT() in Excel and achieve the same results.

An app will be used to access the data stored on the database. I want to prevent malicious application of SQL injecting the database.

SQL injection relies on readily accessible data. You can restrict this by only allowing data access through a controlled form, like views or stored procedures. This would mean not giving the user any access to the raw tables, but rather just EXECUTE access to stored procedures or similar. For what it's worth, I wrote a series of blog posts that cover different aspects of permissions, execution context and ownership chaining. See (1), (2), (3) and (4).

But first and foremost, you need to protect your app against SQL injection.

This cannot be overstated, and other security measures can only augment it, never replace it.

2
  • There is a HUGE precondition on the above and that is having control on the application itself. So it is not my app, it is someone's app. And security in depth and being proactive never hurt anybody.
    – Zach
    Commented Mar 29, 2016 at 9:13
  • Sorry to sound categorical, but if there is any risk of SQL injection at all, you need to bring that up with the developers of the app. There really is no other way to deal with that risk, apart from the measures I've described in my answer (which will probably also require app modifications). Commented Mar 29, 2016 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.