Ascertain access

[KillGorack]

Need some pointers.

User (usr), Application (app), and Permission (acs) tables.

Need an SQL to ascertain access levels for read, modify, and admin..

A record in asc is NOT guaranteed!

in the app table for example if ap_read = 1  then we need not a record in acs to read whatever is there. If however it was 0 we would need a record in asc with read = 1 for that app/usr combo

I currently open and query these three separately, and it works, but there has to be a more elegant way.

not EXACTLY normalized.. I guess. I hope the question is descriptive enough.

[KillGorack]

If it makes understanding it easier, here's my current solution..

          $sql = "SELECT 
              acs_read AS `read`,
              acs_modify AS `modify`,
              acs_administer AS `admin`
          FROM
              acs
          WHERE
              acs.acs_usr = :usr AND acs.acs_app = :app
          UNION SELECT 
              ap_read AS `read`,
              ap_modify AS `modify`,
              ap_administer AS `admin`
          FROM
              app
          WHERE
              app.ID = :app 
          UNION SELECT 
              acs_administer AS `read`,
              acs_administer AS `modify`,
              acs_administer AS `admin`
          FROM
              acs
          WHERE
              acs.acs_usr = :usr AND acs.acs_app = 3";
          $vars = array('app' => $kernel['app']['ID'], 'usr' => $kernel['ses']['usr']['ID']);
          $bits = $this->pdo->fetchdata($sql, 2, $vars);

          $row = array(
            'read' => max(array_column($bits, 'read')),
            'modify' => max(array_column($bits, 'modify')),
            'admin' => max(array_column($bits, 'admin')),
          );

The weird part at the end where I point to app 3; Three is the permissions app. If you have the admin bit set at one there for a user.. you have access to everything..

 

[gizmola]

It's not clear at all how/what you are trying to secure.   

Why do you have a separate app for "admin" purposes which requires a relation through acs?  What is the purpose of admin then?

   

[KillGorack]

1 hour ago, gizmola said:

It's not clear at all how/what you are trying to secure.   

Why do you have a separate app for "admin" purposes which requires a relation through acs?  What is the purpose of admin then?

   

I don't have a separate app for admin purposes. Admin is just a bit boolean variable that will let a user do administrative functions in an app.

• Read; read stuff
• modify; modify stuff like record editing / deleting
• admin; administrative functions like changing how data is presented to users.

These are just bits boolean variables..

Edited March 12, 2022 by KillGorack

[KillGorack]

It's kinda ugly but this works

SELECT
  GREATEST(
    COALESCE((SELECT acs.acs_read FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = :app), 0),
    COALESCE((SELECT app.ap_read FROM app WHERE app.ID = :app), 0),
    COALESCE((SELECT acs.acs_administer FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = 3), 0)
  ) AS `read`,
  GREATEST(
    COALESCE((SELECT acs.acs_modify FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = :app), 0),
    COALESCE((SELECT app.ap_modify FROM app WHERE app.ID = :app), 0),
    COALESCE((SELECT acs.acs_administer FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = 3), 0)
  ) AS `modify`,
  GREATEST(
    COALESCE((SELECT acs.acs_modify FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = :app), 0),
    COALESCE((SELECT app.ap_modify FROM app WHERE app.ID = :app), 0),
    COALESCE((SELECT acs.acs_administer FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = 3), 0)
  ) AS `admin`
from acs
WHERE acs.acs_usr = :usr

 

[kicken]

select 
    coalesce(acs.acs_read, app.ap_read) as read,
    coalesce(acs.acs_modify, app.ap_modify) as modify,
    coalesce(acs.acs_administer, app.ap_administer) as administer
from app 
left join acs on acs.acs_app=app.ID and acs.acs_usr=$user
where
    app.ID=$app

This will give you the value in acs if it exists.  If no row exists it will fall back to the value in app.

If a row in acs exists with ap_read=0, then the user would be denied read even if ap_read=1.  If that's not what you want you will need to adjust things.

Edited March 13, 2022 by kicken

[KillGorack]

14 hours ago, KillGorack said:

It's kinda ugly but this works

SELECT
  GREATEST(
    COALESCE((SELECT acs.acs_read FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = :app), 0),
    COALESCE((SELECT app.ap_read FROM app WHERE app.ID = :app), 0),
    COALESCE((SELECT acs.acs_administer FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = 3), 0)
  ) AS `read`,
  GREATEST(
    COALESCE((SELECT acs.acs_modify FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = :app), 0),
    COALESCE((SELECT app.ap_modify FROM app WHERE app.ID = :app), 0),
    COALESCE((SELECT acs.acs_administer FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = 3), 0)
  ) AS `modify`,
  GREATEST(
    COALESCE((SELECT acs.acs_modify FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = :app), 0),
    COALESCE((SELECT app.ap_modify FROM app WHERE app.ID = :app), 0),
    COALESCE((SELECT acs.acs_administer FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = 3), 0)
  ) AS `admin`
