Part 1 – Creating a permission structure for a PHP/MySQL SAAS application

by | Feb 1, 2014 | General IT, IT, MySQL, PHP, Programming, SAAS (Software as a Service), Webtech

After some work I have changed the way I am going to do permissions. For more information check this post.

So I am working on a Software as a service application (SAAS) and one of the major feature requests have been to be able to grant specific permissions into the site.

For example.

  • A user who can only see the public access areas.
  • A user who can view the weekly timetables but not edit classes etc.
  • A user who can look after a specific location (maintain the master timetable, create classes at that location, edit classes at that location etc)

This presents a real challenge, especially since my previous applications have been based upon either a user is an admin or not. (all or nothing), or the old number system where 0 is the highest level permission working the way down to 9 being the lowest level permission. (as you work your way up the scale more features become unlocked but not able to grant specific access to anything)

So after much thought I have worked out a solution which I hope will work. (We will find out over the next few days)

I have grouped features into areas that I believe are suitable permission areas. Some of these include

  • View the weekly timetable
  • View the master timetable
  • Manage the master timetable (Edit, create and remove master time table entries and create terms for the location)
  • Class management (Edit, create and remove classes)
  • User Management (able to view, edit create and delete user accounts)
  • Reports
  • Site Settings
  • Holiday Management
  • Location Management
  • Public Access.

There are a few others but that gives a good idea. Especially since some of the ones listed above overlap.

So to get started I have created a database with enough columns for there are permissions areas. (This can be extended later as more features come online and the structure will be easy to update and manage)

Here is the SQL script to create the database.

CREATE TABLE IF NOT EXISTS `permissions` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `location` varchar(1) NOT NULL,
  `view_weekly_timetable` varchar(1) NOT NULL,
  `view_master_timetable` varchar(1) NOT NULL,
  `manage_master_timetable` varchar(1) NOT NULL,
  `class_management` varchar(1) NOT NULL,
  `instructor_management` varchar(1) NOT NULL,
  `student_management` varchar(1) NOT NULL,
  `user_management` varchar(1) NOT NULL,
  `reports` varchar(1) NOT NULL,
  `site_settings` varchar(1) NOT NULL,
  `holiday_management` varchar(1) NOT NULL,
  `location_management` varchar(1) NOT NULL,
  `public_access` varchar(1) NOT NULL,
  `invoice_management` varchar(1) NOT NULL,
  `notices_management` varchar(1) NOT NULL,
  PRIMARY KEY (`id`)

As you can see from the above script there is no user ID in that table. The reason for this is that the user account will be associated with the permission table via the association table. This will allow permissions to be added or removed without having to update the entire site when a user account is created or edited.

To split the permissions into particular sites each account will have as many rows as there is locations (i.e. 1 and 3 in this example) and 1 row for master (with a location of m).

Each location will have entries in the appropriate columns and skip the master columns and the opposite for the master set (this is common to the site and not a location and will become clearer as the structure unfolds)

That is where I will leave it for now. Next time I will try and talk about how I am going to get the permissions out and into a useful format for each page load.