CakePHP ACL and Auth Tutorial: Database Setup

View full index of ACL Tutorial Articles

This article describes the initial steps used to created the sample website I created to demonstrate real-world usage of CakePHP and the Auth and ACL Components.

Get the source code for that sample website here.

This is more of a practical step-by-step tutorial than my previous tutorials on the ACL Component. I recommend reading those articles as well as the official Cake documentation if you want a broader overview of what ACL is.

This part of the tutorial will show the steps required to create and configure some basic ACL and Auth related elements in your database. I should point out from the start that this configuration will only work with the Auth Component in ‘crud’ mode. Although, the alternative ‘actions’ mode isn’t totally incompatible, you should read more about the differences between the two modes before creating your real application:

• aros, aros_acos, and acos tables used by the ACL Component
• a table to store names of groups
• a table in to store usernames and passwords and a column value to connect each user to a group
• an aros table populated with values that correspond to values in the group and user tables
• an acos table populated with rows representing your app’s controllers
• a superuser who will have all access to all pages in the site

Obviously, you first need to have a working install of the latest nightly build of Cake. Also, take the time to make sure you are familiar with how to use the cake console.

1. Initialize ACL Component DB Tables

On the command line, navigate to your app’s root directory (usually one step up from the app directory), and run the following command:

cake schema run create DbAcl

If this command runs with success, it will create 3 tables in your database. Here are the MySQL schema for these tables.

2. Create tables for Users and Groups

The application I am creating will organize it’s users into groups. Most permissions will be assigned to groups, and users will inherit access permissions based on their group membership. Exceptions will be possible, so that in some cases a user might get permission to something that other users in the same group do not have access to.

To make this possible, we will create the database tables which will hold data for Users and Groups. Users will be assigned group membership through a foreign key ‘group_id’.

In the case of this application, I will store the user’s login name in a field called “username” and store the password in a field called “passwd”. I am making the field length of “passwd” 255 characters long, because it will be holding a hashed version of the user’s password. By default this will be an sha1 hash, but PHP 5 allows even more secure hashes like SHA256 and Whirlpool, so I am making it extra long just in case.

