locked
ways of making just a table readonly RRS feed

  • Question

  • Hi we run 2016 enterprise.

    I'm reading the tricks at https://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/ for making a table readonly and tried the view trick where a union is introduced with predicate 1=0.  But my delete statement worked (it wasn't supposed to) so I've decided to post here.

    My next choice from that list would be triggers that would prevent crud ops. 

    I don't want to make the db readonly.  And I don't want to introduce a filegroup that is readonly.  And from what I understand using denies etc or anything security based isn't going to happen.

    Are there any other tricks for making just a table readonly?

    USE [formsdn]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[testViewBlocksCRUD](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[afield] [varchar](250) NOT NULL,
    	
     CONSTRAINT [PK_testViewBlocksCRUD] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    --insert [dbo].[testViewBlocksCRUD] select 'a test'
    select * from [dbo].[testViewBlocksCRUD]
    
    create view vw_testViewBlocksCRUD as
    select * from testViewBlocksCRUD
    union all 
    select 0,'' where 1=0
    
    delete from testViewBlocksCRUD
    

    Thursday, August 16, 2018 5:49 PM

Answers

  • The view part didn't work because you delete from the table, not the view (see that article a bit more closely and you'll see that is assumes that you can do the delete on the view and not directly on the table).

    Why do you say that regular permissions won't work? You say " And from what I understand using denies etc or anything security based isn't going to happen." Can you elaborate?

    How about a trigger? That can be either an INSETAD OF trigger that does nothing, or an AFTER trigger that does rollback with an error message (whatever your preference is).


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Proposed as answer by Shanky_621MVP Friday, August 17, 2018 7:13 AM
    • Marked as answer by db042190 Monday, August 20, 2018 9:37 PM
    Thursday, August 16, 2018 5:56 PM
  • Creating a trigger is the simplest thing

    CREATE TRIGGER trg_BlockCRUD ON [dbo].[testViewBlocksCRUD]
    INSTEAD OF INSERT, UPDATE, DELETE
    AS
    BEGIN
        RAISERROR('ERROR: Changes are not allowed to table!',16,1);
    END;
    
    
    

    • Proposed as answer by Shanky_621MVP Friday, August 17, 2018 7:13 AM
    • Marked as answer by db042190 Monday, August 20, 2018 9:37 PM
    Thursday, August 16, 2018 6:27 PM
  • You should be able to create a read only FileGroup and move one or more tables into that FileGroup, making them read only.

    How to make a table read only in SQL Server

    Give that a shot and see if it works for you.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Thursday, August 16, 2018 7:12 PM
    • Proposed as answer by Shanky_621MVP Friday, August 17, 2018 7:14 AM
    • Marked as answer by db042190 Monday, August 20, 2018 9:37 PM
    Thursday, August 16, 2018 7:11 PM
  • I think DENY could work for you, as long your users are not db_owner (object owner of the table) or sysdba as described in this article

    DENY (T-SQL) https://docs.microsoft.com/en-us/sql/t-sql/statements/deny-transact-sql?view=sql-server-2017

    Tested the following and it worked

    -- login as db_owner

    create table test
    (
      field1 varchar(10)
    )
    
    insert into test values ('test') -- works as expected


    create new user "testdba" with db_datareader / db_datawrite permissions

    -- Deny as db_owner access to testdba to the "test" table
    DENY INSERT, UPDATE, DELETE ON test TO testdba

    login as "testdba" and try insert data into test table

    insert into test values ('foo')
    
    Msg 229, Level 14, State 5, Line 1
    The INSERT permission was denied on the object 'test', database 'TestDb', schema 'dbo'.

    If a not "db_owner" login wouldn't be accepted by your dba (we are not allowing our users to be db_owner nor sysdba due to company security rules and best practices ;) ) , I think an instead of trigger is your only chance.

    Found inspiration on this article

    Different ways to make a table read only in a SQL Server database

    https://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/


    • Edited by K.Verwold Thursday, August 16, 2018 9:09 PM
    • Marked as answer by db042190 Monday, August 20, 2018 9:38 PM
    Thursday, August 16, 2018 9:03 PM
  • Hi db042190,

     

    From you description, my understanding is that you want to make a table read only. If anything is misunderstood, please tell me.

     

    As mentioned by Tibor, you create a view named vw_testViewBlocksCRUD and contents defined by the query are same as the testViewBlocksCRUD table. All DML operations based on the view are prevented. However the DML operations on the table will not be affected.

     

    Based on my test, apart from the Insert/Update/Delete trigger, you can try to using Check Constraint and Delete Trigger. You can add the Check Constraint like the following code, and the check constraint don't prevent the DELETE operation. Then you can add a trigger to prevent the delete operation. The following is my testing code:

    create table Testtable_
    (
      id int,
      name varchar(10)
    )
     
    insert into Testtable_ values (1, 'test')
    
    alter table Testtable_ WITH NOCHECK ADD CONSTRAINT chk_read_only_testtable_ CHECK( 1 = 0 )
    
    create
    trigger trReadOnlyDel_testtable_ ON testtable_
    
        instead of delete
    
    as
    
    begin
    
        RAISERROR( 'Testtable_ table is read
    only.', 16, 1 )
    
        rollback transaction
    
    end
    
    delete from Testtable_ where id=1
    
    insert into Testtable_ values (1, 'test')
    
    update Testtable_ SET id = 2 WHERE name = 'test'

    And when executing the Insert/Update/Delete operation, there will occur the errors like the screenshot.

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Friday, August 17, 2018 9:23 AM

All replies

  • The view part didn't work because you delete from the table, not the view (see that article a bit more closely and you'll see that is assumes that you can do the delete on the view and not directly on the table).

    Why do you say that regular permissions won't work? You say " And from what I understand using denies etc or anything security based isn't going to happen." Can you elaborate?

    How about a trigger? That can be either an INSETAD OF trigger that does nothing, or an AFTER trigger that does rollback with an error message (whatever your preference is).


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Proposed as answer by Shanky_621MVP Friday, August 17, 2018 7:13 AM
    • Marked as answer by db042190 Monday, August 20, 2018 9:37 PM
    Thursday, August 16, 2018 5:56 PM
  • Creating a trigger is the simplest thing

    CREATE TRIGGER trg_BlockCRUD ON [dbo].[testViewBlocksCRUD]
    INSTEAD OF INSERT, UPDATE, DELETE
    AS
    BEGIN
        RAISERROR('ERROR: Changes are not allowed to table!',16,1);
    END;
    
    
    

    • Proposed as answer by Shanky_621MVP Friday, August 17, 2018 7:13 AM
    • Marked as answer by db042190 Monday, August 20, 2018 9:37 PM
    Thursday, August 16, 2018 6:27 PM
  • You should be able to create a read only FileGroup and move one or more tables into that FileGroup, making them read only.

    How to make a table read only in SQL Server

    Give that a shot and see if it works for you.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Thursday, August 16, 2018 7:12 PM
    • Proposed as answer by Shanky_621MVP Friday, August 17, 2018 7:14 AM
    • Marked as answer by db042190 Monday, August 20, 2018 9:37 PM
    Thursday, August 16, 2018 7:11 PM
  • i'm relying a bit on my dba's advice that we don't want to go down the security path.  Something about roles, lots of work etc etc.

    yes, the trigger is looking like the easiest way to go.

     
    Thursday, August 16, 2018 8:00 PM
  • thx, no read only file group.
    Thursday, August 16, 2018 8:00 PM
  • I think DENY could work for you, as long your users are not db_owner (object owner of the table) or sysdba as described in this article

    DENY (T-SQL) https://docs.microsoft.com/en-us/sql/t-sql/statements/deny-transact-sql?view=sql-server-2017

    Tested the following and it worked

    -- login as db_owner

    create table test
    (
      field1 varchar(10)
    )
    
    insert into test values ('test') -- works as expected


    create new user "testdba" with db_datareader / db_datawrite permissions

    -- Deny as db_owner access to testdba to the "test" table
    DENY INSERT, UPDATE, DELETE ON test TO testdba

    login as "testdba" and try insert data into test table

    insert into test values ('foo')
    
    Msg 229, Level 14, State 5, Line 1
    The INSERT permission was denied on the object 'test', database 'TestDb', schema 'dbo'.

    If a not "db_owner" login wouldn't be accepted by your dba (we are not allowing our users to be db_owner nor sysdba due to company security rules and best practices ;) ) , I think an instead of trigger is your only chance.

    Found inspiration on this article

    Different ways to make a table read only in a SQL Server database

    https://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/


    • Edited by K.Verwold Thursday, August 16, 2018 9:09 PM
    • Marked as answer by db042190 Monday, August 20, 2018 9:38 PM
    Thursday, August 16, 2018 9:03 PM
  • Hi db042190,

     

    From you description, my understanding is that you want to make a table read only. If anything is misunderstood, please tell me.

     

    As mentioned by Tibor, you create a view named vw_testViewBlocksCRUD and contents defined by the query are same as the testViewBlocksCRUD table. All DML operations based on the view are prevented. However the DML operations on the table will not be affected.

     

    Based on my test, apart from the Insert/Update/Delete trigger, you can try to using Check Constraint and Delete Trigger. You can add the Check Constraint like the following code, and the check constraint don't prevent the DELETE operation. Then you can add a trigger to prevent the delete operation. The following is my testing code:

    create table Testtable_
    (
      id int,
      name varchar(10)
    )
     
    insert into Testtable_ values (1, 'test')
    
    alter table Testtable_ WITH NOCHECK ADD CONSTRAINT chk_read_only_testtable_ CHECK( 1 = 0 )
    
    create
    trigger trReadOnlyDel_testtable_ ON testtable_
    
        instead of delete
    
    as
    
    begin
    
        RAISERROR( 'Testtable_ table is read
    only.', 16, 1 )
    
        rollback transaction
    
    end
    
    delete from Testtable_ where id=1
    
    insert into Testtable_ values (1, 'test')
    
    update Testtable_ SET id = 2 WHERE name = 'test'

    And when executing the Insert/Update/Delete operation, there will occur the errors like the screenshot.

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Friday, August 17, 2018 9:23 AM