Path: utzoo!attcan!uunet!cs.utexas.edu!usc!uscacsc!redondo.usc.edu!dzhang From: dzhang@redondo.usc.edu Newsgroups: comp.databases Subject: Summary: 4GL command level access control (long) Message-ID: <1225@uscacsc.usc.edu> Date: 7 Feb 90 18:40:04 GMT Sender: news@uscacsc.usc.edu Lines: 342 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. -------------- --