Re: Problem w/ Column-Level Privileges in 9.4

I wanted to report that we found a solution to this issue. I found several scripts in $INFORMIXDIR/etc that referenced the syscolauth table. One of them, named dummyupds914.sql, looked promising, and since we knew (or at least strongly suspected) that the problem was being caused by difficulty accessing syscolauth data, we decided to run it on our 'train' (non-production) database. It resolved the problem in that DB, so we decided to run it in on our production DB, and it appears to have resolved the problem there, as well. Our suspicion is that the dummyupds914.sql is supposed to run automatically when the DB is brought back on line after being upgraded. But, since we had problems when we first brought IDS back on line after the upgrade (due, we suspect, to $INFORMIXDIR/bin not being defined in the path of our startup script), the dummyupds914.sql didn't run when it should have. At any rate, our column-level privileges are working for us once again! -- | Jim Carpenter | Information Systems Project Coordinator | Point Loma Nazarene University | jcarpent@pointloma.edu Jim Carpenter wrote: > OS: HP-UX 11i v1 (11.11) > DB: IDS 9.40.HC5 > > We just moved from IDS 7.31.UD6 last weekend (please don't ask why we > were still on 7.31.UD6). Since the upgrade, we've had problems with > column-level privileges in certain applications. For example, we still > use PERFORM for certain purposes, and tables on which we have granted > column-level update privileges to certain users are no longer working > properly when accessed with PERFORM. The problem is that the update > privileges for the users are only found for one of the columns to which > the users have been granted update privileges. So, for example, instead > of being able to update 10 columns in a given table, the user is only > able to update one column. > > I believe that I have narrowed down the problem to weird behavior by > the syscolauth table. If I run selects against syscolauth and ask for > specific values for columns like grantee, all is well. But as soon as I > specify a certain value for the tabid field, I receive only one row of > data in return for each grantee in the table. > > For example, let's say we have a user named testuser. If I run this > select: > > select unique colno, colauth > from syscolauth > where grantee = 'testuser' > and tabid = 6567> > The results are this: > > colno colauth > > 1 -u- > > Yet I know this user actually has 81 rows in the table for tabid 6567. > FWIW, this select returns exactly the same results: > > select unique syscolauth.colno, syscolauth.colauth > from systables, syscolauth > where systables.tabname = 'test_table' > and systables.tabid = syscolauth.tabid > and syscolauth.grantee = 'testuser'> > I can verify that the user has the expected entries in the syscolauth > table by running this: > > select unique tabid, count(*) > from syscolauth > where grantee = 'testuser' > group by 1> > This shows a count of 81 for tabid 6567, plus counts for other tables > to which the user has column-level access. Running this: > > select * > from syscolauth > where grantee = 'testuser' > order by tabid, colno> > provides all of the data from syscolauth for this user. The values are > as expected (for example, for tabid 6567, all 81 rows have a value of > '-u-' for the colauth column). > > The mystery is the fact that this data isn't directly accessible when > specifying the tabid value. Because of this, as I mentioned, we have > several applications that no longer function correctly. > > In case anyone was wondering or was tempted to suggest it, we have run > 'update statistics' on all of the system catalog tables (including > syscolauth). > > If anyone out there has any idea what could be causing this and how to > fix it, or if you've experienced the same thing and just want to share > in our grief, it would be most appreciated. Even if the answer is that > there are problems with our IDS version and that we need to move to a > different version, that's okay -- at least that's better than not > having any idea as to how to fix the problem. > -- > > | Jim Carpenter > | Information Systems Project Coordinator > | Point Loma Nazarene University > | jcarpent@pointloma.edu