This one has me stumped: we have a fairly small database (it has only a
single table) containing records entered over a couple of years. The
records have date-of-creation fields. Can we set permissions such that
the records created in every year prior to the current one are
read-only? We want users to be able to search the entire database, but
only modify this year's records. Thanks in advance to all who respond.

I dont think you can do logic-based access grants in mysql. What you
can do is create a separate schemas for current year and the old ones,
and grant access to the users accordingly on the two schemas. This
might need a change in your app, as you need to include both schemas in
your searches.

Either this, or you can control this at your application level.

-cheers,
Manish

Leslie Houk wrote:
> This one has me stumped: we have a fairly small database (it has only a
> single table) containing records entered over a couple of years. The
> records have date-of-creation fields. Can we set permissions such that
> the records created in every year prior to the current one are
> read-only? We want users to be able to search the entire database, but
> only modify this year's records. Thanks in advance to all who respond.

I dont think you can do logic-based access grants in mysql. What you
can do is create a separate schemas for current year and the old ones,
and grant access to the users accordingly on the two schemas. This
might need a change in your app, as you need to include both schemas in
your searches.

Either this, or you can control this at your application level.

-cheers,
Manish

Leslie Houk wrote:
> This one has me stumped: we have a fairly small database (it has only a
> single table) containing records entered over a couple of years. The
> records have date-of-creation fields. Can we set permissions such that
> the records created in every year prior to the current one are
> read-only? We want users to be able to search the entire database, but
> only modify this year's records. Thanks in advance to all who respond.

As per:

http://dev.mysql.com/doc/refman/5.0/...iff-views.html

"
1.9.5.6. Views
Views (including updatable views) are implemented beginning with MySQL
Server 5.0.1. See Chapter 19, Views.

Views are useful for allowing users to access a set of relations (tables) as
if it were a single table, and limiting their access to just that. Views can
also be used to restrict access to rows (a subset of a particular table).
For access control to columns, you can also use the sophisticated privilege
system in MySQL Server. See Section 5.8, "The MySQL Access Privilege System".

In designing an implementation of views, our ambitious goal, as much as is
possible within the confines of SQL, has been full compliance with "Codd's
Rule #6" for relational database systems: "All views that are theoretically
updatable, should in practice also be updatable."

"

So in other words, you might be able to keep all data in the same table,
dissallow access to the table directly, and instead retrieve data via views
that give the needed rights.

One view for older data - READ ONLY, and one for newer FULL permissions.

HTH.

~ Duane Phillips

"Leslie Houk" <lhouk@ghg.net> wrote in message
news:1158175622.788592.99830@d34g2000cwd.googlegro ups.com...
> This one has me stumped: we have a fairly small database (it has only a
> single table) containing records entered over a couple of years. The
> records have date-of-creation fields. Can we set permissions such that
> the records created in every year prior to the current one are
> read-only? We want users to be able to search the entire database, but
> only modify this year's records. Thanks in advance to all who respond.
>

Duane Phillips wrote:
> So in other words, you might be able to keep all data in the same table,
> dissallow access to the table directly, and instead retrieve data via views
> that give the needed rights.
>
> One view for older data - READ ONLY, and one for newer FULL permissions.

I admit, I hadn't thought of that approach. I was wondering if I could
put a trigger on the table that would check a record's date whenever
anyone tried to update it, and prevent the update if the record was
older than a given date. But, not having used triggers before, I
couldn't figure out the appropriate CREATE TRIGGER command, or even if
this approach would work.

Leslie Houk wrote:
> Duane Phillips wrote:
>> So in other words, you might be able to keep all data in the same table,
>> dissallow access to the table directly, and instead retrieve data via views
>> that give the needed rights.
>>
>> One view for older data - READ ONLY, and one for newer FULL permissions.
>
> I admit, I hadn't thought of that approach. I was wondering if I could
> put a trigger on the table that would check a record's date whenever
> anyone tried to update it, and prevent the update if the record was
> older than a given date. But, not having used triggers before, I
> couldn't figure out the appropriate CREATE TRIGGER command, or even if
> this approach would work.
>
Why not use archive tables, they can only do inserts and selects and are
compressed.

You can use Stored Procedures for your updates and writes, and have those
test the date or cancel the update. Leave the table read only except via
SPs.

Or, you could implement writes within your app... unless the users get
direct access to table data. But the Views do a lot in the way of
controlling that... that is what they are there for, not to mention joining
related table result sets.