from acs
WHERE acs.acs_usr = :usr

 

Typo fixing this for future reference.. apologies!

 

SELECT

            GREATEST(

              COALESCE((SELECT acs.acs_read FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = :app), 0),

              COALESCE((SELECT app.ap_read FROM app WHERE app.ID = :app), 0),

              COALESCE((SELECT acs.acs_administer FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = 3), 0)

            ) AS `read`,

            GREATEST(

              COALESCE((SELECT acs.acs_modify FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = :app), 0),

              COALESCE((SELECT app.ap_modify FROM app WHERE app.ID = :app), 0),

              COALESCE((SELECT acs.acs_administer FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = 3), 0)

            ) AS `modify`,

            GREATEST(

              COALESCE((SELECT acs.acs_administer FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = :app), 0),

              COALESCE((SELECT app.ap_administer FROM app WHERE app.ID = :app), 0),

              COALESCE((SELECT acs.acs_administer FROM acs WHERE acs.acs_usr = :usr AND acs.acs_app = 3), 0)

            ) AS `admin`

from acs

WHERE acs.acs_usr = :usr

[gizmola]

I think you missed the point of my question.   You have a field named "acs_admininister in ACS and you have a field named "ap_administer" in app.  Then to top it all off, you state that you have magical app # 3, that you will also query all the time.

Why?  If a user is a "superadmin" who can just do everything, then why don't you just have a column in User named "is_super_admin"?  As it is now, you repeatedly and inefficiently are doing a query against a table that will hardly ever change for no reason.

As for an app, what is the purpose of ap_administer vs. a user with acs_administer?  Why do you have both columns?  

You have several major relational design mistakes, the most obvious being repeating groups.  You have employed repeating groups in both the ACS and the App table, which makes trying to optimize anything pretty much of a lost cause.  But you also have a design that does almost nothing for security, because you don't have anything that represents a feature to be secured.  I was hoping you might see the problem?  Yes you have a bunch of items that are another "repeating group" in app, although it is impossible to tell if any of those represent "features", but even if they did/do, there is nothing database driven that anyone can use to determine in even a semi-sophisticated way, whether or not someone should have access to do something in your system.

There is no granularization possible.  If I have "modify" does that mean I can add/edit/delete all my data?  What about other data?  Is there some data I should be able to add/edit, but other data I shouldn't be able to?  Maybe some things I should delete but other things I shouldn't be able to?  

You don't have a sql optimization problem, you have a database design problem.  You have an inept database design for security purposes, and you can combine and reduce the number of queries (which might save you a few hundred milliseconds), but that doesn't really change much internally from a database efficiency standpoint, and what is worse is that your security scheme does almost nothing useful, and doesn't make sense.   The code you build upon it, is not going to be valuable, maintainable or extensible. 

I know this is blunt, but I think the best value I could bring to you here, is to just be brutally honest.  

Here is an adequate normalized multi-system ACL schema.  It tries to maximize reuse across applications by having the main string keys (role name, access_name) in tables so that those standard names can be reused, which makes this look a little more complicated than it actually is.  The important things are that an app_access record is something to be secured for an app.  

 

 

These get grouped up at app setup by role.  So you configure all the accesses a non-authenticated user should be able to do see/do.  Then all the things a normal user should be able to see/do.  In normal operation functions are simple:

- Users get one or more roles assigned to them, based on whatever logic you desire.

-You query the database for a user user ->role(s) ->app->accesses

-You have simple unit testable function for access that only needs 3 things:

• app_id, user_id, access_name.name

Let's imagine that function was named userCanDo($appId, $userId, $accessName).

Your only "magical" access would be a bypass based on someone having a "SUPERADMIN" role, which just needs a "IS_SUPER_ADMIN" access for an app.  Access would boil down to something simple like:

 

if (userCanDo($appId, $userId, 'IS_SUPER_ADMIN') || userCanDo($appId, $userId, 'THING_TO_DO')) {
   // Proceed
} else {
   // Show Access Error
}

 

I don't expect you to adopt this system, but at very least you should be comparing the functionality, simplicity and configurability it provides, to what you are doing, and noticing the significant differences as well as missing pieces in yours.