Your thoughts on mixing "editable" and "non editable" records in the database?

PHP
1

I’m working on a system which has a website and a sizeable back office. One of the design issues we’re looking at is the use of ‘special’ or ‘magic’ values in the database so that editable and non-editable records can live side by side. Some examples are;

Non-editable users: system user, always at least one back office superuser, guest website user
Editable users: all other users

Non-editable automated emails: system confirmation email, system password reset email, etc
Editable emails: marketing emails, newsletter emails, ad-hoc emails

Non-editable core website pages: login page, “404” not found page
Editable pages: all other pages

What are your thoughts on which is best:

  1. add a ‘flag’ column to the database tables to prevent mark certain rows as ‘protected’ or for editing
  2. two tables per orginial table - one for reserved values and one for editable - perform a UNION for SELECTs
  3. hard-code the never to be changed records as arrays/objects in the program code, in a configuration file or elsewhere
  4. something else?

I know I can quite happily use ‘meta’ tables to allow me to add/edit data about the reserved records without acutally changing the core values, but stuck on which option to go for in order to prevent inadvertent meddling or deleting! What is everyone else doing?

Thanks

2

Hi,

In my experience it is best to do this using a flag in the database. I may also recommend that you set up a permissions table and a mapping table that map users to permissions (many2many). This gives you a greater granularity on the permissions for that user, which can include ‘do not edit’.

My 2 cents worth.

Steve

3

Hi Steve - thanks for replying. You mention that in your experience it is best to go for the ‘flag’ option. Do you have experience of the other methods and any pitfalls?

I should point out we have a permissions system in place for the editable records, but the non-editable records should have zero permissions for any user.

Thanks

4

Hi boatingcow,

RE:two tables per orginial table - one for reserved values and one for editable - perform a UNION for SELECTs
Each of your users is an entity in the database; your user entities have different editing states - some can and some cannot be edited; however each is a user. It:

[LIST=1]
[]It is generally recommended to keep entities together and use different strategies and behaviours on these entities to get desired behaviour.
[]SQL (UNION queries are expensive) and may lead to problems when your scope of users grow
[*]Using a flag (even if you do not need a permissions and mapping) can be returned with your user data without even requiring a JOIN and becomes a simple PHP check if set, not set or a value of 1 = do something, 2= do something else…
[/LIST]hard-code the never to be changed records as arrays/objects in the program code, in a configuration file or elsewhere

[LIST=1]
[]Avoid hardcoding at all costs. What happens if an administrator becomes a regular user or a regular user becomes another system user. If your logic works in a consistent way for all users and your filter behaviours then it is much more flexible to make changes to just the database values and not your production code.
[]When some of your application data is hardcoded and most the rest of it is in the database then in the future if others have to work on your application it becomes harder to work on - not as clear - and can cause development to branch more in a hardcoded manner to extend functionality for System users while standard application development for the rest. This is bad.
[*]You may like to think that your application will never involve other developers or will not grow that much. I have worked on too many systems where the companies though this was the going to be the case, and trying to explain that their design is unique and very surprise when I quote what it will take to get the app updated.
[/LIST]Hope this helps.
Steve

5

Thanks for taking the time to reply!

6

Put a study into permissions system doctrine. This is a rough description of what I’ve used for awhile. It’s overkill most of the time, but it works. but first, lets look at permissions in general.

A permission is an action. As in UNIX, there are essentially two actions against a record - read, write. UNIX also has execute, but that will never be relevant here. We can, probably should split this up more. I use CRUD – Create, Read, Update or Delete.

Permission can be dependent upon record organization. Again, from Unix we have user, group and world. This is a reasonable model.

In a database system it may become necessary to set permission on a field level. A billing officer might be able to see a patient’s billing information, but not their medical history details. A doctor may have reverse access. An office worker can see both. All of them can see the patient’s social security number, but only an administrator can edit it once committed. A problem is that sometimes data off the same table might have different permissions.

Writing a system to deal with this is not easy. I’ve done it partially, but the project was cancelled before completion for lack of funding.

As far as enforcing permissions, I was going to use Role Based Access Control (RBAC). In this approach users are mapped to roles, and roles are mapped to actions. Users can have one or more roles. Users are also mapped to groups, but groups address the question of record ownership, not permission (at least not directly - this will be clearer in a moment).

RBAC is pessimistic - that is it assumes the user does not have permission to perform an action. When an action is attempted the roles of the user are checked. If any of the roles says “yes” to the action and none of the roles says “no” the user can perform the action.

