Can I make a column read-only?

[Chuck Anderson]

I'd like to set a value for a column when a row is first inserted and
then I'd like to lock that column so that it cannot accidentally be
changed (updated), while still allowing other columns in the row to be
updated.

Is there a way to do that?

--
*****************************
Chuck Anderson • Boulder, CO
http://www.CycleTourist.com
Nothing he's got he really needs
Twenty first century schizoid man.
***********************************

[Gordon Burditt]

>I'd like to set a value for a column when a row is first inserted and
>then I'd like to lock that column so that it cannot accidentally be
>changed (updated), while still allowing other columns in the row to be
>updated.

You can use column-level privileges to control this. GRANT SELECT
and INSERT privileges on the column to be made "read-only", and
grant SELECT, INSERT, and UPDATE privileges on the other columns
in the table to users allowed to do any updates at all.

You also need to be sure *not* to grant table-level, database-level,
or global-level privileges on this table, as these will give
privileges broader than what you want and defeat the whole point
of avoiding updates.

[Chuck Anderson]

Thank you. That's exactly what I was looking for (but could not find).

[Chuck Anderson]

Chuck Anderson wrote:
> Gordon Burditt wrote:
>
>>> I'd like to set a value for a column when a row is first inserted and
>>> then I'd like to lock that column so that it cannot accidentally be
>>> changed (updated), while still allowing other columns in the row to be
>>> updated.
>>>
>>>
>> You can use column-level privileges to control this. GRANT SELECT
>> and INSERT privileges on the column to be made "read-only", and
>> grant SELECT, INSERT, and UPDATE privileges on the other columns
>> in the table to users allowed to do any updates at all.
>>
>> You also need to be sure *not* to grant table-level, database-level,
>> or global-level privileges on this table, as these will give
>> privileges broader than what you want and defeat the whole point
>> of avoiding updates.
>>
>>
>>
>
> Thank you. That's exactly what I was looking for (but could not find).
>
>

I'm having trouble with this.

I'm using MySQL Administrator to make the task easier (MySQL vers
4.1.14 - Windows XP). I created a new account, gave it no global
privileges and no Schema privileges. Under Schema Object privileges I
did not assign privileges for the entire database, rather I assigned
privileges to each table in the database separately - except for the
table with the read-only field. For that table I granted privileges to
the fields individually as you stated.

The problem I've encountered is:
UPDATE command denied to user 'new_user'@'localhost' for table 'the_table'

I can read and write all the other tables in the database, but I am
unable to update "the_table" where I granted privileges per field (the
"read-only" field is not in the UPDATE query).

I cannot make it deny access to a single column. I am denied UPDATE
rights to the whole table.

The table in question does not appear in tables_priv, but all the other
tables in the database are in there with Select,Insert,Update
privileges. The table in question is in columns_priv with all fields in
the table entered with Select,Insert,Update privileges (even the one I
want to ultimately limit access to).

I don't know what else I need to do.

[Gordon Burditt]

>>> You can use column-level privileges to control this. GRANT SELECT
>>> and INSERT privileges on the column to be made "read-only", and
>>> grant SELECT, INSERT, and UPDATE privileges on the other columns
>>> in the table to users allowed to do any updates at all.
>>>
>>> You also need to be sure *not* to grant table-level, database-level,
>>> or global-level privileges on this table, as these will give
>>> privileges broader than what you want and defeat the whole point
>>> of avoiding updates.
>>>
>>>
>>>
>>
>> Thank you. That's exactly what I was looking for (but could not find).
>>
>>
>
>I'm having trouble with this.
>
>I'm using MySQL Administrator to make the task easier (MySQL vers
>4.1.14 - Windows XP).

Is that version of MySQL new enough?

>I created a new account, gave it no global
>privileges and no Schema privileges. Under Schema Object privileges I
>did not assign privileges for the entire database, rather I assigned
>privileges to each table in the database separately - except for the
>table with the read-only field. For that table I granted privileges to
>the fields individually as you stated.

The output of
SHOW GRANTS for 'new_USER'@'localhost';
would be useful here.

I have not used these features extensively and I have run into
problems with GUI-based administration thingies that don't do the
right thing if you have an empty privilege level. (That is, you
have to grant *something* at the global, database, and table levels,
even if it's only CONNECT, which is pretty much a dummy privilege,
in order to avoid confusing it.) I haven't used MySQL Administrator
and I'd be inclined to revert to the command line 'mysql' interface
to debug the problem.

Also, read the documentation carefully. There may be some details
I haven't mentioned.

>The problem I've encountered is:
>UPDATE command denied to user 'new_user'@'localhost' for table 'the_table'
>
>I can read and write all the other tables in the database, but I am
>unable to update "the_table" where I granted privileges per field (the
>"read-only" field is not in the UPDATE query).

I take it all the fields you *ARE* updating are listed with column-level
UPDATE privilege.

[Chuck Anderson]

Gordon Burditt wrote:
>>>> You can use column-level privileges to control this. GRANT SELECT
>>>> and INSERT privileges on the column to be made "read-only", and
>>>> grant SELECT, INSERT, and UPDATE privileges on the other columns
>>>> in the table to users allowed to do any updates at all.
>>>>
>>>> You also need to be sure *not* to grant table-level, database-level,
>>>> or global-level privileges on this table, as these will give
>>>> privileges broader than what you want and defeat the whole point
>>>> of avoiding updates.
>>>>
>>>>
>>>>
>>>>
>>> Thank you. That's exactly what I was looking for (but could not find).
>>>
>>>
>>>
>> I'm having trouble with this.
>>
>> I'm using MySQL Administrator to make the task easier (MySQL vers
>> 4.1.14 - Windows XP).
>>
>
> Is that version of MySQL new enough?
>

Yes.

>
>> I created a new account, gave it no global
>> privileges and no Schema privileges. Under Schema Object privileges I
>> did not assign privileges for the entire database, rather I assigned
>> privileges to each table in the database separately - except for the
>> table with the read-only field. For that table I granted privileges to
>> the fields individually as you stated.
>>
>
> The output of
> SHOW GRANTS for 'new_USER'@'localhost';
> would be useful here.
>

And indeed it helped me understand what was happening, thanks.

Okay .... I'm "not sure exactly" how I got it to work, but the key was
to issue FLUSH PRIVILEGES after making changes. I say, "not sure
exactly" because it seems that I could not do it through MySQL
Administrator alone. I *did* set all the fields as desired using the
handy interface there, but then I had to make a change to at least one
of those fields (not all) from the mysql command line, too ... then
issue FLUSH PRIVILEGES - and then all the changes I made in MySQL
Administrator were accepted.

It now works as desired. Thanks very much for your help.

I suppose I may as well upgrade to MySQL 5 now, too. (Upgrading always
seems to take longer than I expect and I lose half a day .... we'll see).

This exercise has also gotten me into understanding how to manage users
and their privileges ...... all on my development-only - home PC -
machine, but just the same, it's good to know.