Access control list

Access control list

Access control list

An access control list (ACL), is a list of permissions assigned to a site user. An ACL specifies which users processes are granted access to objects, as well as what operations are allowed on given users. Each entry in a typical ACL specifies a subject and an operation. For instance, if a file object has an ACL that contains (Alice: read,write; Bob: read), this would give Alice permission to read and write the file and Bob to only read it.

Implementations

Filesystem ACLs

A filesystem ACL is a data structure (usually a table) containing entries that specify individual user or group rights to specific system objects such as programs, processes, or files. These entries are known as access control entries (ACEs) in the Microsoft Windows NT, OpenVMS, Unix-like, and Mac OS X operating systems. Each accessible object contains an identifier to its ACL. The privileges or permissions determine specific access rights, such as whether a user can read from, write to, or execute an object. In some implementations, an ACE can control whether or not a user, or group of users, may alter the ACL on an object.

Networking ACLs

On some types of proprietary computer hardware (in particular routers and switches), an Access Control List refers to rules that are applied to port numbers or IP Addresses that are available on a host or other layer 3, each with a list of hosts and/or networks permitted to use the service. Although it is additionally possible to configure Access Control Lists based on network domain names, this is generally a questionable idea because individual TCP, UDP, and ICMP headers do not contain domain names. Consequently, the device enforcing the Access Control List must separately resolve names to numeric addresses. This presents an additional attack surface for an attacker who is seeking to compromise security of the system which the Access Control List is protecting. Both individual servers as well as routers can have network ACLs. Access control lists can generally be configured to control both inbound and outbound traffic, and in this context they are similar to firewalls. Like Firewalls, ACLs are subject to security regulations and standards such as PCI DSS.

Database implementations

ACL algorithms have been ported to SQL and relational database systems. All database based systems, like Enterprise resource planning and Content management systems, have used ACL model in their administration modules.

Here I will develop an ACL set with database implementation for a module based vendor and consumer request manager system developed in codeignitor. The system have “Tabs” and inside Tabs there are links to “Modules” of the system according to the user type either vendor or consumer. The users are provided the access to modules and tabs based on the user type and role. The tabe are visible if the user has access to any of the module related to the tab. Further the module access is checked again in each module separately.

Database Schema

Table Tabs

In this table we will store all the tabs we have in the system like Consumers, Requests, Admin, Manage, Settings, Reports etc.

