locked
Table has been denied but still can insert rows via the IIS web-server.. RRS feed

  • Question

  • Hi All,

    I came through an unusual situation yesterday. I denied (Deny) a user from select , update and insert on a table in a particular database although the  user was 'db_owner' of that database, I checked it via query window (SSMS) and got permission denied error while selecting or updating record on that table. 

    However the same user was able to access and insert data in the database through the website i.e through IIS web server.

    Is this a kind security lapse in SQL server?

    We are using windows server 2012R2 and SQL server 2014


    aa

    Monday, January 18, 2016 10:40 AM

Answers

  • To be clear, you can DENY members of the DB_OWNER role. But they are database-level administrators, so applying object-level permissions is not secure. It looks like a security measure, but isn't really. You should avoid such "fake security".  Even if you understand the limitations, whoever comes after you will either think it's stronger than it is, or think that you didn't understand the limitations of that approach.

    If you want to grant a user the ability to INSERT, UPDATE, and DELETE except for a few tables, DENY is a good choice.  But you should really never use the old built-in roles any more.  Just solve the problem with grants.

    Remember you can GRANT at the schema level, then DENY at the object level to make exclusions.

    David


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

    Tuesday, January 19, 2016 3:06 PM

All replies

  • However the same user was able to access and insert data in the database through the website i.e through IIS web server.

    Hello,

    And which authentication is the IIS web application using to logon to SQL Server? Does it use the Service account or does it impersonate the current user or is an other account used?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, January 18, 2016 10:44 AM
  • The website uses a SQL login to connect the database.

    aa

    Monday, January 18, 2016 10:47 AM
  • The website uses a SQL login to connect the database.

    And that was the login that you denied access to that table?

    Monday, January 18, 2016 10:41 PM
  • It's really irrelevant, since DENY on a member of DB_OWNER is ineffective, as that user can simply impersonate DBO.

    David


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

    Monday, January 18, 2016 11:38 PM
  • deny on dbo (or sa on that matter) will take no effect, you should remove the user from db owner and only allow it to have read and write access. That should be the better approach given its allow access from IIS.

    Tuesday, January 19, 2016 1:36 AM
  • To be clear, you can DENY members of the DB_OWNER role. But they are database-level administrators, so applying object-level permissions is not secure. It looks like a security measure, but isn't really. You should avoid such "fake security".  Even if you understand the limitations, whoever comes after you will either think it's stronger than it is, or think that you didn't understand the limitations of that approach.

    If you want to grant a user the ability to INSERT, UPDATE, and DELETE except for a few tables, DENY is a good choice.  But you should really never use the old built-in roles any more.  Just solve the problem with grants.

    Remember you can GRANT at the schema level, then DENY at the object level to make exclusions.

    David


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

    Tuesday, January 19, 2016 3:06 PM