I will preface this by warning everyone that I am an ametuer at this so there are likely more efficient ways to accomplish this but it has so far worked well for my application so maybe it will help someone else.
As I have been developing an application for work using PHPR v11, I found that several of the pages that I present to users are based on views or other related tables. One problem I ran into was it was easy to forget to assign permissions to the related views so that users could have appropriate access. If I can't remember to get the permissions correct, I cannot expect someone unfamiliar with the DB structure to know which tables are related to which views and require specific permissions. To solve this problem, I created a database table to store a 'Base Table' and 'Target Tables'. Any permissions assigned to the user for the Base Table will be programmatically assigned to Target Tables for the logged in user after a successful login.
CREATE TABLE `sys_PermissionExtension` (
`idsys_PermissionExtension` int(11) NOT NULL AUTO_INCREMENT,
`baseTable` varchar(100) NOT NULL,
`targetTables` text NOT NULL,
`CreatedBy` varchar(45) DEFAULT NULL,
`CreatedDate` datetime DEFAULT NULL,
`Notes` text DEFAULT NULL,
PRIMARY KEY (`idsys_PermissionExtension`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;The baseTable is a single tablename from the PHPR project. The taretTables field is a comma-separated list. For example, if your user group has Add/View permissions for the orders table but you also have two related views used on dashboards, if you forget to assign permission for the dashboard views, they will be blank. By adding orders as the baseTable and dashvieworders1,dashvieworders2 as the targetTables, the dashboard views will be appropirately visible to the user.
I have the below function in an external PHP file that is included in the project and is called in the 'AfterSuccessfulLogin' event by simply adding applyPermissionExtensions() but I imagine you could just put the code in the event itself. I separated it so I can expermient with calling it from different places or at different times in the application lifecycle.
<?php
/*==================================================================
Often, there are multiple tables/views associated with a base
table. To avoid end users being required to assign appropriate
permissions for each item, tables can be linked. This function
duplicates the base table permissions to the target tables.
==================================================================*/
function applyPermissionExtensions() {
// Get all permission extension rules from database
$rs = DB::Query("SELECT baseTable, targetTables FROM sys_PermissionExtension");
$allExtensions = [];
//Without this block, fetchAssoc only gets the first record
while ($row = $rs->fetchAssoc()) {
$allExtensions[] = $row;
}
foreach ($allExtensions as $extension) {
try {
// Get permissions from base table
$rights = Security::getPermissions($extension['baseTable']);
// Process simple comma-separated list
$targetTables = array_map('trim',
explode(',', $extension['targetTables'])
);
// Remove empty entries and apply permissions
foreach ($targetTables as $table) {
if (!empty($table)) {
Security::setPermissions($table, $rights);
}
}
} catch (Exception $e) {
// Log error but continue with other extensions
//Insert your own error logging
continue;
}
}
} //************END applyPermissionExtensions
?>I am not yet running this in production but it has so far been successful in my Sandbox environment. The above table must be included in the project for the code to function but I would not suggest making it accessible to the end user Admin group. During development, I created Add/Edit pages for this system table but before uploading it for production, those pages will be removed from the project to prevent even Admins from editing these relationships.
I hope this all makes sense. I am always open to feedback or ideas to extend functionality or security. Happy coding!