mysql Make a cell unchangeable?

[-Alex-]

Hi guys,

Simple question really - is it possible to make a cell unchangeable, or otherwise to lock it? Only 1 cell needed, but if row/table locking is possible, this is also an option.

Thanks! :)

[MattehCarter]

In the MySQL client I use, you make the cell a 'primary key' or 'key' to prevent the data being changed. I'm assuming it would be the same throughout all MySQL clients.

[-Alex-]

In the MySQL client I use, you make the cell a 'primary key' or 'key' to prevent the data being changed. I'm assuming it would be the same throughout all MySQL clients.

A primary key (and keys/indexes in general) do a very different thing to what you're suggesting... I don't know if you're doing it on purpose, but the advice you're giving out on these forums is really bad...

[+theblazingangel MVC]

Hi guys,

Simple question really - is it possible to make a cell unchangeable, or otherwise to lock it? Only 1 cell needed, but if row/table locking is possible, this is also an opktion.

Thanks! :)

You didn't say what in, but I think assuming some common dbms is safe. Most dbms's should allow users to be created and allow you to grant/revoke table/column level access to data. MySQL allows this. If you need to restrict access to specific records in a table, then this should be possible through the use of views.

[MattehCarter]

A primary key (and keys/indexes in general) do a very different thing to what you're suggesting... I don't know if you're doing it on purpose, but the advice you're giving out on these forums is really bad...

You asked how to make a cells data unchangeable, here's some information I picked up about keys from a google search

• Always has a value (can not be null)
  
• Has a value which remains the same (does not change)
  
• Has a unique value for each record in the table
  

You asked a question, I replied with an answer, and other sources also back the information I told you.

I now know who not to offer my help to in the future, goodbye :)

[+theblazingangel MVC]

You asked how to make a cells data unchangeable, here's some information I picked up about keys from a google search

• Always has a value (can not be null)
  
• Has a value which remains the same (does not change)
  
• Has a unique value for each record in the table
  

You asked a question, I replied with an answer, and other sources also back the information I told you.

I now know who not to offer my help to in the future, goodbye :)

-Alex- is actually entirely correct about the solution you proposed, keys/indexes have nothing to do with access restrictions or making data attributes read-only like you suggested.

[MattehCarter]

-Alex- is actually entirely correct about the solution you proposed, keys/indexes have nothing to do with access restrictions or making data attributes read-only like you suggested.

From the question he proposed, I thought he would have a cell that would hold a piece of data that will never be edited, hence the suggestion of a key.

I didn't think he wanted to protect a cell so that the data couldn't be edited; apologies.

[+theblazingangel MVC]

From the question he proposed, I thought he would have a cell that would hold a piece of data that will never be edited, hence the suggestion of a key.

I didn't think he wanted to protect a cell so that the data couldn't be edited; apologies.

even so, that is in no way what keys/indexes are for

[MattehCarter]

even so, that is in no way what keys/indexes are for

I'm guessing the rules for keys in Microsoft Access and MySQL are different then? If not, I think the education is flawed; if I've forgot this basic piece of information, I'm not sure how the hell I achieved an A* in 'Database software (creation and management)'.

My understanding of keys is, what I quoted in my last post, and that they are a pieces of information used to identify rows in a table. Going of this belief, and my use of keys I am perfectly correct. I always use keys in terms of user IDs, usernames, passwords etc. pieces of unique data that will never change.

[+theblazingangel MVC]

I'm guessing the rules for keys in Microsoft Access and MySQL are different then? If not, I think the education is flawed; if I've forgot this basic piece of information, I'm not sure how the hell I achieved an A* in 'Database software (creation and management)'.

My understanding of keys is, what I quoted in my last post, and that they are a pieces of information used to identify rows in a table. Going of this belief, and my use of keys I am perfectly correct. I always use keys in terms of user IDs, usernames, passwords etc. pieces of unique data that will never change.

You are correct in thinking that a primary key is used to uniquely identify a row/record, however there is nothing to stop them changing, the only requirement is that they remain unique within the set of records in that table. Also, you can only have one primary key per table.

[MattehCarter]

You are correct in thinking that a primary key is used to uniquely identify a row/record, however there is nothing to stop them changing, the only requirement is that they remain unique within the set of records in that table. Also, you can only have one primary key per table.

Ah, well my client doesn't let me edit cells which are assigned a primary key value, hence why I said it in my first post.

[+Warwagon MVC]

Wait... we aren't talking about cell phones?..... warwagon is sad :(

[-Alex-]

