Security groups slowing the api requests

👨‍💻 Development Help
1

Since adding security groups to my application the speeds have dropped drastically.

I have logged this query that seems to be 18 times just for a module to load with a load tie of 2s per query.

( SELECT acl_actions . * , acl_roles_actions . access_override , ? AS user_role FROM acl_actions INNER JOIN acl_roles_users ON acl_roles_users . user_id = ? AND acl_roles_users . deleted = ? LEFT JOIN acl_roles_actions ON acl_roles_actions . role_id = acl_roles_users . role_id AND acl_roles_actions . action_id = acl_actions . id AND acl_roles_actions . deleted = ? WHERE acl_actions . deleted = ? ) UNION ( SELECT acl_actions . * , acl_roles_actions . access_override , ? AS user_role FROM acl_actions INNER JOIN securitygroups_users ON securitygroups_users . user_id = ? AND securitygroups_users . deleted = ? INNER JOIN securitygroups_acl_roles ON securitygroups_users . securitygroup_id = securitygroups_acl_roles . securitygroup_id AND securitygroups_acl_roles . deleted = ? LEFT JOIN acl_roles_actions ON acl_roles_actions . role_id = securitygroups_acl_roles . role_id AND acl_roles_actions . action_id = acl_actions . id AND `acl_roles_actions

is it cause of hte permissions? why does this get called soo much? is there a caching option I need to set up?

2

If you can try a SQL EXPLAIN that might give you some clues.

Usually a bit of clever indexing takes you a long way.

3

We have addd the indexes the cause if hte function getUserActions

this is the problem. This is causing major delays has any one else experiences this on security groups and roles?

4

Can you see a logic behind the successive calls?

For example, a query to show a list box or subpanel with 6 elements, does it get 6 separate calls? Sometimes this is the bug, something that is getting called every iteration, when once could be enough

5

yeah its on every page its called multiple times. It seems to be trying to get the allowed actions for a role and user then determine from that list if they are allowed to do what they are trying ie view the listing page.

I really want to still use roles and security groups but why does a security group give such issues? what options are available to me?

6

I think I can see the logic issue


  if (!$refresh && !empty($_SESSION['ACL'][$user_id])) {
            if (empty($category) && empty($action)) {
                return $_SESSION['ACL'][$user_id];
            } else {
                if (!empty($category) && isset($_SESSION['ACL'][$user_id][$category])) {
                    if (empty($action)) {
                        if (empty($type)) {
                            return $_SESSION['ACL'][$user_id][$category];
                        }
                        return isset($_SESSION['ACL'][$user_id][$category][$type]) ? $_SESSION['ACL'][$user_id][$category][$type] : null;
                    } else {
                        if (!empty($type) && isset($_SESSION['ACL'][$user_id][$category][$type][$action])) {
                            return $_SESSION['ACL'][$user_id][$category][$type][$action];
                        }
                    }

                    $aclCatType = null;
                    if (isset($_SESSION['ACL'][$user_id][$category][$type])) {
                        $aclCatType = $_SESSION['ACL'][$user_id][$category][$type];
                    } else {
                        LoggerManager::getLogger()->warn('ACL Category Type is not set for user action');
                    }

                    return $aclCatType;
                } elseif (!empty($type) && isset($_SESSION['ACL'][$user_id][$category][$type][$action])) {
                    return $_SESSION['ACL'][$user_id][$category][$type][$action];
                }
            }
        }

It does not get added to the session