locked
Security Policy has problem while insertion, Row level; Security RRS feed

  • Question

  • Hi Experts,

    I have created Security policy and has FILETR PREDICATES, I have bound this with table XX this table has security column called System user. Its like the login user can see only his data from that table. Its working fine for the Update, delete, select, but in case of insert I found -- If no record is there for that user then its working fine . but if that user related data is already there then  its allowing to insert  another user data also.  for example If I login as User1 I am able to insert: insert into XX (id , SystemUser) values(1,'User2'), that should not.

    Thanks in advance... 


    Regards Vikas Pathak


    Friday, September 4, 2015 11:28 AM

Answers

  • If I understand your situation correctly, you are seeing the currently designed behavior. Filter predicates allow you to enter a row that does not meet your filter. You need a blocking predicate to prevent an insert that does not meet your predicate. From Books Online https://msdn.microsoft.com/en-us/library/dn765131.aspx:

    Filter predicates are applied while reading data from the base table, and it affects all get operations: SELECT, DELETE (i.e. user cannot delete rows that are filtered), and UPDATE (i.e. user cannot update rows that are filtered, although it is possible to update rows in such way that they will be subsequently filtered). Blocking predicates are not available in this version of RLS, but equivalent functionality (i.e. user cannot INSERT or UPDATE rows such that they will subsequently be filtered) can be implemented using check constraints or triggers.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, September 8, 2015 3:39 PM
  • As Rick mentioned, the behavior you are seeing is by design. We have a blog article that may be useful to get started with blocking behavior on INSERT operations using check constraints:

     http://blogs.msdn.com/b/sqlsecurity/archive/2015/03/23/row-level-security-blocking-unauthorized-inserts.aspx

    BTW. We have received feedback regarding this limitation before, and while we cannot promise timelines, let me asure you that we are listening to your feedback and we are working on a better solution.

    Thanks,

    -Raul Garcia

      SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, September 8, 2015 4:59 PM

All replies

  • Hi Vikas

    Try Setting Default values for SystemUser column as 

    ALTER TABLE YourTable ADD CONSTRAINT DF_YourTable DEFAULT USER FOR SystemUser

    Then write an trigger on SystemUser column which will check and  roles back any inappropriate entry.

    More details: https://msdn.microsoft.com/en-IN/library/ms189799.aspx

    Monday, September 7, 2015 6:26 AM
  • Are we talking about SQL Server 2016 row-level security? Or something else? If this is just a generic question on how to prohibit something else but your own username when you do an INSERT, then just create an INSERT trigger on the table to check/set it.

    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, September 7, 2015 11:55 AM
  • Thank you TiborK for interest. Here I am talking about SQL Server 2016 row-level security.

    Regards Vikas Pathak

    Tuesday, September 8, 2015 9:06 AM
  • thank you Sarang for interest,

     Here I am talking about SQL Server 2014 Row level security. I can not create trigger as I am implementing this for 500 table. Its not good to have these many triggers


    Regards Vikas Pathak


    Tuesday, September 8, 2015 9:11 AM
  • If I understand your situation correctly, you are seeing the currently designed behavior. Filter predicates allow you to enter a row that does not meet your filter. You need a blocking predicate to prevent an insert that does not meet your predicate. From Books Online https://msdn.microsoft.com/en-us/library/dn765131.aspx:

    Filter predicates are applied while reading data from the base table, and it affects all get operations: SELECT, DELETE (i.e. user cannot delete rows that are filtered), and UPDATE (i.e. user cannot update rows that are filtered, although it is possible to update rows in such way that they will be subsequently filtered). Blocking predicates are not available in this version of RLS, but equivalent functionality (i.e. user cannot INSERT or UPDATE rows such that they will subsequently be filtered) can be implemented using check constraints or triggers.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, September 8, 2015 3:39 PM
  • As Rick mentioned, the behavior you are seeing is by design. We have a blog article that may be useful to get started with blocking behavior on INSERT operations using check constraints:

     http://blogs.msdn.com/b/sqlsecurity/archive/2015/03/23/row-level-security-blocking-unauthorized-inserts.aspx

    BTW. We have received feedback regarding this limitation before, and while we cannot promise timelines, let me asure you that we are listening to your feedback and we are working on a better solution.

    Thanks,

    -Raul Garcia

      SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, September 8, 2015 4:59 PM
  • Thank you very much Raul.  Its rely helped me . answered my question.

    Also I brought new Item from there CipherDB.


    Regards Vikas Pathak


    Friday, September 11, 2015 8:51 AM