Lock a Table Column For Updating?

[nomi2000]

dear fellow progarmmers
Is it possible to Lock a table column for updating means once i insert the row in the table i don't want anyone to update a certain column in that row.
b) secondly is it posssible to behave a column as toggel say i have a bolean column and i want only 1 value to be true at a time
Regards
Nouman

 

[bperry]

re: 'locking' a column
There is no feature or column attribute that you can directly set to prevent updates to a column value.

But you can perhaps indirectly achieve the same effect by writing an Update trigger on the table. The trigger would automatically fire every time the table was updated, check to see if your column had changed, and then could do either of two things, depending on what you wanted.

(1)One option would be to cancel the whole update transaction completely, and return an error message to the user. Something like this, perhaps:

CREATE TRIGGER ProtectClientName ON TestTable
FOR UPDATE
AS
IF UPDATE(ClientName)
BEGIN
RAISERROR('Cannot update Client Name',16,1)
ROLLBACK TRAN
END
------------------

(2)Another option would be to allow the original update, but to re-update the table to set the column value back to what it was. This would be useful if several columns were being updated, and you wanted to allow the other changes to go through okay.

CREATE TRIGGER ProtectClientName ON TestTable
FOR UPDATE
AS
IF UPDATE(ClientName)
BEGIN
SET NOCOUNT ON
UPDATE TestTable
Set ClientName = (Select ClientName from Deleted)
END
------------------

Give them a try if you like to see how it works in your environment.

bperry

 

[bperry]

Oooops, my apologies!!!
There is an error in Option2 shown above. I forgot to include a Where clause in the code that re-updates the table with the old value. Try this instead:

CREATE TRIGGER ProtectClientName ON TestTable
FOR UPDATE
AS
IF UPDATE(ClientName)
BEGIN
SET NOCOUNT ON
UPDATE TestTable
Set ClientName = (Select ClientName from Deleted)
Where PkId = (Select PkId From Deleted)
END
-----------------------------
Again, my apologies for that unhelpful error.
bperry

 

[bperry]

Read this only if you can stand some more mindless detail ......

You'll notice in my examples above, that I use UPDATE(ClientName) to determine if the client name had changed. But UPDATE() doesn't actually check to see if the client value changed; I believe it just looks to see if the client name column was included in the Update statement.

i.e. If you said:
Update TestTable Set ClientName = 'Bill' Where PkId = 1

then UPDATE() would think the value had changed even if the name was already Bill (was not really changing.)


However, you can set up to get around this if it suited you.
We could change the trigger, for instance, so that it would only generate an error message if the client name was really changing. With the trigger code shown below, we could say:
Update TestTable Set ClientName = 'Bill' Where PkId = 1

and if the clientname was already Bill, then we would not get an error message back.

CREATE TRIGGER ProtectClientName ON TestTable
FOR UPDATE
AS
BEGIN
IF EXISTS
(Select 'True'
From Inserted i JOIN Deleted d
ON i.PkId = d.PkId
Where i.ClientName <> d.ClientName)
BEGIN
RAISERROR('Cannot update Client Name',16,1)
ROLLBACK TRAN
END
END
-------------------------------
Like I said, I just mention this in case that's the way you want to go.

 

[tlbroadbent]

bperry,

The code you've provided assumes that deleted will contain only one record. The triger will error if multiple rows are updated.

CREATE TRIGGER ProtectClientName ON TestTable
FOR UPDATE
AS
IF UPDATE(ClientName)
BEGIN
SET NOCOUNT ON
UPDATE TestTable
Set ClientName = d.ClientName
FROM TestTable t
JOIN Deleted d
WHERE ON t.PkId=d.PkId
END

Nouman,

As far as locking a column goes, another option is to Deny Update permissions on the column. See Deny in SQL BOL. However, permisions are denied to users or roles so this would not prevent updates by database owners or system administrators. However, that should be OK if your security is set up for roles and/or users. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[bperry]

Hi Terry,
Yes, I knew that was an issue, but I didn't know exactly how to allow for it. (I live in a trigger-less world, and had never written one before.) I was quite sure you would correct it for me. Thank you.

re: Denying Update Permissions
Yes, I agree that's another way, and it deserves to be mentioned. I omitted it only because I have never seen anyone actually implement security down to that level. (Have you?) I'm under the impression that most people don't think it's worth the hassle; but no doubt it definitely could be done.

