Thursday, August 6, 2009

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

2 comments:

John Scott said...

Hey Chet,

You didn't read my book did you? ;)

I covered that exact same INSTR issue in chapter 3 (I show a nice solution to it using string_to_table).

John.

oraclenerd said...

@john

I haven't read it...yet. It came out when I was no longer working with Apex (primarily anyway).

I need to pick it up though...no excuses. ;)