Self-updating table in InnoDB
Posted on 2011-04-20
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.