On the other hand, a potential problem with triggers is that they can be shut off, and forgotton to be turned back on. So I guess that has to be weighed against the pluses and minuses of the DENY approach.

thanks again

 

[nomi2000]

Dear bperry adn teryy
thanx bothe for gicing me such good help i really appreciate the way you people are helping,well yes the onething i want is not to update certain column no matter who the user are? becuase i make a LOOKUP table and i don't want any sql user to change oh one thing i forgot the above suggestion will help me in locking the column but wat about deleteing the row or row(s) from table i also don't want this to be happend even don't want to drop that table can i do this?
i dunno get the answer to the second questio
b) secondly is it posssible to behave a column as toggel say i have a bolean column and i want only 1 value to be true at a time ( means the column should have only one default value to true and all else should be false)
Regards
Nouman

Regards
Nouman

 

[bperry]

terry:
I have done some testing re: DENY on the specific column (ClientName). I found that it works okay, but if there are several columns being updated, including ClientName, then DENY works to abort the whole update.

i.e. Within an Update, if Nouman didn't want to rollback the entire transaction, but instead wanted to allow the other columns to go ahead, but just Rollback the ClientName update, then he would have to go back to the trigger approach.

 

[bperry]

Nouman:
...well yes the onething i want is not to update certain column no matter who the user are?

It is really tough to absolutely prevent your Administrator accounts (sa, for example) from updating the column. i.e. if there is an Update trigger on the table, the sa can temporarily disable it. If sa DENYies himself permission to update the table, he could GRANT it back again.

So we can tightly control what our normal database users can do; and we can somewhat control what sa can do, but it is difficult to provide absolute guarantees.


...the above suggestion will help me in locking the column but wat about deleteing the row or row(s) from table
A Delete trigger could also be used here, but in this case I would use the DENY approach (i.e. DENYing Delete permission) instead, because here we are looking at deleting the entire row, not just focusing on a single column.

It's a similar situation. We can prevent the users from doing this with by DENYing delete permission. But it would be tough to absolutely guarantee that sa could not do that.


...i also don't want this to be happend even don't want to drop that table can i do this?
Again, this is tough to do. Your average user won't be able to do this, but at the end of the day the sa can drop the whole darn database, and there's not a lot you can do about that.

 

[bperry]

re: your second question
b) secondly is it posssible to behave a column as toggel say i have a bolean column and i want only 1 value to be true at a time ( means the column should have only one default value to true and all else should be false)

I'm not sure I clearly understand what you are meaning here.
I think you are saying that if there are 10 rows in the table, only one of the rows may have a particular column set to 'true' at a time, and the other 9 rows must have a value of 'false'. And if a new row was is inserted, it must have a column value of 'false', because there is already a row with 'true'. Is this correct?

If that's correct, then a similar issue was raised yesterday in thread183-265364 . The user wanted only one record 'active' at a time. You could look at that to get an idea.

BTW, SQL Server doesn't have a boolean datatype per se. We usually accomodate that situation by having a column of integer or varchar, with values of 0/1, or Null/1, Active/Inactive, that kind of thing. There's also a bit datatype that often gets used for that; but I think that one is a T-SQL extension rather than ANSI, if that's a concern to you. (It usually isn't to most users in this forum.)

 

[nomi2000]

dear bperry and terry
thanx for ur help i am getting closer and closer what i want to achieve
bperry u said
&quot;sa can drop the whole darn database, and there's not a lot you can do about that.&quot;
well yes i agreed on it but still u see i have checked the existance of database before the user can log into the application (written in VB) using SQL server 2000..but i can't check each and every table existance and their names..what i want if possible is
a) No user can be able to change my object's name viz StoredProcedures,View and Tables becuase it will make my application to throw Errors well yes i can handle this with a good error handler in application but still want to strengthen this by not allowing anyone to do this...at bottom line i don't want the user to change my DATABASE STRUCTURE,is it possible at back-end? May be some kind of SQL script which can locked or prevent the user from changing the structure might do this,but i am not sure?
actually the problem is that i made the database and then send this database to client which he restores at his place but i don't want him to do changing in structure or playing with the data present in my lookup tables (which are necessary for the application)
thanxs again for ur help
Regards
Nouman

 

[bperry]

...Maybe some kind of SQL script which can locked or prevent the user from changing the structure might do this,but i am not sure?