Matteh, you're completely correct about primary keys being a unique ID assigned to a row (in combination with autoincrement)... but yes, I'm looking to protect a cell/column/table. Sorry for the way I wrote my post... I've been working on an overdue project from 5pm yesterday til 2pm today and I hadn't had any sleep.

You didn't say what in, but I think assuming some common dbms is safe. Most dbms's should allow users to be created and allow you to grant/revoke table/column level access to data. MySQL allows this. If you need to restrict access to specific records in a table, then this should be possible through the use of views.

The dbms was in the tag :p But yes, I should've mentioned it somewhere in the thread too. I'm running MySQL 5.5.19. As for column level locking, can you tell me a bit more about this?

What might complicate this more is that I want the access restriction to stand when the database is dumped and re-imported on to a different server... and I get the feeling that ain't possible.

[+theblazingangel MVC]

The dbms was in the tag :p But yes, I should've mentioned it somewhere in the thread too. I'm running MySQL 5.5.19. As for column level locking, can you tell me a bit more about this?

What might complicate this more is that I want the access restriction to stand when the database is dumped and re-imported on to a different server... and I get the feeling that ain't possible.

tags aren't shown on my mobile browser :p

you just need to check out the grant syntax: http://dev.mysql.com/doc/refman/5.5/en/grant.html

you can absolutely copy the privileges over, they're written in sql.

[HeyRatFans]

Hi guys,

Simple question really - is it possible to make a cell unchangeable, or otherwise to lock it? Only 1 cell needed, but if row/table locking is possible, this is also an option.

Thanks! :)

Only way I can think of doing it would involve a trigger, which means MySQL 5.0 minimum. I assume you want it so it can't be changed with an UPDATE?

Here's an example. Change column_name, database_name to the applicable values and be sure to run the the entire SQL in one transaction.

DELIMITER $$

DROP TRIGGER column_name_readonly$$

CREATE TRIGGER column_name_readonly BEFORE UPDATE ON table_name
FOR EACH ROW BEGIN
    SET NEW.column_name = OLD.column_name;
END;
$$

DELIMITER;[/CODE]

[-Alex-]

Only way I can think of doing it would involve a trigger, which means MySQL 5.0 minimum. I assume you want it so it can't be changed with an UPDATE?

Here's an example. Change column_name, database_name to the applicable values and be sure to run the the entire SQL in one transaction.

<snip>

This worked perfectly! Thank you very much! :D

[-Alex-]

Just out of time to edit, but in the end I used:

CREATE TRIGGER `copyright_readonly` BEFORE UPDATE ON `options`
FOR EACH ROW BEGIN
SET NEW.copyright = OLD.copyright;
END

CREATE TRIGGER `no_delete` BEFORE DELETE ON `options`
FOR EACH ROW BEGIN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You cannot delete from this table!';
END

CREATE TRIGGER `no_insert` BEFORE INSERT ON `options`
FOR EACH ROW BEGIN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You cannot insert into this table!';
END

Just one more question... I presume a user could delete these triggers? What (if anything) would stop them from being able to do so?

[+theblazingangel MVC]

Just one more question... I presume a user could delete these triggers? What (if anything) would stop them from being able to do so?

whether or not the dbms user has the 'trigger' privilege

[-Alex-]

whether or not the dbms user has the 'trigger' privilege

What might complicate this more is that I want the access restriction to stand when the database is dumped and re-imported on to a different server... and I get the feeling that ain't possible.

[+theblazingangel MVC]

like i said earlier, you set up privileges with sql, so as long as you can modify privileges on the other server, you should be able to dump the priviles to a .sql file and import them just like anything else. ;)

[-Alex-]

I won't be able to modify privs on the other server. It's to go to a client in the form of a load of .php files and .sql file... my client will have to setup the MySQL user herself. So obviously that user's gonna have all privs.

Guess I can't prevent it?

[+theblazingangel MVC]

I won't be able to modify privs on the other server. It's to go to a client in the form of a load of .php files and .sql file... my client will have to setup the MySQL user herself. So obviously that user's gonna have all privs.

Guess I can't prevent it?

create a php file to hold "config" data, have them stick the username in that; give them a php "setup" script that will take the username in the config file and issue the necessary create user and grant sql commands to set it up, which they can run after importing the sql file to create the actual database. if necessary, work out a suitable username for them beforehand, and ensure the existing user the setup will be run under has the necessary grant privileges itself. i don't know what you're doing about the existing user credential that was to be used, but you could perhaps have a login form displayed by the setup script that would take those credentials and use them to connect to the database with admin privileges to then create the users for your application (as specified in the config file).

[-Alex-]

Yeah that was the only other way I could think of doing it. Thanks for your help! :)