CREATE TABLE tabs (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  parent_id int(11) NOT NULL DEFAULT '0' COMMENT 'Parent if the tab is a child',
  machine_name varchar(20) NOT NULL COMMENT 'Machine Name',
  `name` varchar(20) NOT NULL COMMENT 'Tab Name',
  weight int(11) NOT NULL COMMENT 'Sort Order ',
  `status` enum('Active','Inactive','Deleted') NOT NULL DEFAULT 'Active' COMMENT 'Active Status',
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time when Tab is created',
  created_by_id int(11) NOT NULL COMMENT 'Who created this row',
  updated timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Time when this is updated',
  updated_by_id int(11) NOT NULL COMMENT 'who updated this row',
  PRIMARY KEY (id),
  UNIQUE KEY machine_name (machine_name),
  KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Table to store request types';
The structure of the above table structure is as
id Primary Key
parent_id If the tab is a child of other tab
machine_name will be unique and will used in the system to process on this row
name the display name of the tab
weight the sort order of the tab
status Active
created time stamp auto filled when each tab row is inserted
created_by_id the user id (super admin)
updated timestamp of update
updated_by_id user id of the user who updated this row if any
tabs-schema

tabs-schema

Module Table

In this table we will store our developed modules of the system like Requests, Roles, Permissions, Consumer, Organizations, Locations, Users, Customers etc.

CREATE TABLE modules (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  parent_id int(11) NOT NULL DEFAULT '0',
  tab_id int(11) NOT NULL COMMENT 'Tab ID',
  machine_name varchar(20) NOT NULL COMMENT 'Machine Name',
  `name` varchar(20) NOT NULL COMMENT 'Request Type Name',
  weight int(11) NOT NULL COMMENT 'Sort Order ',
  `status` enum('Active','Inactive','Deleted') NOT NULL DEFAULT 'Active' COMMENT 'Active Status',
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time when Request type is created',
  created_by_id int(11) NOT NULL COMMENT 'Who created this row',
  updated timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Time when this is updated',
  updated_by_id int(11) NOT NULL COMMENT 'who updated this row',
  PRIMARY KEY (id),
  UNIQUE KEY machine_name (machine_name),
  KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Table to store request types';
The structure of the above table structure is as
id Primary Key
parent_id If the module is a child of other module
tab_id foriegn key relation to tabs table (which tab the module falls in)
machine_name will be unique and will used in the system to process on this row
name the display name of the module
weight the sort order of the module
status Active
created time stamp auto filled when each module row is inserted
created_by_id the user id (super admin)
updated timestamp of update
updated_by_id user id of the user who updated this row if any
modules-schema

modules-schema

Roles Table
In this table we will store different roles for users like Application Owner, Super Admin, Admin, Consumer, Vendor, Staff etc.

CREATE TABLE roles (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  machine_name varchar(20) NOT NULL COMMENT 'Machine Name',
  `name` varchar(20) NOT NULL COMMENT 'Role Name',
  description text COMMENT 'Role Description',
  weight tinyint(4) NOT NULL COMMENT 'Priority Order ',
  `status` enum('Active','Inactive','Deleted') NOT NULL DEFAULT 'Active' COMMENT 'Active Status',
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time when Role was created',
  created_by_id int(11) NOT NULL COMMENT 'Who created this row',
  updated timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Time when this is updated',
  updated_by_id int(11) NOT NULL COMMENT 'who updated this row',
  PRIMARY KEY (id),
  UNIQUE KEY machine_name (machine_name),
  KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Table to store request types';
The structure of the above table structure is as
id Primary Key
machine_name will be unique and will used in the system to process on this row
name the display name of the Role
description Role Details
weight the sort order of the tab
status Active
created time stamp auto filled when each tab row is inserted
created_by_id the user id (super admin)
updated timestamp of update
updated_by_id user id of the user who updated this row if any
roles-schema

roles-schema

Permissions Table
In this table we will store different permissions based on our developed modules like Manage Organizations, Manage Users, Add Staff, Generate QR Code, Impersonate User etc.

CREATE TABLE permissions (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  tab_id int(11) NOT NULL COMMENT 'Tab ID ',
  module_id int(11) NOT NULL COMMENT 'Module ID',
  machine_name varchar(255) NOT NULL COMMENT 'Machine Name',
  `name` varchar(255) NOT NULL COMMENT 'Permission Name',
  user_types varchar(255) NOT NULL COMMENT 'The user type for which the permissions is defined',
  description text COMMENT 'Description',
  `status` enum('Active','Inactive','Deleted') NOT NULL DEFAULT 'Active' COMMENT 'Active Status',
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time when row is created',
  created_by_id int(11) NOT NULL COMMENT 'Who created this row',
  updated timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Time when this is updated',
  updated_by_id int(11) NOT NULL COMMENT 'who updated this row',
  PRIMARY KEY (id),
  UNIQUE KEY machine_name (machine_name),
  KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Table to store request types';

Each permission is associated with a module and inturn the module is associated with the tab. In the above table I have added the tab_id and module_id also with permission to save database joins. (Denormalized the table). I have also saved the user type in the table to properly manage the permissions based on the user type. This will also help to easily map these permission with roles. I will explain this in my next post.

The structure of the above table structure is as
id Primary Key
tab_id the tab id in which the module falls
module_id the module id in which the permission falls
machine_name will be unique and will used in the system to check the permission
name the display name of the permission
user_type the user type for which the perission is
description Role Details
weight the sort order of the tab
status Active
created time stamp auto filled when each tab row is inserted
created_by_id the user id (super admin)
updated timestamp of update
updated_by_id user id of the user who updated this row if any
permissions-schema

permissions-schema

Roles and Permissions Mapping Table
In this table we will store roles and permissions mapping. This role will tell us which role have what set of permissions.

CREATE TABLE roles_permissions (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  role_id int(11) NOT NULL COMMENT 'Role ID',
  permission_id int(11) NOT NULL COMMENT 'Permission ID',
  permission varchar(255) NOT NULL COMMENT 'Permission Machine Name',
  tab varchar(255) NOT NULL COMMENT 'Tab Machine Name',
  module varchar(255) NOT NULL COMMENT 'Module Machine Name',
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time when Row is created',
  created_by_id int(11) NOT NULL COMMENT 'Who created this row',
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Table to store request types';

Each permission is associated with a module and inturn the module is associated with the tab. In the above table I have added the tab_machine_name and module_machine_name also with permission_machine_name to save database joins. (Denormalized the table).

The structure of the above table structure is as
id Primary Key
role_id the mapped role with this permission
permission_id the permission id
permission permision machine name (this will be used to check for particlular permission)
tab the tab machine name (this will be used to check tab permission)
module the module machine name (this will be used to check module permission)
status Active
created time stamp auto filled when each tab row is inserted
created_by_id the user id (super admin)
updated timestamp of update
updated_by_id user id of the user who updated this row if any
role_permission_mapping

role_permission_mapping

role_permission_schema

role_permission_schema

User Roles Table
This table is used to store user multiple roles.

CREATE TABLE users_roles (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  user_id int(11) NOT NULL COMMENT 'User ID',
  role_id int(11) NOT NULL COMMENT 'Role ID',
  `status` enum('Active','Inactive','Deleted') NOT NULL DEFAULT 'Active' COMMENT 'Active Status',
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time when row is created',
  created_by_id int(11) NOT NULL COMMENT 'Who created this row',
  updated timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Time when this is updated',
  updated_by_id int(11) NOT NULL COMMENT 'who updated this row',
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Table to store request types';
The structure of the above table structure is as
id Primary Key
role_id the user role
user_id the user id
status Active
created time stamp auto filled when each tab row is inserted
created_by_id the user id (super admin)
updated timestamp of update
updated_by_id user id of the user who updated this row if any
role-roles-schema

role-roles-schema

In the next post I will explain the implementation of this ACL with Codeignitor.


Leave a Reply

Your email address will not be published. Required fields are marked *