I doubt there is a script approach to answering your questino. In my experience all this is usually resolved by not giving permissions to users to do those kinds of activities.

But having said that, I haven't been involved with situations where we are sending the database to someone else to run, and hoping they don't change it. Hopefully, some other folks (who are stronger in admin issues than I am) might comment here.

I think it would have been better if you had indicated in your original post what was really on your mind (i.e. sending database to client.) We may have gotten some other forum readers to comment who are quite familiar with that scenario.

My sincere apologies for not being able to help you further at this time. But I will continue to follow this thread to see if anything develops.

bperry

 

[tlbroadbent]

I have a correction on the trigger and some additional logic. The code has a syntax error - an extra WHERE!

You'll want to make sure you don't update the column if it wasn't changed.

CREATE TRIGGER ProtectClientName ON TestTable
FOR UPDATE
AS
IF UPDATE(ClientName)
BEGIN
SET NOCOUNT ON
UPDATE TestTable
Set ClientName = d.ClientName
FROM TestTable t
JOIN Deleted d
ON t.PkId=d.PkId
WHERE t.ClientName <> d.ClientName

END
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[tlbroadbent]

db owners, sys admins and ddl admins can create, drop or modify objects. When you control the database, make sure you don't grant these permissions or make a user a member of roles with those permissions. You can also encrypt Stored procedure definitions, User-defined function definitions, View definitions, Trigger definitions, Default definitions, and Rule definitions to prevent modifications to those objects.
Even the use of triggers will not stop DBOs and Sys Admins from changing the data. Triggers can be disabled.

If you send a database to a client site to load on their server, they will be in control. They will have a sys admin account. They will setup logins, users, and permissions. They can do whatever they want to a database (except encrypted objects). So the question is, &quot;Why would they want to invalidate or break the database by changing the structure or deleting lookup entries?&quot; We use databases purchased from other companies and would never consider making schema changes or modifying lookup data provided by the vendor.

One vendor, however, insists that its database must be the only DB on the server. The installation script secures the sa account, adds new sys admin , db owner and user accounts, In this case all permissions are controlled by the vendor. Not many customers will want to purchase a Server to run one application. If you want more control over the database, that may be your only option. But even this is not totally secure. Bottom line is, you have to trust that a client doesn’t want to break an application or a database.
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[nomi2000]

Dear bPerry and terry
thanx of u ,u pplz are giving valuable help
well bperry i am sorry but u see when i asked that q the main objective is to somehow did how to secure DB and one of the q regarding this is lock the column for updating...
terry after reading ur last post its very tough to completely secure the DB but still i can do somelevel of security to it so they don't change my objects
so there is two things which i can do
a) Somehow make my objects (SP,Views and Tables) hidden (If possible) i don't know if its possible to hidden some object using some SQL i need help if it can be done?
b) moreover i can encrypt the objects,i also don't know how to encrypt objects,so terry or bperry i need ur help on it..

i am not the guru at back-end but these soltns might tighten things to secure my objects not completely ofcourse but at some level

Thaks and Regards
Nouman

 

[tlbroadbent]

AS I've said, you can't hide tables from system admins. There is really nothing you can do except encrpyt some objects. That's the way it should be. A system administrator must be able to do all things or she won't be an SA.

It is easy to encrypt a view, procedure, trigger, etc. Simply use the WITH ENCRYPTION option when creating these objects. SQL BOL contains all the details. Make sure you save unencrypted source for each object you create with encryption. You'll not be able to view the encrypted object. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[nomi2000]

thanx Terry
i got it but there is one q left the Trigger which i made is running fine now tell me how to put it OFF so i could see the column can be update?
i mean can i put Trigger OFF withoug deleting it
Regards
Nouman

 

[bperry]

What you do there is temporarily disable the trigger. Change names to suit.

-- Disable the trigger.
ALTER TABLE TestTable
DISABLE TRIGGER ProtectClientName

-- Re-enable the trigger.
ALTER TABLE TestTable
ENABLE TRIGGER ProtectClientName

 

[nomi2000]

thanks bperry
ur help is effortless ,bperry can i do this without the SQL i mean is is there option at design ?
Regards
Nouman

 

[bperry]

When you Create the trigger, there's no option to say 'Create Trigger Disabled'. So it is enabled by default, and you then have to disable it if that's what you wanted. And similarly re-enable leter, if you wanted.