Postgresql – How to avoid large joins when determining which rows a user is authorized for

access-controlauthorizationjoin;performancepostgresql

Typical wep app situation: you have logged in users querying for resources exposed through an API. As a hypothetical example, company administrators quering for their employees' time sheets:

company <- employees <- time_sheets
company <- admin

Say I wanted to get all the time sheets that I'm authorized for/I have access to as an admin of that company.

Without any optimizations, in the above I'd have to join admin with company with employees with time_sheets to determine all the time_sheets I can look at. You can see how for a wider schema the joins could add up very fast. I realize that in modern databases joins are ludicrously fast, but I'm guessing this is still a case you don't want to completely ignore.

Is there a good solution to this? One could potentially have a separate admin to time_sheets many to many join table just to speed-up those queries, but then maintaining it might be tricky.

Any suggestions?

Best Answer

Like most things it's all about trade-offs. You can denormalize, maintain materialized views, etc, but all that has its own costs. It really depends on workload, query patterns, response time priorities, maintenance burden you're willing to bear, etc.

In general I'd recommend starting with the properly normalized design and join chain, but doing it via views for common queries. This allows you to later replace the view with a trigger-maintained materialized view, to selectively denormalize the schema, etc, without messing with the app's queries.

Don't try to fix it until you know it's actually a problem.

Related Solutions

SQL Server 2008 – How to Find User Access to Tables and Views

You can use the function fn_my_permissions and impersonate the user to see which objects they have access to. You may want to expand the types to procedures and the various types of functions...

 USE Your_Database;
 GO
 EXECUTE AS USER = N'the_user_name';
 GO
 SELECT 
    s.name,
    o.name,
    p.[permission_name]
 FROM sys.objects AS o 
 INNER JOIN sys.schemas AS s
 ON o.[schema_id] = s.[schema_id]
 CROSS APPLY sys.fn_my_permissions(QUOTENAME(s.name) 
   + N'.' + QUOTENAME(o.name), N'OBJECT') AS p
   WHERE o.[type] IN (N'U', N'V') -- tables and views
   AND p.subentity_name = N''; -- ignore column permissions 
 GO
 REVERT;

Only tested on compat level 100+; you may need to structure that slightly differently in older compatibility modes (see the first example here).

Since your actual role has been granted a bunch of implicit access to tables through authorization, perhaps try:

-- all the tables and views in the system
SELECT 
  schemaName = s.name,  
  objectName = o.name, 
  o.[object_id] 
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
WHERE type IN (N'U',N'V')
AND NOT EXISTS
(
  -- except those that have been *explicitly* denied
  SELECT 1
  FROM sys.database_permissions AS p
  WHERE p.class = 1
  AND p.state_desc = N'DENY'
  AND p.major_id = o.[object_id]
  AND p.grantee_principal_id IN (USER_ID(N'db_finrep_deny'),USER_ID(N'finrep'))
);

DB2 Express User Rights – How to Grant Rights for a User

DB2 will by default look for the group information in the same directory where the user is defined. For a domain user that would be the domain itself. If a domain user is a member of a local group, after setting DB2_GRP_LOOKUP=local make sure to restart the DB2 instance.

If you have enabled extended security (which is the default installation option), you may want to try adding your ID to the DB2USERS group.

Since the DB2 instance will need to perform the domain directory lookup when authenticating domain users, the account that runs the DB2 instance service must have sufficient permissions to perform the AD lookup. For example, Local Service authority would not be able to do that.

Each DB2 instance service needs such authority. There are other DB2-related services (DB2 DAS, DB2 Remote Command Server, etc. -- described in http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.qb.server.doc/doc/r0023249.html) -- they do not perform authentication and therefore do not require domain rights.

Best Answer

Related Solutions

SQL Server 2008 – How to Find User Access to Tables and Views

DB2 Express User Rights – How to Grant Rights for a User

Related Question