Monday, July 20, 2009

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.

4 comments:

John Scott said...

Hi Chet,

The other nice thing is that you can cache the authorization per session, so that it's evaluated once and you don't incur the 'overhead' of rerunning your code each time you want to check if they're a developer etc.

Obviously in certain circumstances you might want to run the code each time, however for something like this where the authorization check is pretty much tied to the user, it's unlikely to change during the duration of their session (and if it did, there's a nice APEX_UTIL.RESET_AUTHORIZATIONS routine which lets you reset any cached authorizations).

John.

oraclenerd said...

Mr. Scott,

I saw those options, but I think I chose page. I believe you are right though, it is very unlikely that there privileges will change mid session so once will be good enough.

Patrick Wolf said...

Hi Chet,

btw, you do not even need your own roles table. Just create database roles and use the oracle dictionary views to check which database user is assigned to which database role.

Regards
Patrick

oraclenerd said...

@patrick

Good observation. I had database roles in mind with that table (VARCHAR2(30)) since eventually, Apex will have the ability to utilize not only database authentication but authorization. That was definitely my end game. Start with that and then transition over.

chet