CREATE TABLE `users` (                                                                
`id` int(10) unsigned NOT NULL auto_increment,                                      
`username` varchar(50) NOT NULL default '',                                         
`passwd` varchar(255) NOT NULL default '',                                          
`name` varchar(50) NOT NULL default '',                                             
`email` varchar(100) NOT NULL default '',                                                                 
`group_id` int(10) unsigned NOT NULL default '0',                                   
`active` tinyint(1) unsigned NOT NULL default '0',                                            
`created` datetime NOT NULL default '0000-00-00 00:00:00',                          
`modified` datetime NOT NULL default '0000-00-00 00:00:00',                         
PRIMARY KEY  (`id`),                                                                
UNIQUE KEY `username` (`username`),                                                                                             
KEY `group_id` (`group_id`)                                                         
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `groups` (                                                               
`id` int(10) unsigned NOT NULL auto_increment,                                      
`name` varchar(50) NOT NULL default '',  
`parent_id` int(10) default '0',                                                    
`created` datetime NOT NULL default '0000-00-00 00:00:00',                          
`modified` datetime NOT NULL default '0000-00-00 00:00:00',                         
PRIMARY KEY  (`id`)                                                                 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

4. Create Starter Groups and a Superuser

The following MYSQL commands will manually create your intial groups:

INSERT INTO `groups` SET 
    `id` = 1, 
    `name` = 'users', 
    `parent_id` = null;
INSERT INTO `groups` SET 
    `id` = 2, 
    `name` = 'editors', 
    `parent_id` = 1;
INSERT INTO `groups` SET 
    `id` = 3, 
    `name` = 'superusers', 
    `parent_id` = 2;

To manually create a user with a password, you will need to get the security salt used by your application. Look into your app/config/core.php file and get the salt value from a line like this:

Configure::write('Security.salt', 'welcometomykitchen');

In this case the salt is “welcometomykitchen”. Your salt should be randomized and unique. This salt is combined with the user’s password string for extra security. So, to manually create the first superuser for your site, keep the salt handy.

The following will create a superuser for your site. Use your own salt value in place of mine, and add your own email address. The code below will give the ‘pherme’ the password of ‘ILikeCake’.

INSERT INTO `users` SET 
    `id` = 1, 
    `username` = 'pherme', 
    `passwd` = SHA1("welcometomykitchenILikeCake"), 
    `name` = 'Pierre Herme', 
    `email` = 'pherme@nonexistant.com', 
    `group_id` = 3, 
    `active` = 1;

5. Add The Groups and Superuser to ARO Tree

Now that we have the basic database tables set up, we can build our intial ARO Tree. This tree will allow us to assign permissions in such a way to allow an efficient use of inheritance. Both groups and users will be in the tree. However, as mentioned above, most permissions will ultimately be assigned to groups, with users inheriting most of their rights from the group they belong to.

After this intial setup, the ACL Behavior will manage this type of synchronization between the aros, users, and groups tables.

Here are the ACL commands to set up your groups and one user:

cake acl create aro root users
cake acl create aro users editors
cake acl create aro editors superusers
cake acl create aro superusers pherme

For the first group ‘users’ I am using the magic parent “root”, which gives the ARO node a parent_id value of null.

If you do:

cake acl view aro

You should now see something like this:

[1]users
    [2]editors
        [3]superusers
            [4]pherme

This indicates that editors will inherit permissions from users, and so on down the tree.

The Cake Console only creates alias values in the aros table. In order for the ACL Behavior to work, however, the rows of the aros table have to have ‘model’ and ‘foreign_key’ values properly set.

The ‘model’ field should correspond to the CamelCase name of the model which the record belongs to — either Group or User, while the ‘foreign_key’ field represents the id of that record. Again, ACL Behavior will take care of this later on, but for now we will manually create them with the following MySQL commands:

UPDATE `aros` SET 
    `model` = 'Group', 
    `foreign_key` = 1 
    WHERE `alias` = 'users';
UPDATE `aros` SET 
    `model` = 'Group', 
    `foreign_key` = 2 
    WHERE `alias` = 'editors';
UPDATE `aros` SET 
    `model` = 'Group', 
    `foreign_key` = 3 
    WHERE `alias` = 'superusers';
UPDATE `aros` SET 
    `model` = 'User', 
    `foreign_key` = 1 
    WHERE `alias` = 'pherme';

6. Create Basic ACO Elements

Before assigning permissions, we must also have some values in the acos table.

This table should contain a list of entries with alias values that correspond to CamelCase names of your application’s controllers. It is also helpful to organize these under a parent node, which I call “Site”.

cake acl create aco root Site

In this example, we will eventually have controllers named Users, Usermanager, Groups, Articles and SecretRecipes, so we need to add corresponding ACO nodes:

cake acl create aco Site Users
cake acl create aco Site Usermanager
cake acl create aco Site Groups
cake acl create aco Site Articles
cake acl create aco Site SecretRecipes

Note how all of these have the parent of Site.

7. Assigning Permissions

First lets give full access to our superusers group. We do this with the following command. By granting permission to the parent ACO named ‘Site’, the superusers will continue to have all permissions on any future ACO created whose parent is Site.

cake acl grant superusers Site *

All our users should get ‘read’ access on all acos:

cake acl grant users Site read

Except that we actually want to block users from accessing Usermanager:

cake acl deny users Usermanager *

8. “Check” Your Work

Let’s make sure everything was set up correctly by running the check command (the expected responses are indicated by the arrow):

cake acl check pherme Articles create
		--> pherme is allowed

cake acl check pherme Usermanager delete
	    --> pherme is allowed

cake acl check editors Articles read
	    --> editors is allowed

cake acl check users Usermanager create
	    --> users is not allowed

cake acl check users Usermanager read
	    --> users is not allowed

In Conclusion

Spend some time now taking a look at your database tables. I have already written an example of how to dissect these tables. Take some time to understand the way the lft and rght values are used to represent the permission trees in BOTH the acos and aros tables. Also take a look at the aros_acos table and spend some time understanding how the permissions are granted and denied through the use of the ‘_create’, ‘_read’, ‘_update’, and ‘_delete’ columns.

You now have a good foundation in your database from which to now build out your application with more users and more permissions.

Again, to skip some steps and just see what all this looks like as a final website:
download the source code here

[EDIT: May 26, 2008] Made references to sample site source code.

Sunday May 18, 2008