Professional Web Applications Themes

Controlling user access - MySQL

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....

  1. #1

    Default Controlling user access

    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.

    Leslie Guest

  2. #2

    Default Re: Controlling user access

    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: 

    Manish Guest

  3. #3

    Default Re: Controlling user access

    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: 

    Manish Guest

  4. #4

    Default Re: Controlling user access

    As per:

    http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-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" <net> wrote in message
    news:googlegroups.com... 


    Duane Guest

  5. #5

    Default Re: Controlling user access

    Duane Phillips wrote: 

    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 Guest

  6. #6

    Default Re: Controlling user access

    Leslie Houk wrote: 
    >
    > 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.
    >[/ref]
    Why not use archive tables, they can only do inserts and selects and are
    compressed.
    Kim Guest

  7. #7

    Default Re: Controlling user access

    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" <net> wrote in message
    news:googlegroups.com... 
    >
    > 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.
    >[/ref]


    Duane Guest

  8. #8

    Default Re: Controlling user access

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

    ~ Duane Phillips.

    "Kim Hunter" <uq.edu.au> wrote in message
    news:eecko8$2afs$cc.uq.edu.au... 
    >>
    >> 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.
    >>[/ref]
    > Why not use archive tables, they can only do inserts and selects and are
    > compressed.[/ref]


    Duane Guest

  9. #9

    Default Re: Controlling user access

    Duane Phillips wrote: 

    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

    Leslie Guest

  10. #10

    Default Re: Controlling user access

    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/en/create-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" <net> wrote in message
    news:googlegroups.com... 
    >
    > 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
    >[/ref]


    Duane Guest

  11. #11

    Default Controlling user access

    > 19.1. CREATE TRIGGER Syntax 

    Yeah, I found that, printed it out, and got thoroughly confused before
    I posted my initial question.
     

    You mean, instead of checking before the update and trying to cancel
    the changes, check after the update and reverse the changes if
    necessary? Something like:

    CREATE TRIGGER FY2007 AFTER UPDATE ON ITPLAN.REQ
    FOR EACH ROW BEGIN
    IF REQ.INIT_DATE < '2006-09-01' THEN
    IF REQ.FIELD1 <> OLD.FIELD1 THEN SET REQ.FIELD1 =
    OLD.FIELD1;
    IF REQ.FIELD2 <> OLD.FIELD2 THEN SET REQ.FIELD2 =
    OLD.FIELD2;
    IF REQ.FIELD3 <> OLD.FIELD3 THEN SET REQ.FIELD3 =
    OLD.FIELD3;
    ...
    ENDIF
    END

    I'm probably writing that code incorrectly, but as you've no doubt
    noticed, you could put everything I know about MySQL into a gnat's
    bellybutton.

    Leslie Guest

  12. #12

    Default Re: Controlling user access

    It isn't a pretty solution is it? <grin> Using a trigger to implement a
    lock on older records is not going to be pretty in any case. And the
    biggest problem is, it provides no feedback to the user whatsoever... A
    Stored Procedure would be better, where you don't even run the update in the
    first place if the date is in the "no-touchy" zone, rather than trying to
    catch it with a trigger. A Stored Procedure can also be geared to provide
    feedback in the way of a failure output parameter, that can be used by the
    UI.

    Triggers are usually used more for DB back-end maintenance, logging,
    integrity, creating child table records, and the like. Using them to
    control user updates is not usually recommended... but it can be done.

    Cheers!

    ~ Duane Phillips.

    "Leslie Houk" <net> wrote in message
    news:googlegroups.com... 
    >
    > Yeah, I found that, printed it out, and got thoroughly confused before
    > I posted my initial question.

    >
    > You mean, instead of checking before the update and trying to cancel
    > the changes, check after the update and reverse the changes if
    > necessary? Something like:
    >
    > CREATE TRIGGER FY2007 AFTER UPDATE ON ITPLAN.REQ
    > FOR EACH ROW BEGIN
    > IF REQ.INIT_DATE < '2006-09-01' THEN
    > IF REQ.FIELD1 <> OLD.FIELD1 THEN SET REQ.FIELD1 =
    > OLD.FIELD1;
    > IF REQ.FIELD2 <> OLD.FIELD2 THEN SET REQ.FIELD2 =
    > OLD.FIELD2;
    > IF REQ.FIELD3 <> OLD.FIELD3 THEN SET REQ.FIELD3 =
    > OLD.FIELD3;
    > ...
    > ENDIF
    > END
    >
    > I'm probably writing that code incorrectly, but as you've no doubt
    > noticed, you could put everything I know about MySQL into a gnat's
    > bellybutton.
    >[/ref]


    Duane Guest

Similar Threads

  1. Controlling Access
    By Doug Wolfgram in forum PHP Development
    Replies: 1
    Last Post: August 26th, 04:12 PM
  2. [PHP] Controlling Access
    By Javier Tacon in forum PHP Development
    Replies: 0
    Last Post: August 26th, 04:00 PM
  3. controlling access to parts of a site
    By Geoff Wickens in forum ASP
    Replies: 2
    Last Post: August 24th, 04:41 PM
  4. controlling user edits and using timeouts
    By SlimFlem in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 11:55 AM
  5. Controlling Unix access using Wins2000 AD
    By Jean Q. He in forum Sun Solaris
    Replies: 1
    Last Post: July 2nd, 09:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139