Each role has one of 5 settings for an action

Yes, unconditionally.
Yes, if a member of an owning group of the resource to be affected.
Yes, if the owner of the resource to be affected
Deny
No comment

When a role is first created it makes no comment on any action. You then map what the role can do. By mapping a user to multiple roles you can compose quite specific permissions for individuals.

A final piece to this puzzle though is account aliasing. If you have this complicated a permissions system eventually you will need to test the permissions of a user. Many programmers take a cheap route and establish a “skeleton key” password that can log into any account. DON’T DO THIS. The liability can be enormous. And there’s a better way.

When developing a system this complex develop a means for super users to masquerade as other users from their own account. This ‘account aliasing’ should be exactly like being logged in as the aliased user except two critical differences

  1. You can leave their account and return to your own at any time.
  2. When you perform an action, any audit logging in place still notes that you performed that action, aliased as another user. For example my system leaves the note “File modified by Michael Morris aliased as John Doe” instead of the normal “File modified by John Doe” note.

Aside from that you will see the system as the user you are aliased as sees it so that the permissions can be tested. Reason - even if you code RBAC correctly, it’s very easy to configure it wrong. It often won’t be the dev’s job to do such testing - rather the admins - but it is important.

7

[ot]Although boatingcow, was not asking about a permission system, I like your points Michael. I know the pain of writing an authentication/permission system as recently I had to write a commissioned application with total custom code no GLP or opensource license of any kind allowed. It took a lot of time, I use a lot of class based composition, working out security hashing, salting, all the security concerns, redirection, login attempt tracking, timeout and eventual lockout, Javascript, and database design and scripting, it was a lot of work, but they now have a very flexible role based permission system. If possible it is best to use opensource systems than reinvent this difficult wheel.

Regards,
Steve
[/ot]

8

RE: Michael’s post - some good points worth noting, however as Steve says, maybe a bit off topic. We already use a permissions system (ever evolving) which has roles & groups and affects permissions not only in a CRUD style, but also with row-level and column-level constraints. Although my question is concerned more with data normalisation, your last point Michael about ‘configuring it wrong’ is why I chose to post here in the first place. On one hand, I’m always keen to learn of a ‘best-practice’ or industry standard, but as ever, real systems sometimes require work-arounds and ‘bodges’ to bring them closer to how they should have been built to begin with! In any case, my question is more about removing the ability to edit a ‘row’ from an RBAC perspective entirely, so that permissions don’t come into the equation at all. I’d far prefer for a silent entity to exist somewhere, than have a permission with ‘DENY ALL’. The latter, as you point out, only requires an admin user’s configuration mismatch to ruin the part of the system which depends on there being an absolute rather than editable record.

9

I work with government systems now and so, for audit reason, “DELETE FROM…” and “UPDATE” never get executed without a record of the change being made. The auditors want to see everything so nothing truly leaves the system. I was planning on upgrading the system to completely harden the situation, so some of what I’ve designed might be of use to you. I haven’t written any implementation.

The basic idea is to create a history table that has all the fields of the main table except for how the keys work. The main table has the “id” of the row. The history table has a primary key “id” and a “recordid” foreign key. The changing of a record involves inserting a new record onto the history table, then running the UPDATE or DELETE as desired from the main table.

Hence, deleted records can be found by querying the history for records whose recordid is no longer on the main table. And any active record can have it’s whole edit history exposed with a single join. The datetime of the record can be used to sort the records by creation order.

Anyway, that’s sort of a number 4 selection. Returning to your original question

Non-editable users: system user, always at least one back office superuser, guest website user
Editable users: all other users

Guest can safely be hard coded. This should be the de facto behavior of the site. The superuser can be set from the config - vbulletin does it, or superuser is assumed to be user number 1 - this is how Joomla used to do it but I don’t think so anymore. If you do go the database config route make sure that prevent by hard code the accidental deletion (or demotion) of the last super user.

Non-editable automated emails: system confirmation email, system password reset email, etc

Editable emails: marketing emails, newsletter emails, ad-hoc emails

Non-editable core website pages: login page, “404” not found page
Editable pages: all other pages
[/quote]

My solution in PNL (or Gazelle) was to define these at the framework level, and then allow the project to have a specific version as an override. Delete the over-ride and the system falls back to the framework default version. All of these are going to use templates to some degree which will most likely be hard coded. Given, although all of these are hard coded, their content bodies aren’t. If someone deletes the content body of, say, the 404 page, then the system doesn’t break, it just goes to the default.