Solved

SQL Table Column

Posted on 2013-01-25
24
223 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
  • 9
  • 8
  • 4
  • +1
24 Comments
 
LVL 39

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:ScottPletcher
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
 
LVL 69

Expert Comment

by:ScottPletcher
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:ScottPletcher
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:ScottPletcher
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:ScottPletcher
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 69

Expert Comment

by:ScottPletcher
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 39

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:
ScottPletcher 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:ScottPletcher
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 39

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:ScottPletcher
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now