Self-updating table in InnoDB

Posted on 2011-04-20
Last Modified: 2012-05-11
Hopefully I can explain this as well as needed.

I am currently designing the database that will run an ERP I am developing. The ERP primarily internal with external access via the internet as required. I am using InnoDB tables in MySQL 5.x.

Part of the ERP is user permissions. I am planning on having two views for updating and assigning permissions. One 'broad' and one 'granular'. In the broad view I would like to assign 'x_create' to a user, which will imply 'x_delete'. In the granular view I will be able to select 'x_delete', 'x_create', 'x_modify' etc... separately, giving more control.

To minimize the amount of code required to check permissions, I was hoping there is a way within the database itself of assigning conditional statements.


If table.x_create = 1 then table.x_delete = 1 (this will fire ONLY when updating table.x_create)

It should not fire when table.x_create is set to 0 and consequently should not set x_delete to 0. This preserves the granular control (ie- removing permission to create while maintaining permission to delete)

Does such a thing exist, and can you link to a reference or provide an example of how to set it up? I can set this up in PHP, but it requires either:

1. Additional checks if more than one permission implies more than one other permission
2. Checks at the time of assigning permissions to update other permissions depending on the broad/granular view used.
Question by:Vampireofdarkness
    LVL 2

    Accepted Solution

    You could solve it using update/insert triggers.

    This will require code in the databasse that can, depending on your front end, increase speed. If you're using a language like C++, Java, ... you could put the code as well into your native code (in form of SQL queries) to make the code more readable.

    The granular table may be a view on the detailed table:
     select person.Name, securables.Name,  
              (select sign(x_create*x_delete*x_modify) from granular 
                         person.ID = ... 
                       and securable.ID = ...
             ) as x_create 
    from granular
        join person on person.ID = granular.personID
       join securable on securable.ID = granular.securableID
    group by person.Name, securables.Name

    Open in new window

    You can add other generic rights using the schema above. The sign function will limit the value of the product to one even if any component has a value > 1. The product will return zero if any of the granular right is not granted.

    To your imply-problem: Put them into a seperate database table and write a generic method that updates the settings based on that data. It's a bit of work but pays off. You need to have the generic right and the granular right in one row as well as an indicator whether to act on grant or deny (or on both). Fetching all the rows having the generic right in it along with the current action (set or unset) will give you all of the granular rights to be changed.

    To set the single access rights based on granting/denying the high level rights you need to code that with the help of the table described above.

    LVL 9

    Author Comment

    I did consider triggers before asking, but I've never really found a whole lot of documentation for it. Perhaps something like the following:

    (Table: user_permissions)
    (Adding a field for 'broad' which I'll set to field_name when using the broad form)

    CREATE TRIGGER user_permission_verify AFTER UPDATE on user_permissions
          IF NEW.broad = 'user_create' THEN
             IF NEW.user_create = 1 THEN
                SET NEW.user_modify = 1; // user_create implies ability to user_modify
                SET NEW.user_delete = 1; // user_create implies ability to user_delete
                SET NEW.broad = NULL; // Reset the granular update flag.
             END IF;
          END IF;

    Open in new window

    I haven't tested this as currently I have no data in my tables. I don't know if the = operator should be = or == yet.

    In theory, after an update, if user_permissions.broad = user_create it'll check if user_permissions.user_create == 1 then change user_permissions.user_modify and user_permissions.user_delete to 1 also.
    LVL 9

    Author Closing Comment

    Slight tweak to my code:

    It has to be BEFORE, not AFTER when using SET apparently. Also, comments are # not //.

    I'll be able to set this up to do what I wanted.

    Thanks again.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now