Solved

SQL Table Column

Posted on 2013-01-25
24
229 Views
Last Modified: 2013-01-30
Is there a way to set the actual column in a QL Table so no record  edit or delete can take place?
0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 4
  • +1
24 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 38819162
You could use a COMPUTED PERSISTENT column that cannnot be used in INSERT/UPDATE and write a trigger on that table so the ROW cannot be deleted:

http://msdn.microsoft.com/en-us/library/ms191250(v=sql.100).aspx
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38819474
Are you trying to restrict one column for every row?

Or the entire row ("record") containing a column with a specific value?

Either way, you can use DENY to restrict it.  Only "sysadmin"s would then be able to alter/delete it (you can't restrict sysadmins from any table/column).
0
 

Author Comment

by:lrbrister
ID: 38819519
ScottPletcher
Just came from planning meeting.
What they want is that once a [Reconciled] Bit field is set to 1, not allow the data to be changed nor the ropw deleted.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38819594
Hmm, OK, I spoke too soon before, a simple DENY won't handle that.

Easiest would likely be a trigger that rolls back the all changes if any row had that bit on.

I'll think about it and see if I can come up w/ any other option(s).
0
 

Author Comment

by:lrbrister
ID: 38819615
Scott...thanks.
This one is a bit perplexing...but interesting
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38819809
>>What they want is that once a [Reconciled] Bit field is set to 1, not allow the data to be changed nor the ropw deleted<<

Not to sound rude by any means, but why do you need to reinvent the wheel here? This is clearly an application rule and a such, it should be treated at the application level. Why do you want to mess around at the database level?

As Scott mentioned, you could implement some trigger functionality there, but still... I wouldn't go that way personally.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38819918
I would 100% implement it at the db level to make sure the restriction is fully enforced.

It's too easy to get around a "block" at the app level.  Or even for that condition to accidentally be left out of an app once.

A failsafe check in the app also wouldn't hurt, but I personally wouldn't rely on that since a more insured method to enforce the rule is available.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38820013
>>I would 100% implement it at the db level to make sure the restriction is fully enforced.<<

We will have to agree to disagree here. In my opinion, it's just adding overhead to the database. The restriction should be implemented at the application level. Of course we are assuming we have good database access control in place so that the application controls are not bypassed. But we are entering another territory there.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38820040
I've never been any place that truly, fully blocked developers from changing prod data.

Of course it adds overhead to the database -- but so does writing the data to it to begin with.  If it's fundamental to what needs done, the overhead must be accepted.
0
 

Author Comment

by:lrbrister
ID: 38820064
ralmad
I don't have control over who gets access to what.
Neither does the owner apparently
So there are several folks that have access to the SQL Database and can run queries.
The've already deleted records before.

So I'm looking to minimize that as much as I personally can
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38820107
Sadly, perhaps, that's a normal state of affairs.  As I said, I've NEVER seen a place with genuinely locked-down controls on production data.  [Interestingly, at one place I worked, order history was indeed 100% protected and was absolutely unchangeable by anyone.]
0
 

Author Comment

by:lrbrister
ID: 38820142
Scott,
 Thanks.  You saod you'd think about this a bit with that trigget?

In this table it would be when the [reconciled] column is set to 1 (True)

CREATE TABLE [dbo].[ClientVisitTracking](
	[id] [int] IDENTITY(1000,1) NOT NULL,
	[ClientID] [int] NULL,
	[ContactID] [int] NULL,
	[createdBy] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[createdDate] [datetime] NULL CONSTRAINT [DF_ClientVisitTracking_createdDate]  DEFAULT (getdate()),
	[reconciled] [bit] NULL CONSTRAINT [DF_ClientVisitTracking_reconciled]  DEFAULT ((0)),
 CONSTRAINT [PK_ClientVisitTracking] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38820222
CREATE TRIGGER dbo.ClientVisitTracking__TRG_UPD
ON dbo.ClientVisitTracking
AFTER UPDATE
AS
IF EXISTS(
    SELECT 1
    FROM inserted i
    INNER JOIN deleted d ON
        d.id = i.id
    WHERE
        d.reconciled = 1
)
BEGIN
    RAISERROR('Any row that has "reconciled" set to 1 cannot be updated at all.  All attempted updates in this batch have been cancelled.', 16, 1)
    ROLLBACK TRANSACTION
END --IF
GO
0
 
LVL 40

Expert Comment

by:lcohan
ID: 38820260
@ScottPletcher
 "I've never been any place that truly, fully blocked developers from changing prod data."

Hmmm.....not to start any war here but....ANY place having a Production/QA/Dev environment MUST block developers access(at the MOST just let them have read access).
Or maybe I should say any business that cares a little bit about security but again - as I said...no wars here just a observation/comment.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38820309
@lbrister,

>>I don't have control over who gets access to what.
Neither does the owner apparently
So there are several folks that have access to the SQL Database and can run queries.
The've already deleted records before.<<

You have another problem then, which is security, and that could potentially lead to other risks, not just record being deleted, but information being leaked... Trying to solve this with a trigger, I don't think it's the right approach.

Anyway, I'm not sure Scott trigger covers all the aspects, since it's missing the delete part.
I was thinking maybe something along this line. I'm sure Scott can improve on it and come with a different approach.

CREATE TRIGGER Tr_Visit ON ClientVisitTracking
INSTEAD OF UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
	--If bit is <> 1
	if not exists(select 1 from inserted a inner join ClientVisitTracking b on a.id = b.id and b.reconciled = 1)
	begin

		--if delete
		if not exists(select i.id from inserted i full outer join deleted d on a.id = b.id)
		begin
			delete a
			from ClientVisitTracking a
			inner join deleted b on a.id = b.id
		end

		--if update
		if not exists(select coalesce(i.id, d.id) from inserted i full outer join deleted d on a.id = b.id)
		begin
			update a
			set 	a.[ClientID] = b.ClientID,
				a.[ContactID] = b.ContactID,
				a.[createdBy] = b.CreatedBy,
				a.[createdDate] = b.[CreatedDate],
				a.[reconciled] = b.[reconciled]
			from ClientVisitTracking a
			inner join inserted b on a.id = b.id
	end

END

Open in new window

0
 

Author Comment

by:lrbrister
ID: 38820311
Hey folks,
 I make my living from small companies of 20-40 people that have a great idea and are going to the "Next Level"

They started with Excel and Word files everywhere on the system and QUickBooks
Moved to MS Access with Access data
Changed to MS Access  and SQL Server backend because of corruption issues

Their Web looks like it was made in Publisher by the drunk marketing team on a Friday night.

Now they want to move to a professional look and a business app with .Net AJAX or WinForms

Along the way they have "trusted" employees that have access to everything, everywhere.

And I have a serious battle getting them to remove access to these folks.

It's just the way it is.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38820367
>>Along the way they have "trusted" employees that have access to everything, everywhere.

And I have a serious battle getting them to remove access to these folks.<<

Fair enough, just remember that if they have access to everything, the could easily disable the trigger... Plus you will have to create new triggers / rules for each new situation you might encounter...
0
 

Author Comment

by:lrbrister
ID: 38820381
scott...your example didn't prevent anything

ralmada
I had to change line 11 ans 19 to get yours to compile.

But now it prevents ANYTHING from getting updated...or deleted.

ALTER TRIGGER Tr_zTest ON zTest
INSTEAD OF UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
	--If bit is <> 1
	if not exists(select 1 from inserted a inner join zTest b on a.id = b.id and b.reconciled = 1)
	begin

		--if delete
		if not exists(select i.id from inserted i full outer join deleted d on i.id = d.id)
		begin
			delete a
			from zTest a
			inner join deleted b on a.id = b.id
		end

		--if update
		if not exists(select coalesce(i.id, d.id) from inserted i full outer join deleted d on i.id = d.id)
		begin
			update a
			set a.sText = b.sText,
				a.lText = b.lText,
				a.dataDate = b.DataDate,
				a.[createdDate] = b.[CreatedDate],
				a.nCol = b.nCol,
				a.[reconciled] = b.[reconciled]
			from zTest a
			inner join inserted b on a.id = b.id
		END
	End

END

Open in new window

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 38820440
>> scott...your example didn't prevent anything <<

Hmm, odd, I don't see how that could be for UPDATEs, could something else be going on?

Maybe some other code is trapping then ignoring the error?

Did you check the rows themselves?



I did, however, leave off DELETE, which I should have included:


CREATE TRIGGER dbo.ClientVisitTracking__TRG_DEL_UPD
ON dbo.ClientVisitTracking
AFTER DELETE, UPDATE
AS
IF EXISTS(
    SELECT 1
    FROM inserted i
    INNER JOIN deleted d ON
        d.id = i.id
    WHERE
        d.reconciled = 1
)
BEGIN
    RAISERROR('Any row that has "reconciled" set to 1 cannot be deleted or updated at all.  All attempted deletes or updates in this batch have been cancelled.', 16, 1)
    ROLLBACK TRANSACTION
END --IF
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38820482
So, ralmada, are you claiming that at your company now, you can't go to a Query window screen and issue an UPDATE statement against a prod table??  Not even one??
0
 
LVL 40

Expert Comment

by:lcohan
ID: 38820679
I don't want to steal someone elses answer however....in my opinion there must be most of the companies not allowing to do that and the places you've been were more open about security or data not that sensitive.
Just think that some developer would miss by accident (NOT bad intention) the WHERE clause in an update or delete...you can figure the rest if there is no FK to prevent the action...that's all I'm gonna say anymore...

 I'm off - good luck:)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38820711
@lcohan:

So you're saying that in the place(s) you've worked, developers have been literally, genuinely prevented from modifying prod data, no matter what method they try to use to do it?

It sounds obvious, but I've never actually seen it genuinely done and enforced.
0
 

Author Comment

by:lrbrister
ID: 38821515
Guys,
 I've seen in larger companies that the developers have no access at that level.

But smaller companies calling in a contracter?  Usually he's the only guy with an inkling.
0
 

Author Closing Comment

by:lrbrister
ID: 38835226
Thanks
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question