systabperm question
A DBA on IDS 10 (HP-UX) asked what the '*' in a permissions column means and whether an old 7.3-era script that DELETEs rows from systabauth/systabperm is still an acceptable way to clean up stale user privileges. Answer: the '*' flags that column-level privileges also exist (see syscolauth, documented in Chapter 2 of the SQL Reference); 'systabperm' turned out to be an application table, not a catalog. Deleting from system catalogs was strongly discouraged — instead query systabauth to find discrepancies against a list of valid users and generate REVOKE statements (or use a third-party permissions tool).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Running IDS 10.00.HC5 on an HP rp7410 with OS HPUX 11v2 (11.23). I have two questions on permission that I hope someone can help with. 1 In systabperm the third column of perms sometimes has a * but I cant seem to find what the * means. Looking at some of the records I would guess it means partial access of some kind. Can someone point to where perms columns are explained or tell me? Most seem simple: s=select, d=delete, u=update,i=insert. 2 We have a script (IDS 7.3 vintage) that would remove permissions from users in systabauth & systabperm that no longer where on the system or had permission changed and not all tables updated (remove old access). In other words clean up the mess when not done correctly. The script generates a temp table with all users that need access removed and then deletes all records from systabauth & systabperms for those users. My question is in IDS 10, is this a good thing or is there a better way to clean up user permissions? John
Refer to Chapter 2 of SQL Reference manual for system catalogue info. It w= ill walk through each table. The "*" indicates that there are active colu= mn permissions as well - in the syscolauth table. I've never heard of the systabperm table. You are deleting from system catalogues? Not a good plan at all. Especial= ly when it's really simple to script up the commands to REVOKE privileges. = If you like, I have a script which does this. cheers j. >From: JOHN DAVID ADAMSKI <adamski@graceland.edu> >Date: 2008/02/19 Tue AM 10:04:29 CST >To: ids@iiug.org >Subject: systabperm question [11337] >Running IDS 10.00.HC5 on an HP rp7410 with OS HPUX 11v2 (11.23).=20 > >I have two questions on permission that I hope someone can help with.=20 > >1 =C2=96 In systabperm the third column of perms sometimes has a =C2=91*= =C2=92 but I can=C2=92t=20 >seem to find what the =C2=91*=C2=92 means. Looking at some of the records = I would guess=20 >it means partial access of some kind. Can someone point to where perms col= umns=20 >are explained or tell me? Most seem simple: s=3Dselect, d=3Ddelete,=20 >u=3Dupdate,i=3Dinsert.=20 > >2 =C2=96 We have a script (IDS 7.3 vintage) that would remove permissions = from=20 >users in systabauth & systabperm that no longer where on the system or had= =20 >permission changed and not all tables updated (remove old access). In othe= r=20 >words clean up the mess when not done correctly. The script generates a te= mp=20 >table with all users that need access removed and then deletes all records= =20 >from systabauth & systabperms for those users. My question is in IDS 10, i= s=20 >this a good thing or is there a better way to clean up user permissions?= =20 > >John=20 > > >**************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > >See you at the IIUG Informix 2008 Conference >The Power Conference for Informix Professionals >April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas >http://www.iiug.org/conf >Registration Now Open!!=20
I just checked systabperm is a local table (tabid = 746) for the application. So disregard that table. John -----Original Message----- From: vze2qjg5@verizon.net [mailto:vze2qjg5@verizon.net] Sent: Tuesday, February 19, 2008 10:49 AM To: John Adamski; ids@iiug.org Subject: Re: systabperm question [11337] Refer to Chapter 2 of SQL Reference manual for system catalogue info. It will walk through each table. The "*" indicates that there are active column permissions as well - in the syscolauth table. I've never heard of the systabperm table. You are deleting from system catalogues? Not a good plan at all. Especially when it's really simple to script up the commands to REVOKE privileges. If you like, I have a script which does this. cheers j. >From: JOHN DAVID ADAMSKI <adamski@graceland.edu> >Date: 2008/02/19 Tue AM 10:04:29 CST >To: ids@iiug.org >Subject: systabperm question [11337] >Running IDS 10.00.HC5 on an HP rp7410 with OS HPUX 11v2 (11.23). > >I have two questions on permission that I hope someone can help with. > >1 Â In systabperm the third column of perms sometimes has a Â*Â but I canÂt >seem to find what the Â*Â means. Looking at some of the records I would guess >it means partial access of some kind. Can someone point to where perms columns >are explained or tell me? Most seem simple: s=select, d=delete, >u=update,i=insert. > >2 Â We have a script (IDS 7.3 vintage) that would remove permissions from >users in systabauth & systabperm that no longer where on the system or had >permission changed and not all tables updated (remove old access). In other >words clean up the mess when not done correctly. The script generates a temp >table with all users that need access removed and then deletes all records >from systabauth & systabperms for those users. My question is in IDS 10, is >this a good thing or is there a better way to clean up user permissions? > >John > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > >See you at the IIUG Informix 2008 Conference >The Power Conference for Informix Professionals >April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas >http://www.iiug.org/conf >Registration Now Open!!
I guess I overstepped myself there. Startup system catalogues have tabids under 99, but there are some older add-on system catalogues sysmenuts, sysmenuitems, syscolattr(?) which were created when you first used them and thus got tabids > 99. Don't know that anybody uses those particular ones anymore. Googling a bit. Any chance this database was migrated over from some other RDBMS at some point? j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of John Adamski Sent: Tuesday, February 19, 2008 1:11 PM To: ids@iiug.org Subject: RE: systabperm question [11339] I just checked systabperm is a local table (tabid = 746) for the application. So disregard that table. John
The database was created in IDS around 1999 (before my time here so not sure which version - guess IDS 7.x). When I came on it was IDS 7.3 and got the job to upgrade to IDS 9.4 then last November to IDS 10. I talked to one of the developers that been here since '99 and he's not sure but might be an application table. I guess my question really is how do I 'audit' the DB to make sure no one has more access then they should. What do other people do? I know that sounds a little stupid of a question. All the developers have DBA privileges and when I'm away the developers will play. ;-) John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jack Parker Sent: Tuesday, February 19, 2008 5:32 PM To: ids@iiug.org Subject: RE: systabperm question [11348] I guess I overstepped myself there. Startup system catalogues have tabids under 99, but there are some older add-on system catalogues sysmenuts, sysmenuitems, syscolattr(?) which were created when you first used them and thus got tabids > 99. Don't know that anybody uses those particular ones anymore. Googling a bit. Any chance this database was migrated over from some other RDBMS at some point? j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of John Adamski Sent: Tuesday, February 19, 2008 1:11 PM To: ids@iiug.org Subject: RE: systabperm question [11339] I just checked systabperm is a local table (tabid = 746) for the application. So disregard that table. John ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!
Sorry, forgot I was going to send you this. Must be the alcohol. Actually, I'd rather send it just to you and not share it with the world at this time. Could you send me an email address to jack dot parker4 at verizon dot net? The way you were checking for existing permissions is fine. My only complaint was the way you were deleting from the systab* tables. If you check with Advanced Data Tools, Lester Knutsen has a package he used to sell fairly inexpensively to manage permissions for you. You could roll your own fairly easily and it sounds like you are on the path - if it's not worth your time, check with adtc.com (I think it is). Otherwise I would keep a list of valid users and their permissions and then check that list periodically against systabauth, picking out discrepancies and then fire this script "revoke <database> -p <insert update delete> -u <user> -t <table>" cheers j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of John Adamski Sent: Tuesday, February 19, 2008 7:28 PM To: ids@iiug.org Subject: RE: systabperm question [11349] The database was created in IDS around 1999 (before my time here so not sure which version - guess IDS 7.x). When I came on it was IDS 7.3 and got the job to upgrade to IDS 9.4 then last November to IDS 10. I talked to one of the developers that been here since '99 and he's not sure but might be an application table. I guess my question really is how do I 'audit' the DB to make sure no one has more access then they should. What do other people do? I know that sounds a little stupid of a question. All the developers have DBA privileges and when I'm away the developers will play. ;-) John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jack Parker Sent: Tuesday, February 19, 2008 5:32 PM To: ids@iiug.org Subject: RE: systabperm question [11348] I guess I overstepped myself there. Startup system catalogues have tabids under 99, but there are some older add-on system catalogues sysmenuts, sysmenuitems, syscolattr(?) which were created when you first used them and thus got tabids > 99. Don't know that anybody uses those particular ones anymore. Googling a bit. Any chance this database was migrated over from some other RDBMS at some point? j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of John Adamski Sent: Tuesday, February 19, 2008 1:11 PM To: ids@iiug.org Subject: RE: systabperm question [11339] I just checked systabperm is a local table (tabid = 746) for the application. So disregard that table. John **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!