Zend Framework 2 with zfc-rbac database population

Question

There are numerous 'getting started' tutorials out there on how to implement zfc-user and zfc-rbac into Zend Framework 2. The github pages for zfc-user and zfc-rbac (https://github.com/ZF-Commons) are clear and the implementation is indeed pretty easy (as stated on many of the tutorials). I also found the SQL schemes which are needed for both zfc-user and zfc-rbac (/vendor/zf-commons/zfc-[user/rbac]/data/).

The creation of a user into the database is easy, since zfc-user already sets this up for you (http://example.com/user). Everything fine so far. Now I want to populate the roles, but it's not clear to me on how to populate the rbac tables correctly. The lack on information about this surprises me, since the zfc-rbac component is a popular module for the Zend Framework.

I understand the principal of Role Based Access Control and the population of the tables for the permissions and the table linking the permissions and roles together are clear, it's the role table that's not clear to me. I understand that you can have a role which has a parent role, but it's not clear how to populate the table with a parent role since there is a foreign key constraint which states the 'parent_role_id' has to be a 'role_id'.

Below is the SQL for the role table (this is the SQL provided by zfc-rbac):

CREATE TABLE `rbac_role` (
  `role_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parent_role_id` int(11) unsigned NOT NULL,
  `role_name` varchar(32) NULL,
  PRIMARY KEY (`role_id`),
  KEY `parent_role_id` (`parent_role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

ALTER TABLE `rbac_role`
  ADD CONSTRAINT `rbac_role_ibfk_1` FOREIGN KEY (`parent_role_id`) REFERENCES `rbac_role` (`role_id`);

With the foreign key in place adding a parent role seems impossible?

INSERT INTO `rbac_role` (parent_role_id, role_name) VALUES (NULL, 'admin');

Basically my question is (and I feel very stupid for asking this) but how does an insert for a parent role look like? And if the insert statement I presented is in fact correct, do I always need to remove the foreign key before inserting a parent role?

I'm no expert on SQL, but this self-referenced-association seems to be false. The parent_role_id HAS to be allowed to be NULL, because otherwise you'll NEVER be able to insert a first data-row. — Sam
That's what I thought. Thank you. It is strange though that zfc-rbac provides the create table statement WITH that foreign key, they should either explain more or leave it out because this is kind of confusing. — Lennart Weijl
Already issued a pull request for this. Certainly this is a bug which simply has been overlooked. Zf-Commons contributors are normal developers that do mistakes, too :) — Sam

chrislondon chrislondon · Accepted Answer · 2013-05-25T16:46:20

Change your create table to the following:

CREATE TABLE `rbac_role` (
    `role_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `parent_role_id` int(11) unsigned NULL,
    `role_name` varchar(32) NULL,
    PRIMARY KEY (`role_id`),
    KEY `parent_role_id` (`parent_role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Notice that parent_role_id is NULL instead of NOT NULL. If parent_role_id is NOT NULL then that means that it has to have a parent but since the foreign key reference is to the same table there is no way to insert a parent row!

Zend Framework 2 with zfc-rbac database population

2 Answers