Re: Still no luck with permissions
In <1561600006@igc.org>, Scott Weikart laments:
> I sure wish Informix would allow the use of Unix *groups* (in
> addition to account names) in the database permissions.
While Informix doesn't give you group permission, you can make your
own group permissions.
In your table of employees (or whatever you call it), add a field:
employee.fname CHAR(25) # for example
employee.lname CHAR(25) # for example
employee.login_name CHAR(8) # for example
etc.
employee.group_id CHAR(3) # <--- add this field
Then add a table called group, that looks like:
group.group_id CHAR(3) # links to employee.group
group.table_name CHAR(18) # links to systables.tabname
group.qry_perm CHAR(1) # "Y" or "N"
group.add_perm CHAR(1) # "Y" or "N"
group.del_perm CHAR(1) # "Y" or "N"
group.upd_perm CHAR(1) # "Y" or "N"
Enter all of the valid groups into group_perm (eg. "LOW"/"MED"/"HIG",
or "DBA"/"PER"(personnel)/"FIN"(finance&acct.)/"EMP"(employee), or whatever).
Then assign each employee to a group.
Make sense so far? Now you need to convert these group permissions to
Informix permissions. Write a 4GL program that looks something like:
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
DATABASE foo
MAIN
DEFINE pr_employee RECORD LIKE employee.*,
pr_group_perm RECORD LIKE group_perm.*,
sql_stmt CHAR(256)
DECLARE c1_emp CURSOR FOR
SELECT * FROM employee, group_permFOREACH c1_emp INTO pr_employee.*, pr_group_perm.*
{ ** QUERY ** }
IF (pr_group_perm.qry_perm = "Y") THEN
LET sql_stmt = sql_stmt CLIPPED, "GRANT QUERY ON ",
pr_group_perm.table_name CLIPPED, " TO ",
pr_employee.login_name CLIPPED
PREPARE ex_grant FROM sql_stmt
EXECUTE ex_grant
END IF
{ ** INSERT ** }
IF (pr_group_perm.add_perm = "Y") THEN
LET sql_stmt = sql_stmt CLIPPED, "GRANT INSERT ON ",
pr_group_perm.table_name CLIPPED, " TO ",
pr_employee.login_name CLIPPED
PREPARE ex_grant FROM sql_stmt
EXECUTE ex_grant
END IF
{ ** do same thing for DELETE and UPDATE ** }
END FOREACH
END MAIN
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Run this program whenever changes are made to the employee table.
Caveat: Informix has some strange *features* when it comes to permissions.
For example, if user_1 grants "QUERY" to user_2, and user_3 grants "INSERT"
to user_2, then user_1 cannot revoke "INSERT" from user_2, and user_3 cannot
revoke "QUERY". It IS possible to overcome these *features*!
================================================================================
"When you think it's butter, but it's not, it's chiffon" - Zen parable
Todd Glidden CSC contractor at Sacramento Army Depot (SAAD),
Directorate of Information Management (DOIM)
doimic01@saad-emh1.army.mil
================================================================================