In general, there are several situations that require you to set the database to read-only:
1. Insert,update,delete Trigger
2. Check constraints and Delete triggers
3. Set the database to read-only
4. Put the table in a read-only filegroup
5. Reject Object-level permissions
6. Create a View
Before you begin, create a database and table as an example:
Copy Code code as follows:
Create DATABASE MyDB
CREATE TABLE Tblevents
(
ID int,
LogEvent varchar (1000)
)
INSERT INTO tblevents
VALUES (1, ' Password Changed '), (2, ' User dropped '), (3, ' Finance Data Changed ')
Nsert/update/delete triggers:
Note that the INSTEADOF trigger is used here because if you use after trigger, requesting a lock when executing delete, UPDATE, and INSERT statements will have a performance impact on both the write transaction log and the rollback operation
Copy Code code as follows:
CREATE TRIGGER trreadonly_tblevents on tblevents
INSTEAD of INSERT,
UPDATE,
DELETE
As
BEGIN
RAISERROR (' tblevents table is read only. ', 16, 1)
ROLLBACK TRANSACTION
End
When the user executes insert/update/delete, the following error is prompted:
MSG 50000, level, State 1, Procedure trreadonly_tblevents, line 7tblEvents The table is read only. MSG 3609, level, State 1, line 1The transaction ended in the trigger. The batch has been aborted.
Using Check constraints and delete triggers:
Adding a Check constraint "1=0" to the table now means always failing. It prevents you from performing an insert or delete operation on any line.
First, disable the trigger created in the previous step: Disable Trigger trreadonly_tblevents on tblevents and then add the constraint: ALTER TABLE tblevents with NOCHECK add CONSTRAINT chk_read_only_tblevent CHECK (1 = 0) After execution, the following error message will be prompted whether you execute any of the insert/update statements:
MSG 547, level, state 0, line 1
The UPDATE statement conflicted with the Checkconstraint "Chk_read_only_tblevent". The conflict occurred indatabase "MyDB", table "Dbo.tblevents".
The statement has been terminated.
However, the constraint does not affect the delete operation, and to this end, a DDL trigger is created:
Copy Code code as follows:
CREATE TRIGGER trreadonlydel_tblevents on tblevents
INSTEAD of
DELETE
As
BEGIN
RAISERROR (' tblevents table is read only. ', 16, 1)
ROLLBACK TRANSACTION
End
Set the database to read-only:
You can set the database to be read-only, thus prohibiting the ddl/dml operation of the entire database. You can use the following statement:
Copy Code code as follows:
Use [master]
Go
ALTER DATABASE [MyDB] SET read_only with no_wait
Go
To place a table in a read-only filegroup:
You can create a table in a read-only file group:
Copy Code code as follows:
Use [master]
Go
ALTER DATABASE [MyDB] ADD FILEGROUP [Read_only_tbls]
Go
ALTER DATABASE [MyDB] ADD FILE (NAME = n ' mydb_readonly_tables ', FILENAME = N ' C:\JSPACE\myDBReadOnly.ndf ', SIZE = 2048KB , filegrowth = 1024KB) to FILEGROUP [Read_only_tbls]
Go
DROP Table Tblevents
CREATE TABLE Tblevents
(
ID int,
LogEvent varchar (1000)
)
On [Read_only_tbls]
ALTER DATABASE [MyDB] MODIFY FILEGROUP [Read_only_tbls] READONLY
Any DML operations on the table are rejected, and the following error message is returned:
MSG 652, level, State 1, line 1
The index "" for Table "Dbo.tblevents" (Rowsetid 72057594038845440) resides on a read-only filegroup ("Read_only_tbls"), W Hich cannot be modified.
Deny Object level permissions
You can control user rights through the DCL command, but this step cannot restrict advanced rights users such as System Admin,databaseowner:
Copy Code code as follows:
DENY INSERT, UPDATE, DELETE on tblevents to Jugal
DENY INSERT, UPDATE, DELETE on tblevents to public
Create a View
To override the direct Access table, you can use the view:
Copy Code code as follows:
CREATE VIEW Vwtblevents
As
Select ID, LogEvent from tblevents
UNION ALL
Select 0, ' 0 ' where 1=0
In this view, the union is used only when you are sure there is a corresponding number of columns. In this example, the table has two columns, so two output columns are used. At the same time, you should also ensure that the data types are consistent.
When a user tries to manipulate data through insert/update/delete, they get the following error message:
MSG 4406, level, State 1, line 1Update or insert of view or function ' VwtblEvents1 ' failed because it contains a derive D or Constant field. MSG 4426, level, State 1, line 1View ' vwtblEvents1 ' are not updatable because the definition contains a unionoperator.
Last step:
Verify that these steps are necessary to set the table to read-only.
If a table is always read-only, then you should put it in a read-only filegroup.
