• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • Last Modified:

Self-updating table in InnoDB

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.
  • 2
1 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.

VampireofdarknessAuthor Commented:
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.
VampireofdarknessAuthor Commented:
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.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now