/* Here are the tables that make up the view */ CREATE TABLE `cans_agency_personnel_profile` ( `person_id` int(11) NOT NULL, `username` varchar(40) default NULL, `password_hint_question` varchar(100) default NULL, `password_hint_response` blob, `status_flg` char(1) NOT NULL default 'A', `reset_password` tinyint(4) NOT NULL default '1', `updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP, `updated_by` int(11) NOT NULL, PRIMARY KEY (`person_id`), KEY `IDX_FK_cans_agency_personnel_profile` (`person_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `cans_app_user_role` ( `person_role_id` int(11) NOT NULL auto_increment, `role_id` int(11) NOT NULL, `person_id` int(11) NOT NULL, `updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP, `updated_by` int(11) NOT NULL, PRIMARY KEY (`person_role_id`), KEY `IDX_FK_cans_agency_personnel_app_user_role` (`person_id`), KEY `IDX_FK_cans_app_role_app_user_role` (`role_id`) ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 CREATE TABLE `cans_app_role` ( `role_id` int(11) NOT NULL auto_increment, `role_name` varchar(100) default NULL, `role_descr` text, `app_name` varchar(40) NOT NULL, PRIMARY KEY (`role_id`), KEY `IDX_FK_cans_app_config_cans_app_user_role` (`app_name`) ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 CREATE TABLE `cans_app_role_permission` ( `role_perm_id` int(11) NOT NULL auto_increment, `role_id` int(11) NOT NULL, `permission_id` int(11) NOT NULL, `updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP, `updated_by` int(11) default NULL, PRIMARY KEY (`role_perm_id`), KEY `IDX_FK_cans_app_role_permission` (`role_id`), KEY `IDX_FK_cans_role_app_permission` (`permission_id`) ) ENGINE=MyISAM AUTO_INCREMENT=98 DEFAULT CHARSET=latin1 CREATE TABLE `cans_app_permission` ( `permission_id` int(11) NOT NULL auto_increment, `permission_name` varchar(100) default NULL, `permission_description` text, PRIMARY KEY (`permission_id`) ) ENGINE=MyISAM AUTO_INCREMENT=33 DEFAULT CHARSET=latin1 /* This is the result of a SHOW CREATE VIEW user_role_permission_view */ CREATE ALGORITHM=UNDEFINED DEFINER=`steve`@`localhost` SQL SECURITY DEFINER VIEW `user_role_permission_view` AS select (case when isnull(`perm`.`permission_id`) then concat(`profile`.`person_id`,_latin1'_',`role`.`role_id`) else concat(concat(`profile`.`person_id`,_latin1'_',`role`.`role_id`),_latin1'_',`perm`.`permission_id`) end) AS `id`,`profile`.`person_id` AS `person_id`,`profile`.`username` AS `username`,`role`.`role_id` AS `role_id`,`role`.`role_name` AS `role`,`perm`.`permission_id` AS `permission_id`,`perm`.`permission_name` AS `permission`,`role`.`app_name` AS `app` from ((((`cans_agency_personnel_profile` `profile` join `cans_app_user_role` `userrole` on((`userrole`.`person_id` = `profile`.`person_id`))) join `cans_app_role` `role` on((`role`.`role_id` = `userrole`.`role_id`))) left join `cans_app_role_permission` `rolperm` on((`rolperm`.`role_id` = `role`.`role_id`))) left join `cans_app_permission` `perm` on((`perm`.`permission_id` = `rolperm`.`permission_id`))) where (`profile`.`status_flg` = _latin1'A') order by `profile`.`person_id`,`role`.`app_name`,`role`.`role_name`,`perm`.`permission_name` /* Here is the SQL we used to initially create the view */ create or replace view user_role_permission_view as select case when perm.permission_id is null then concat(profile.person_id,'_',role.role_id) else concat(concat(profile.person_id,'_',role.role_id), '_', perm.permission_id) end as id, profile.person_id as person_id, profile.username as username, role.role_id, role.role_name as role, perm.permission_id, perm.permission_name as permission, role.app_name as app from cans_agency_personnel_profile profile join cans_app_user_role userrole on (userrole.person_id = profile.person_id) join cans_app_role role on (role.role_id = userrole.role_id) left outer join cans_app_role_permission rolperm on (rolperm.role_id = role.role_id) left outer join cans_app_permission perm on (perm.permission_id = rolperm.permission_id) where profile.status_flg = 'A' order by person_id, app, role, permission;