locked
Deny db_name(), @@Version and related RRS feed

  • Question

  • Hello!

    Please, how do I deny SELECT on DB_NAME(), @@VERSION, SYSTEM_USER(), @@SERVERNAME, IS_SRVROLEMEMBER and more to an SQL login?

    Thanks!


    Alex Elias

    Thursday, July 17, 2014 6:27 PM

Answers

  • The only way to prevent SQL injection is to write the application properly. That is, use only parameterised commands and never build command strings by string concatenation.

    You can mitigate SQL injection by having applications running with minimum permission.

    But blocking access to SYSTEM_USER is not going to take you anywhere.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, July 18, 2014 8:59 AM
  • The answer is that you don't and that you shouldn't. These system functions defaults with permission to public for a reason. A lot of code would break, if these functions were unavailable.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, July 17, 2014 10:16 PM
  • The login with bare minimum privs can executed your list. By default the logins has public database role.

    Go through the below link which explains the default permission of Public role

    http://blogs.msdn.com/b/sqlserverfaq/archive/2010/08/31/inf-sql-security-restricting-access-to-public-on-server-database-objects-its-implications-and-ownership-chains.aspx

    Remove Public and Guest Permissions

    http://blogs.technet.com/b/fort_sql/archive/2010/02/04/remove-public-and-guest-permissions.aspx

    Note:- Read and understand the above link and don't try this directly on PROD.

    --Prashanth


    • Edited by Prashanth Jayaram Thursday, July 17, 2014 7:53 PM
    • Proposed as answer by SQL DBA1 Thursday, July 17, 2014 8:27 PM
    • Marked as answer by tracycai Tuesday, July 22, 2014 2:18 AM
    Thursday, July 17, 2014 7:48 PM

All replies

  • The login with bare minimum privs can executed your list. By default the logins has public database role.

    Go through the below link which explains the default permission of Public role

    http://blogs.msdn.com/b/sqlserverfaq/archive/2010/08/31/inf-sql-security-restricting-access-to-public-on-server-database-objects-its-implications-and-ownership-chains.aspx

    Remove Public and Guest Permissions

    http://blogs.technet.com/b/fort_sql/archive/2010/02/04/remove-public-and-guest-permissions.aspx

    Note:- Read and understand the above link and don't try this directly on PROD.

    --Prashanth


    • Edited by Prashanth Jayaram Thursday, July 17, 2014 7:53 PM
    • Proposed as answer by SQL DBA1 Thursday, July 17, 2014 8:27 PM
    • Marked as answer by tracycai Tuesday, July 22, 2014 2:18 AM
    Thursday, July 17, 2014 7:48 PM
  • Thanks!

    Alex Elias

    Thursday, July 17, 2014 8:11 PM
  • Did you try this? If you revoke public and disable guest then it just means stops the user from logging to SQL Server. As long as the login has access to SQL you can still do all of this. Was wondering if we can just block these in any other ways!!

    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Thursday, July 17, 2014 9:00 PM
  • I agree with you, I was also thinking in the same way, Alex might want to resist all users to do it but not the SQL authorizes users. Authorized users anyway will be able to execute those by default as those are built for the purpose. I dont think there is such restiction.

    Hi Alex, Can you please provide your exact requirement?


    Swapna

    Thursday, July 17, 2014 9:06 PM
  • one of my developer have access to read storeprocedures so he can connect through SSMS. He is able to get the SQL server name and version. It means that every one who can connect the server will be able to get these data. It is not Login level to resrict rather it is Server level information.


    Swapna

    Thursday, July 17, 2014 10:05 PM
  • The answer is that you don't and that you shouldn't. These system functions defaults with permission to public for a reason. A lot of code would break, if these functions were unavailable.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, July 17, 2014 10:16 PM
  • Hello! Thanks for answer.

    My problem is that I need to prevent sql injection from many ASP and ASP.NET web pages and I need to block a scan by SqlMap application. 

    Part of the scan execute this commands from a webpage by post and success. I need to make this scan fail.


    Alex Elias

    Friday, July 18, 2014 1:43 AM
  • Hello! Thanks for answer.

    I agree, but I need to prevent SQL Injection by any web pages ina a web application and a SqlMap scan.

    If the user stops logging, the web application stops to, and I don´t do this.

    I need to restrict maximum information about the instance of sql server.


    Alex Elias

    Friday, July 18, 2014 1:50 AM
  • Thanks!

    Alex Elias

    Friday, July 18, 2014 1:52 AM
  • The only way to prevent SQL injection is to write the application properly. That is, use only parameterised commands and never build command strings by string concatenation.

    You can mitigate SQL injection by having applications running with minimum permission.

    But blocking access to SYSTEM_USER is not going to take you anywhere.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, July 18, 2014 8:59 AM
  • I fully agree with what Erland says: you really should rather secure your code than trying to rely on the auto-detect feature of sqlmap

    hopefully you are aware, that a hacker could still just specify all variations to test for

    and... that there are dozens of more SQLInjection tools each behaving a little different

    and.. one can still just hack manually - which is why you should maybe ask a penetration tester and don't just rely on a tool (and a true pen tester won't do that either)


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Saturday, July 19, 2014 10:36 AM