Read Only Tables
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/readonlytables.asp
-
Hi
What about to add a filegroup, make it read-only and add all tables that you want only to read from?
Best Regards
Andy
-
A way to use triggers without the need to drop and recreate them would be to use SET CONTEXT_INFO. I found this out only a few months ago whilst reading some articles in this forum.
-
Hi steve..
Good information...
In addition to ROLLBACK TRANSACTION, We can also use RAISEERROR for displaying the message to the user...
Thnx.
-
In my opinion , the better resolution for the as u called "The Hard Way", is to use trigger if type INSTEAD OF DELETE,UPDATE,INSERT and put a Raiserror with some message.
In this way u fon't need to rollback the transaction, cause in fact it no begun. I think it's decrease some overhead, cause data not logged in transaction log
. . .
-
Here's another way for those that like the "tricky" solutions... create a view using a construct that doesn't allow updates and expose only that to your users:
CREATE TABLE dbo.testRead(
i int NOT NULL PRIMARY KEY,
j int NOT NULL
)
GO
CREATE VIEW dbo.v_testRead
WITH SCHEMABINDING
AS
SELECT i, j
FROM dbo.testRead
UNION ALL
SELECT CAST(NULL AS int), CAST(NULL as int)
WHERE 1 = 0
GO
INSERT INTO dbo.testRead(i, j)
SELECT 1, 3
GO
SELECT *
FROM dbo.v_testRead
GO
INSERT INTO dbo.v_testRead(i, j)
SELECT 2, 4
GO
SELECT *
FROM dbo.v_testRead
GO
DROP VIEW dbo.v_testRead
GO
DROP TABLE dbo.testRead
GO
TroyK
-
The approach we take is to move the the read-only tables from the primary databse (D1) and put them into a different database (D2) and expose them in D1 via a view. This permits us to use the db_datawriter role on D1. Everybody gets db_datareader in D2.
This is also helpful for situations where certain tables are updated by batch processes (which is usually our principal reason for database splitting). This allows us to use different data protection techniques in the front-end (or transactional databases) and the back-end (batch) database. Typically, we set the front-end database to full recovery mode and the back-end to "simple."
-
Agree with JB. INSTEAD OF Triggers are the first that I would use for the Hard Way and I have seen code using them. They can not be implemented in one particular case when Cascade action is specified for the key field in the relationship, but in other cases I would use them.
For the easy way I agree that db_datareader role or its equivalent with Select permissions for tables is fine.
I also saw a couple of implementations similar to what dhathewa@isd.net is saying with read-only tables in a different database.
Regards,Yelena Varsha
-
A really easy way is to just use a View with select only rights granted. I don't like giving people rights to tables at all. Granting database object permissons only to views or stored procedures may seem limiting; however, it ensures data integrity and prevents many other mishaps. This approach reduces overall cost of ownership. In a normalized database, looking at a single table provides foreign key values which are "foreign code" to the reader. Views provide a read-only picture complete with the foreign table values.
-
I have about 4 standard trigger templates for tables that disallows updates and/or deletes; I use them all the time on every table that has data that should not change once it has been written. I view this is basic for good database design, since we need to prohibit data changes in select data sets on the database level. For example, reference data--master lists--such as invoice states (statuses), countries, provinces, states, cities should not be changing often, if ever. Publications are another thing that should not change, since once something has been published its published name, publication date, author, and the like will never change. I have a standard template for disallowing certain columns to change, with table lookups to have "row change rules" applied to a table's column.
If you are good with triggers, you can provide incredible control on even row-by-row data read-only attributes. Build your templates and make them reusable and then you can put them quickly and easily wherever you need them. I can build a standard master-list lookp values table in 1.5 hours, with the ultimate data integrity and security built in--all because I have a template that covers all parts of the table build including triggers, indexes, extended properties, full-text indexes. et cetera.
-
Please, please, please get a copy editor. Poor grammar and typos missed by the spellchecker reduce the utility of otherwise useful articles.
-
Inside the trigger you can check
IF SUSER_SNAME() NOT IN ( ... 
ROLLBACK TRAN
The list for NOT IN may be hardcoded, may be read from another database, etc.
_____________
Code for TallyGenerator -
I have implemented 2 tracking systems, which track changes of drillhole information in a mining-related data management system. The 2 systems share some code, but operate independantly. Record tracking, when installed and enabled saves all deleted and updated records in its original form. Object tracking records which drillhole (the parent of all other information) was affected and by whom. The administrator can set a drillhole to read-only. In this case no user can make any changes at any level in the table hierarchy to this drillhole. The triggers are smart enough not to track cascading transaction. Object tracking happens on the point of entry only. Tracking comes free with every licence sold. Clients particularly in remote areas use object tracking. Record tracking has a larger performance overhead. When the data model changes the tracking scripts are re-run to automatically pick up the changes.
Cheers,
Win
-
For basic security, all table access should be through stored procedures anyway so simply deny access to the relevant procedures. Nobody has direct access to the tables.
-
I have been rebuilding our HR database and I need a way to keep legacy information (i.e. Staff, Office Locations, etc...). I decided that a trigger was the best way to keep anyone (including me) from "accidently" deleting information.
CREATE TRIGGER tgrOfficeDelete
ON dbo.tblOffice
FOR DELETE
AS
BEGIN
RAISERROR('Offices cannot be deleted! This is to preserve
historical data.', 16, 1)
ROLLBACK TRANSACTION
END
I didn't think about taking it to the next level and use triggers for creating a "Read-Only" table. I have been using rolls to controll access.
Another thing I have been doing is creating XML files with information in them that doesn't change that often; benefit summaries, state lists, EEOC values, etc., etc. My SQL Server rarely reads the XML files (the 1st of the month at 2:00 AM) so I'm not too concerened with processor resources being taken up by them. Also, my WebApps are using those same XML files for creating DropDownList and presenting "static" information to my users.
Oh, the options for Read-Only Tables...
Imagination is more important than knowledge.– Albert Einstein
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply