How To: Users and Roles (Simple)

I'm using Apex again, which is nice. I love the database work; cleaning, refactoring, etc. but there's just something very cool about being able to put a "face" on that database stuff.

I'm mostly done with the application and I'm going through adding in security. It's not an after thought, I'm just not going to add it first as it will make my life very difficult.

I would love to use Database Authentication, but it only checks to see if you have an account. Your database roles do not carry over into Apex (yet). I don't want to use Apex Authentication mostly because I've never used it.

I could create my own table based security, but not yet. I'm trying to keep it as simple as possible for now.

I decided on a mix of Database Authentication and table based users and roles. I will not be storing passwords nor writing any custom authentication. I'll just rely on Oracle to do that. I will however capture the username and put it in an Application Item. In the Administrator's interface, the users will be able to create users...but only if they already exist in the database. I will be creating the following roles:

--ADMIN - this role can do anything. full access to the application.
--SECTION_DEVELOPER - as it stands, we can not completely hand this off to the business. There will be some intervention required by IT. This role should be able to do all that the business person can do and a couple of extra actions.
--SECTION_ADMIN - this will be the role assigned to the business person.
--TAB_DEVELOPER - similar to SECTION_DEVELOPER, just another section of the application
--TAB_ADMIN - same as SECTION_ADMIN, assigned to business user

Perhaps an easier way to visualize it:
ADMIN
--SECTION_DEVELOPER
----SECTION_ADMIN
--TAB_DEVELOPER
----TAB_ADMIN

Here's the table to hold the roles:

CREATE TABLE t_roles
(
  role_name VARCHAR2(30)
    CONSTRAINT pk_rolename PRIMARY KEY,
  parent_role_name
    CONSTRAINT fk_rolename_troles REFERENCES t_roles( role_name )
);

INSERT INTO t_roles( role_name )
VALUES ( 'ADMIN' );
INSERT INTO t_roles( role_name, parent_role_name )
VALUES( 'SECTION_DEVELOPER', 'ADMIN' );
INSERT INTO t_roles( role_name, parent_role_name )
VALUES( 'TAB_DEVELOPER', 'ADMIN' );
INSERT INTO t_roles( role_name, parent_role_name )
VALUES( 'SECTION_ADMIN', 'SECTION_DEVELOPER' );
INSERT INTO t_roles( role_name, parent_role_name )
VALUES( 'TAB_ADMIN', 'TAB_DEVELOPER' );

I'm creating a view on top of this table because I want to use some of the hierarchical capabilities.

CREATE OR REPLACE
VIEW v_roles
AS
SELECT
  role_name,
  parent_role_name,
  SYS_CONNECT_BY_PATH( role_name, '/' ) role_path,
  level role_level,
  CONNECT_BY_ISLEAF is_leaf,
  RPAD( '-', level * 2, '-' ) || role_name display_role_name
FROM t_roles
  START WITH parent_role_name IS NULL
  CONNECT BY PRIOR role_name = parent_role_name;

I created a procedure, is_authorized which took in the username (Application Item) and a literal; ADMIN, SECTION_DEVELOPER, SECTION_ADMIN, TAB_DEVELOPER, and TAB_ADMIN. It returned either TRUE or FALSE (boolean).

Here's the table definitions for the user and user/role intersection tables:

CREATE TABLE t_users
(
  username VARCHAR2(30)
    CONSTRAINT pk_username PRIMARY KEY
);

INSERT INTO t_users( username ) VALUES ( 'CJUSTICE' );

CREATE TABLE t_user_roles
(
  username
    CONSTRAINT fk_username_userroles REFERENCES t_users( username )
    CONSTRAINT nn_username_userroles NOT NULL,
  role_name
    CONSTRAINT fk_rolename_userroles REFERENCES t_roles( role_name )
    CONSTRAINT nn_rolename_userroles NOT NULL
);

INSERT INTO t_user_roles( username, role_name )
VALUES( 'CJUSTICE', 'ADMIN' );

CREATE OR REPLACE
VIEW v_active_user_roles
AS
SELECT
  ur.username,
  r.role_name,
  r.parent_role_name,
  r.role_path,
  r.role_level,
  r.is_leaf,
  r.display_role_name
FROM
  v_roles r,
  t_user_roles ur
WHERE r.role_name = ur.role_name;

Now I need a FUNCTION that will tell me whether this person is authorized or not.

I struggled with this a little bit (hence the post).

DECLARE
  l_count INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO l_count
  FROM v_active_user_roles
  WHERE username = :p_username
    AND role_name = :p_role_name;

  IF l_count = 1 THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;

OK, so if the code from Apex is passing in CJUSTICE for the username and ADMIN for the role, it works. But ADMIN is the all powerful user. So I need another check in there:

DECLARE
  l_count INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO l_count
  FROM v_active_user_roles
  WHERE username = :p_username
    AND role_name = :p_role_name;

  IF l_count = 1 THEN
    RETURN TRUE;
  ELSE
    SELECT COUNT(*) 
    INTO l_count
    FROM v_active_user_roles
    WHERE ...
  END IF;
END;

That's where I kind of got stuck. The extra SELECT COUNT(*) would have needed at least one more additional IF THEN control statement. Time to step away.

Thinking about it, it might be easier if ADMIN were the bottom-most...leaf? In other words:
SECTION_ADMIN
--SECTION_DEVELOPER
----ADMIN

Using SYS_CONNECT_BY_PATH it would have been easy to check. I think.

If ADMIN is at the bottom though, you'll have to jump through all kinds of code hoops to keep it at the bottom. That's the indicator to me that I'm overthinking it.

What about a loop, using ROLE_PATH (SYS_CONNECT_BY_PATH)? Something like this:

    FOR i IN ( SELECT role_path
               FROM v_roles
               WHERE INSTR( role_path, l_rolename ) > 0 )
    LOOP
      IF INSTR( i.role_path, l_rolename ) < INSTR( i.role_path, p_rolename ) THEN
        RETURN TRUE;
      END IF;
    END LOOP;

If the user's role (ADMIN) is at a place closer to the front of the string and the passed in role (TAB_ADMIN) is further back, the user will be authorized to see/view/execute whatever the authorization is applied to. Here's the code all put together:

CREATE OR REPLACE
FUNCTION is_authorized
  ( p_username IN VARCHAR2,
    p_rolename IN VARCHAR2 ) RETURN BOOLEAN
IS
  l_count INTEGER;
  l_rolename VARCHAR2(30);
BEGIN
--1.  see if the user/role exists
--1a.  if true, return true
--1b.  if false
--1b1.  find the role the user is assigned to
--1b2.  see what roles inherit from the user's role
  SELECT COUNT(*)
  INTO l_count
  FROM v_active_user_roles
  WHERE username = p_username
    AND role_name = p_rolename;

  IF l_count = 1 THEN
    RETURN TRUE;
  ELSE
    BEGIN
      SELECT role_name
      INTO l_rolename
      FROM v_active_user_roles
      WHERE username = p_username;
    EXCEPTION
      WHEN no_data_found THEN
        RETURN FALSE;
    END;

    FOR i IN ( SELECT role_path
               FROM v_roles
               WHERE INSTR( role_path, l_rolename ) > 0 )
    LOOP
      IF INSTR( i.role_path, l_rolename ) < INSTR( i.role_path, p_rolename ) THEN
        RETURN TRUE;
      END IF;
    END LOOP;
      
    RETURN FALSE;
  END IF;
END is_authorized;
/
show errors

It's certainly not the best solution in the world. When I have a bit more time, I'll certainly revisit it.

Integration with Apex
Since I haven't worked with Apex regularly since 2.2, I started to use the Conditions section to store the code. What that would mean is possibly storing

RETURN is_authorized( :F_USERNAME, 'ADMIN' /*(or others)*/ );

in about 60 locations throughout the application. Then I saw this:


I had completely forgotten about that! Authorization Schemes allow you to define as many...schemes as you want. I created 5, one for each role.

Added bonus, when I need to change these, I only have to do so in one place.

You can add Authorization Schemes to almost everything. Pages, HTML Regions, Buttons, Items, Columns in reports, etc. As fine a granularity as you would ever need.

Update: 08/07/2009
This function does not work! Sue me. There is a later post addressing the issues I found, Users and Roles - Revisited.