Eliminating hard-coding for authorization

[TedHolt]

We have a lot of old applications that are full of hard coding for authorization. Consider item master maintenance, for instance. Mary can maintain items with item numbers that begin with 4, Bill can maintain items in classes 01 and 02, and Joe can maintain items for class 06 and also items that begin with 78, but not if they begin with 782. So we have logic like ?IF USER = ?MARY? . . .?. Sometimes it?s user profile, sometimes it?s terminal ID.

I asked the database gurus on another forum how they handle this sort of thing, and the few answers I got were pitiful (e.g. ?Create a view for Mary.?) It seems the folks who work with Oracle, SQL Server, MySQL, mainframe DB2, Unix DB2, etc. don?t have all the answers.

Since I?m always looking for a better way, I?d like to know if you?ve found an elegant way to eliminate hard coding.

[DeadManWalks]

Re: Eliminating hard-coding for authorization

Table below.

Control	StartWith	Class	NotStartWidth
MARY	4
BILL		1
BILL		2
JOE	78	6	782

SQL function or RPG

Parms(SKU,ITEMS_CLASS) Returns(YES/NO)

Perhaps?

[Scott Klement]

Re: Eliminating hard-coding for authorization

We would typically create a file where we'd store information like this (keyed by userid, with fields for each thing the user can be authorized to or blocked from, etc.) Our programs would have logic that looked them up in the file first, and then stop the user if not authorized.

It would be better to have something that's in the database itself so it would secure stuff like SQL/Query access to the tables. For updates/inserts you could block this with a trigger, but I don't see how you could block read access to data based on information that's in the data itself, since you'd have to be able to read it to know what the data is. For example, Mary is only allowed to view items that begin with '4' -- well, in order to determine if an item begins with 4, I need to be able to read the record... so I can't block Mary from reading based on this criteria unless I use something like a stored procedure or function or other program logic.

[TedHolt]

Re: Eliminating hard-coding for authorization

Thanks for the responses. I've thought about things like this. I've soft-coded various and sundry data with database tables.

Let me repeat what I said.

Since I?m always looking for a better way, I?d like to know if you?ve found an elegant way to eliminate hard coding.

I was really hoping for tried-and-true techniques. Surely other people face this same situation.

Again, thanks for your responses.

[tomliotta]

Re: Eliminating hard-coding for authorization

We have a lot of old applications that are full of hard coding for authorization.

Why are you asking the question? I would normally expect that it's because of an intent to replace those old applications with newer ones, or at least replace parts that involve the hard-coded elements.

If so, why isn't adopted authority an appropriate method?

Don't give direct authority to any user except to run entry points into the applications. Then the programming can use adopted authority to access anything it needs as long as it first verifies that the user is authorized.

[B.Hauser]

Re: Eliminating hard-coding for authorization

Creating and accessing data through views (solely) is not a bad idea.
But instead of creating a view for every user, I'd suggest adding where conditions selecting data based on the SESSION_USER special register:

Create View ...
as SELECT ....
       From YourTable
       Where  Class = Case When SESSION_USER in ('MARY', 'TOM', 'OTTO') Then '04'
                           When SESSION_USER in (BILL, BOB, JANE) Then '06'
                            ...
                            Other '' End;

If the Special register may not be enough, you may create a global variable, that can be used within an SQL view.
The global variable can be populated based on the user profile.

CREATE VARIABLE YourGblVar Char(2)
Default SELECT  Case When Session_User in ('MARY', 'OTTO', 'TOM') then '04'
                     When Session_User in ('BILL', 'BOB', 'JANE')
                     ....
                     End;

Create view YourView as
(Select *
   From YourTable
   Where Class = YourGblVar);

The global variable is initialized within a job the first time when used.
A global variable works like a data area within the QTEMP library, i.e. can have different value in different jobs.

Birgitta

[danlong005]

Re: Eliminating hard-coding for authorization

What about assigning roles to the users? Then it is not based on the user, but on the role. That way when Mary retires you just put someone else in that role....

Something like

User    Role
mary    handles4s
bill    handlesClass1and2
joe     handles78butnot782

Then have the table

role                       StartWith    Class	  NotStartWith
handles4s                   4		
handlesClass1and2                        1,2
handles78butnot782         78	          6            782

Then write a stored proc that takes in the userid. That can translate to the role and then using the guidelines set up for the role it can return the results set to the user. Now everyone has access to the data through the stored proc(web guys and IbmI guys). They don't have to know what the underlying stuff is doing at all... They just know that I pass this parm here and I get the results I want.

[TedHolt]

Re: Eliminating hard-coding for authorization

Thanks for the ideas, everyone. I think Daniel's on the right track by defining roles.