dzhang@redondo.usc.edu (02/08/90)
About two weeks ago I posted an article asking how command level access
control can be done in a 4GL. Here I thank all who responded.
Comments/pointers are still welcome.
Before I summarize the responses, let me have a summary of what I had/have
in mind. I said I had written an INFORMIX 4GL application for a small
library. Similar to the way access to relations is controlled (eg,
the GRANT statement in SQL), I'd like to be able to specify who can access
(run) what commands (that in turn access the underlying relations).
To be more specific, suppose that we have something like the
following (INFORMIX 4GL format):
MENU "BOOKS"
COMMAND "Borrow" "Borrow a book."
CALL borrow_book()
COMMAND "Return" "Return a book."
CALL return_book()
COMMAND "Update" "Enter new books or change old info."
CALL update_menu()
COMMAND "Search" "Search for books."
CALL search_menu()
COMMAND "Geport" "Generate reports."
CALL report_menu()
COMMAND "Exit" "Leave the BOOKS menu."
EXIT MENU
END MENU
I'd like to see 4GL statements look like these:
PERMIT BOOKS to PUBLIC
PERMIT Search to PUBLIC
PERMIT Geport to ALL
PERMIT Update to dzhang
PERMIT Update to ALL BUT jsmith
As for the effects of these commands, either a user gets denied and gets
an error message when the user tries to run an un-PERMITted commands,
or a user can only see commands PERMITted.
So we have two types of access control: One is access control
of relations, the other is access control of application commands.
These two are related but they are different and the latter is at a
higher level. Often users only use these commands and don't use
query languages directly to access underlying databases
(or even don't know anything about these databases). When this
is the case, what we care is the latter type of access control.
In a word, what I want(ed) to ask is this: Having a hierarchy of command
menus generated by using a 4GL, do we have 4GL statement(s) that allow
us to specify who can access what commands on the menus?
From the responses I got I'm not sure the answer is yes or no.
It seems something is out there, although no one told me there's such
thing as what I just described above (ie, just specify PERMITs and
no need to do anything else). The responses can be classified
as follows and each response belongs to one or more categories:
- Those that suggested to have separate programs for each
type of user;
- Those that suggested to make use of the users' database access
privilege in the system catalog;
- Those that said it can be done quite easily with certain
4GLs, without showing how it can be done;
- Those that suggested some 4GLs have what I want, without
specifying which 4GLs;
- Those that showed how it can be done by using a table (of
user_ids vs commands) storing the information about
who can access what commands;
- Those that asked me to post this summary.
------------
From sullivan%aqdata.uucp@jarthur.Claremont.edu Fri Jan 19 09:10:46 1990
2) Have separate programs for each type of user. Since you can write
multi-file programs in 4gl, have all programs share everything but
the menu definition. That is where you differ things for the different
users. Then you set things up so that different users run different
"versions" of the program.
------------
From cortesi@infmx.uu.net (or ...pyramid!infmx!cortesi) Fri Jan 19
13:43:24 1990
The basic idea is to get the user's database privilege (see the GRANT
statement) out of the system catalog and use that!
These are scraps of the actual working code, but since I'm cutting and
pasting in a hurry you have to rewrite them... I have included more
than you asked for, hope it isn't too much to sort out.
globals
define
runuser char(8), { who I am today }
authority smallint, { set to one of these: }
VISITOR, { assert: VISITOR < REGULAR < SYSOP }
REGULAR,
SYSOP smallint,
lasthnd char(14), { dflt handle, initially=runuser }
{
Function set_up() does these things:
Puts values in the global "constants." Someday 4gl will have
a constant statement and these will be constants.
Opens forms
Sets the program options for keys and screen format.
Sets the name of the database and opens it. In some future
version we might ask the user for the pathname of the
database, but for now we just assume it's "msgbase"
in the current directory.
Gets the current user's userid; see comments below on method.
Looks in the system catalog and learns the user's privilege
level, and sets "authority" to match.
Adds a row for the user to the userhist table, if necessary
Updates the userhist count of times on and last on
}
function set_up()
define
utype char(1)
let VISITOR = 0 { can only browse messages }
let REGULAR = 1 { can insert, and edit/delete own messages }
let SYSOP = 2 { can edit/delete any message }
...
{ get the current user's id. In a future release 4gl will support the
"user" function in any expression, as it does "today," today. For now
"user" only works in an sql statement, so the following is the only
way I know to get the userid into a program variable. The "from" table
doesn't matter so long as it exists; however if it has more than one
row the select will return multiple values, hence a cursor must be
used instead of a singleton select statement. }
if retcode <> PS_FINITO then { no error so far }
declare uidc cursor for select user from sysusers
open uidc
fetch next uidc into runuser
close uidc
let lasthnd = runuser { initial, default signature }
end if
{ get this user's level of privilege in this database. DBA privilege
makes us a SYSOP while Resource privilege makes us a REGULAR. We
check first for the userid, then for "public." We can use a
singleton select instead of a cursor because sysusers.username
is a unique-indexed column and we can't get more than one value back.
Note on comparisons: informix always down-shifts the word PUBLIC
no matter how it's spelled in the GRANT statement. A select "where
username = "PUBLIC" will always fail even if a grant to PUBLIC exists. }
if retcode <> PS_FINITO then { no error so far }
let authority = VISITOR
let utype = "X" { in case neither select hits }
whenever error continue { no stops on empty selects }
select usertype into utype from sysusers
where sysusers.username = runuser
if status <> 0 then { not authorized by name, try public }
select usertype into utype from sysusers
where sysusers.username = "public"
end if
whenever error stop
if utype = "D" then { DBA, make us sysop }
let authority = SYSOP
let lasthnd = "sysop"
end if
if utype = "R" then { RESOURCE, make us regular }
let authority = REGULAR
end if
{ if both selects failed, utype still = "X" = "bozo" }
end if
{ The userhist table has a row per user, with a unique index on the
username column. Make sure there's a row for this user simply by
inserting one and letting the insert fail if the row exists. Then
update the user's count of times on, set date last-on to today,
and retrieve count of high topic number seen }
if retcode <> PS_FINITO then { no error so far }
whenever error continue { don't care if any of this works }
BEGIN WORK
insert into userhist(username,timeson,laston,msgcount,topcount,tophigh)
values(runuser, 0, 0, 0, 0, 0)
if status = 0 then
COMMIT WORK
else
ROLLBACK WORK
end if
BEGIN WORK
update userhist set timeson = timeson+1,
laston = today
where username = runuser
if status = 0 then
COMMIT WORK
else
ROLLBACK WORK
end if
select tophigh into hitopseen from userhist
where username = runuser
whenever error stop
end if
return retcode
end function
---------------
From kayvan@Transact.COM (or ...!{apple,pyramid,mips}!mrspoc!kayvan)
Fri Jan 19 13:43:31 1990
You can do what you want quite easily with UNIFY DBMS and ACCELL 4GL.
-----------
From munnari!mlacus.oz.au!ash@uunet.UU.NET Thu Jan 25 12:22:52 1990
Sorry, I haven't an answer but 12 months ago my previous employers were asking
the same question. Back then, Informix in Australia was waiting for their US
office to give an answer. It is easier in Informix SQL but I'm not a current
user of either so I won't try. Please summarise responses to the net.
--------------
From segel@icarus.eng.ohio-state.edu (or segel@informix.com) Fri Jan 19
12:02:27 PST 1990
Sure it can be done. Fairly easily. What informix does is track the
users to the database in a systable. So , when the user logs into the
database,the database knows who they are and the USER command lets you
know who that person is. (uid).
What you need to do is create a table of valid users then compare USER
to this table. If not found, then the user does not have the right to
use that command and should be told so immediately. There are various
ways of organizing this table(s) to add security to your system.
To have the user see only the commands he can use, becomes a little
trickier, but still fairly straight forward.
---------------
From ...!bionet!ames!pacbell!pbhyf!rsp Wed Jan 24 12:45:03 PST 1990
Of course, you should be able to do this. INFORMIX (or any database
language system I know) can. You'll have to do a little extra work
to make it happen however. There are a number of ways you could design
it. I'll just suggest one way I've seen done in INFORMIX-4GL:
1. Build SQL tables into your application to allow levels of access. You
can design this in many ways -- whatever seems appropriate to you. One
way is to use an integer to assign levels of security. Any user with
a level from 0 - 99 can see widgets; users from 100 - 199 can view / add
widgets; users from 200 - 299 can view / add / update widgets; 300 -
do anything. This is arbitrary. You can define whatever meanings you
like. You could even have a set of BOOLEAN flags in a SQL security table
that you set and check like:
flag name legal values
========== =============
viewOnly [TRUE/FALSE]
viewAdd [TRUE/FALSE]
viewAddUp [TRUE/FALSE]
all [TRUE/FALSE]
This seems more cumbersome to me, but suit yourself.
2. You'd also have to build a system administration screen to allow these
levels to be set and changed. Use the normal SQL "grant" statement to only
allow system administrators to use this security level setting screen.
3. In your menu code, when a user selects a choice, do a select into
your security table and see if she/he is permitted to make that choice.
You can tell by seeing if the right level of access is listed for that
user, or (if you used boolean flags) if the correct flag is TRUE.
If not, politely refuse access by issuing a nice message and running
"next menu blah-blah" statement instead of calling the forbidden function.
4. You will also have to figure out a way to know who the user is, of course.
In UNIX you'd want to know the user's login ($LOGNAME). A C program may
be required to get it for you. I think INFORMIX has a USER keyword, but
my memory says that it is only of limited function (and damned hard to find
in their documentation.)
This is just the outline of one approach. The point is that what you want to
do is quite doable and is commonly done. So don't give up!
There is an INFORMIX-4GL application called CSS (Corporate Security System)
here at PacBell that is even smart enough to give each user a completely
unique set of menus, based on that user's work group and security level.
Workers in Electronic Operations do not work cases for Investigative
Unit. Using SQL tables as described above, but more complex, the CSS
system determines which menu choices to offer a given user AT RUN TIME.
(I should mention that these menus are NOT the standard INFORMIX-4GL
ring-menus. Those, unfortunately can not be customized at run-time. The
menu system itself is driven from SQL tables which store the menu names,
menu items, program function or menu to call, user access levels required,
etc.)
When a System Administrator changes a user's access level, instantly, the
run-time menus and functions available to that user are changed. This took
a little coding/design work, but it works very well.
---------------
From dberg@nosc.mil (or {akgua decvax dcdwest
ucbvax}!sdcsvax!noscvax!dberg Wed Jan 24 12:45:39 PST 1990
To solve a similar problem, we introduced an "ability table". It is
a two dimensional table of user_ids vs functions. Only users who
are designated in the table as permitted to execute a particular
function are allowed to. One of the functions in the table is the
ability to update the ability table. A simple data entry function
written in 4GL operates on this table.
Of course, this means that for every menu function that you wish to
control, you must query the ability table to see if the logged-in user
is permitted to execute it.
----------
From {uunet,mcgill-vision}!sobeco!roe Wed Jan 24 12:46:23 PST 1990
I've never worked with Informix, but all the (so-called) 4GL's I've used
have a simple way of stating that "certain menu items are only visible
to this user" (read: user 0).
Some of the 4GL's (Dieu above, help me - appgen) let you say: if the
user is not is this group, this command is not available.
If informix doesn't have it, you have a (surprisingly major) fault
in the language.
--------------
--