Cheers!

~ Duane Phillips.

"Leslie Houk" <lhouk@ghg.net> wrote in message
news:1158251567.883354.13250@b28g2000cwb.googlegro ups.com...
> Duane Phillips wrote:
>> So in other words, you might be able to keep all data in the same table,
>> dissallow access to the table directly, and instead retrieve data via
>> views
>> that give the needed rights.
>>
>> One view for older data - READ ONLY, and one for newer FULL permissions.
>
> I admit, I hadn't thought of that approach. I was wondering if I could
> put a trigger on the table that would check a record's date whenever
> anyone tried to update it, and prevent the update if the record was
> older than a given date. But, not having used triggers before, I
> couldn't figure out the appropriate CREATE TRIGGER command, or even if
> this approach would work.
>

Would you not lose "ACID" compliance by using the "Archive" engine type?

~ Duane Phillips.

"Kim Hunter" <s4129366@student.uq.edu.au> wrote in message
news:eecko8$2afs$1@bunyip2.cc.uq.edu.au...
> Leslie Houk wrote:
>> Duane Phillips wrote:
>>> So in other words, you might be able to keep all data in the same table,
>>> dissallow access to the table directly, and instead retrieve data via
>>> views
>>> that give the needed rights.
>>>
>>> One view for older data - READ ONLY, and one for newer FULL permissions.
>>
>> I admit, I hadn't thought of that approach. I was wondering if I could
>> put a trigger on the table that would check a record's date whenever
>> anyone tried to update it, and prevent the update if the record was
>> older than a given date. But, not having used triggers before, I
>> couldn't figure out the appropriate CREATE TRIGGER command, or even if
>> this approach would work.
>>
> Why not use archive tables, they can only do inserts and selects and are
> compressed.

Duane Phillips wrote:
> You can use Stored Procedures for your updates and writes, and have those
> test the date or cancel the update. Leave the table read only except via
> SPs.

I think that will be the best approach, but my lack of mySQL knowledge
is hurting me. I did some reading in the MySQL 5.1 Reference Manual on
dev.mysql.com, and given that I have the table ITPLAN.REQUESTS with the
DATE field "init_date", I'm thinking I would do something like:

CREATE TRIGGER FY2007 BEFORE UPDATE ON ITPLAN.REQUESTS
IF "update request's init_date value" < '2006-09-01' THEN "ignore
update request"

but I don't know what to put for "update request's init_date value" or
"ignore update request". (I think "update request's init_date value"
might be just "NEW.init_date", but I'm not sure about that.) I tried
looking it up in the MySQL 5.1 Reference Manual, but I couldn't find
the appropriate section. Any guidance would be humbly appreciated.

Leslie

Triggers and Stored Procedures are two different things.
However, you will likely acheive what you want with either one.

19.1. CREATE TRIGGER Syntax

That is your MySQL reference section.

This is a sample from the site:

http://dev.mysql.com/doc/refman/5.1/...e-trigger.html

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|

DELIMITER ;

I do not know the syntax for an "ignore" or "cancel" update. Worst case
scenario, you compare the required field, and set all other fields to old or
new values accordingly.

Cheers!

~ Duane Phillips.


"Leslie Houk" <lhouk@ghg.net> wrote in message
news:1158855715.717439.44740@e3g2000cwe.googlegrou ps.com...
> Duane Phillips wrote:
>> You can use Stored Procedures for your updates and writes, and have those
>> test the date or cancel the update. Leave the table read only except via
>> SPs.
>
> I think that will be the best approach, but my lack of mySQL knowledge
> is hurting me. I did some reading in the MySQL 5.1 Reference Manual on
> dev.mysql.com, and given that I have the table ITPLAN.REQUESTS with the
> DATE field "init_date", I'm thinking I would do something like:
>
> CREATE TRIGGER FY2007 BEFORE UPDATE ON ITPLAN.REQUESTS
> IF "update request's init_date value" < '2006-09-01' THEN "ignore
> update request"
>
> but I don't know what to put for "update request's init_date value" or
> "ignore update request". (I think "update request's init_date value"
> might be just "NEW.init_date", but I'm not sure about that.) I tried
> looking it up in the MySQL 5.1 Reference Manual, but I couldn't find
> the appropriate section. Any guidance would be humbly appreciated.
>
> Leslie
>