Users and Roles - Revisited

In a previous post, How To: Users and Roles (Simple), I went through and demonstrated one way to build a simple security system using users and roles.

I'm using the Database Authentication security scheme in Apex. Patrick Wolf [ blog ] pointed out that I should create database roles instead of using my own. That is the end game of course as I've limited the role_name column to VARCHAR2(30). But for now, this will have to do.

38 lines is now 27

The previous code looked like this:

FUNCTION is_authorized
  ( p_username IN VARCHAR2,
    p_rolename IN VARCHAR2 ) RETURN BOOLEAN
IS
  l_count INTEGER;
  l_rolename VARCHAR2(30);
BEGIN
  SELECT COUNT(*)
  INTO l_count
  FROM v_active_user_roles
  WHERE user_name = p_username
    AND role_name = p_rolename
    AND rownum < 2;
  
  IF l_count = 1 THEN
    --user has this role assigned
    RETURN TRUE;
  ELSE
  --user does not have this role assigned
  --check to see if user's role inherits these privs
    l_count := NULL;

    SELECT COUNT( DISTINCT role_name )
    INTO l_count
    FROM v_roles
    WHERE status = 'A'
      AND end_date IS NULL
      AND INSTR( role_path, p_rolename ) > 0
      AND INSTR( role_path, p_rolename ) < INSTR( role_path, role_name );

    IF l_count > 0 THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
    
  END IF;
END is_authorized;

and I found it didn't work too well.

Specifically, the person with the least privileges, TAB_ADMIN, could see everything that only the ADMIN could see.

There was another small problem, using INSTR was screwed up to. Can you guess why?

'ADMIN'?

That would return a hit back for 3 roles: ADMIN, SECTION_ADMIN and TAB_ADMIN.

Bad developer, bad. (Another great reason you should NOT roll your own security!)

What was the problem again?

Let me quickly recap what I am trying to do with a "pretty" picture:



I'm going to try and articulate this with words, please don't laugh. ADMIN is the all powerful, it should be able to do anything it wants to do. There are 5 authorization schemes, one for each role; placed on different objects (pages, items, tabs, etc).

If a button has the TAB_ADMIN authorization schema, ADMIN should be able to run it through inheritance. The picture above is trying to depict that with those red arrows.

So, what's the point of this post? I've forgotten, so I'm skipping to the end.

I refactored the IS_AUTHORIZED procedure from 38 lines to 27 and I believe it's a bit more intuitive than it was. The key for me was included ROLE_LEVEL which is just an alias for the pseudo-column LEVEL.

If the ROLE_LEVEL, of the enabled roles for a user, is less than the ROLE_LEVEL where ROLE_NAME is part of the ROLE_PATH (SYS_CONNECT_BY_PATH), you're in.

Or something like that.

Here's the final product:

FUNCTION is_authorized
  ( p_username IN VARCHAR2,
    p_rolename IN VARCHAR2 ) RETURN BOOLEAN
IS
  l_count INTEGER;
BEGIN
  FOR i IN ( SELECT role_name, role_path, role_level
             FROM v_active_user_roles
             WHERE user_name = p_username )
  LOOP
    IF p_rolename = i.role_name THEN
      RETURN TRUE;
    ELSE
      SELECT COUNT(*)
      INTO l_count
      FROM v_roles
      WHERE INSTR( role_path, i.role_name, -1 ) > 0
        AND role_level > i.role_level;
      
      IF l_count > 0 THEN
        RETURN TRUE;
      ELSE
        RETURN FALSE;
      END IF;
    END IF;
  END LOOP;
END is_authorized;

I'll also be updating the other post to point to this one.

Small reminder: TESTING, TESTING, TESTING