Re: No select permission

Thanks a ton Jonathan for very clear explaination as always you do. Solution given by you has almost resolved my 98% problem, however, remaining 2% may still need to address. I can now find out a list of tables to which user does not have permission (there are couple of 100s of such tables for that user) but how do I know which table(s) require SELECT permission when user session is active. In other words when a user connects database and try to run a query and gets "No Select Permission", how do I find out which table(s) are being accessed. onstat -g ses <sid> does not display current sql. Can some one please help me out ? TIA

Hari Gupta wrote: > Thanks a ton Jonathan for very clear explaination as always you do. I note that I forgot to mention that if you own a table, you have select permission on it -- and you cannot remove that permission. (Side effect: you cannot remove the table owner's permission to update the rows in a table, nor their permission to insert or delete rows. > Solution given by you has almost resolved my 98% problem, however, > remaining 2% may still need to address. I can now find out a list of > tables to which user does not have permission (there are couple of > 100s of such tables for that user) but how do I know which table(s) > require SELECT permission when user session is active. In other words > when a user connects database and try to run a query and gets "No > Select Permission", how do I find out which table(s) are being > accessed. onstat -g ses <sid> does not display current sql. Is it an application that you wrote that gets the error? Or one over which you have influence? If so, you can look to modifying the code that reports the error so that it logs more information - ideally, the SQL statement but maybe the source file and line number are the best that's readily available. If it is a library function that is failing on some dynamic SQL, getting decent data about the calling sequence may be harder. If you have no control over the application and the error reporting, you are in for a harder time. Platform and version information was not given - and wasn't particularly important either. Now it might be. You could look at onaudit auditing - that might be more verbose than you want, though. If you can get hold of sessions reliably, then maybe onstat and some options would do the trick, but what worries me in such cases is that the session probably terminates too quickly after detecting an error, losing the important data. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

onstat -g sql <sid> However, just cause he tries to access it doesn't mean he should have permission to read it. probably worth a conversation with your application people to find out what tables different types of people should be able to access. If anyone can read anything then just grant select to "public" hariog@yahoo.com (Hari Gupta) wrote in message news:<1a1cd35b.0409120303.32718f82@posting.google.com>... > Thanks a ton Jonathan for very clear explaination as always you do. > Solution given by you has almost resolved my 98% problem, however, > remaining 2% may still need to address. I can now find out a list of > tables to which user does not have permission (there are couple of > 100s of such tables for that user) but how do I know which table(s) > require SELECT permission when user session is active. In other words > when a user connects database and try to run a query and gets "No > Select Permission", how do I find out which table(s) are being > accessed. onstat -g ses <sid> does not display current sql. > > Can some one please help me out ? > > TIA

Thanks Scott for your time to reply. onstat -g sql <sid> does not display SQL stmt if a user has got an err -272. I do not want to give public read access to all tables as we need to restrict access for sensitive info. tables. I am sure engine must be storing some where when user tries to access those restricted tables. onaudit does record tabid and error number but I don't want to start onaudit at the moment on production as I am assessing load/space impact of onaudit. May be some one shed lights on this. Thanks anyway.

Hari Gupta wrote: > Thanks Scott for your time to reply. onstat -g sql <sid> does not > display SQL stmt if a user has got an err -272. I think you're correct. That shows the last successfully parsed SQL; failed SQL isn't tracked. > I do not want to give public read access to all tables as we need > to restrict access for sensitive info. tables. Fair enough. You may want to assess which tables are truly sensitive and which are not, and grant more general access (select-only for public is often - but not always - reasonable) to most tables, without exposing the sensitive tables. > I am sure engine must be storing some where when user tries to > access those restricted tables. Why do you think that is the case? It is (or, at least, probably should be) an auditable event; it is otherwise of minimal consequence to the server and it has no interest in storing the information. So it doesn't. > onaudit does record tabid and error number but I don't want to > start onaudit at the moment on production as I am assessing > load/space impact of onaudit. If you don't want to use the main tool other than an application error log that can resolve the problem for you, that is your business -- but you need to understand that when you choose to ignore the information coming your way that tells you what is what, people will be less inclined to continue answerng your questions. I originally wrote 'only tool' instead of 'main tool'; I'm not sure I can think of an alternative, but there might be some such tool. > May be some one shed lights on this. I've tried. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

You could potentially set sqlidebug.... eq: export SQLIDEBUG=2:<somedirwithloootsofspace>/<somename> start your program; after that in somedirwithloootsofspace there will be a file called somename_???? unset SQLIDEBUG then run sqliprint <somedirwithloootsofspace>/somename_???? > afile (sqliprint comes with the clientsdk.) you might find: # values: 0 CMD.....: "select * from xx " [17] SQ_NDESCRIBE SQ_WANTDONE SQ_EOT S->C (12) Time: 2004-09-13 17:55:14.43234 SQ_ERR SQL error..........: -272 ISAM/RSAM error....: 0 Offset in statement: 17 Error message......: "" [0] SQ_EOT or use a blunt axe: onmode -I 272 run your program and the engine will puke an assertion; WARNING you may need to bounce the engine afterwards... See you Superboer. dryburghj@yahoo.com (scottishpoet) wrote in message news:<81714288.0409121340.502d8c29@posting.google.com>... > onstat -g sql <sid>> > However, just cause he tries to access it doesn't mean he should have > permission to read it. > > probably worth a conversation with your application people to find out > what tables different types of people should be able to access. > > If anyone can read anything then just grant select to "public" > > hariog@yahoo.com (Hari Gupta) wrote in message news:<1a1cd35b.0409120303.32718f82@posting.google.com>... > > Thanks a ton Jonathan for very clear explaination as always you do. > > Solution given by you has almost resolved my 98% problem, however, > > remaining 2% may still need to address. I can now find out a list of > > tables to which user does not have permission (there are couple of > > 100s of such tables for that user) but how do I know which table(s) > > require SELECT permission when user session is active. In other words > > when a user connects database and try to run a query and gets "No > > Select Permission", how do I find out which table(s) are being > > accessed. onstat -g ses <sid> does not display current sql. > > > > Can some one please help me out ? > > > > TIA

Thanks Jonathan and Superboer for helping me. SQLIDEBUG, SQLIPRINT and Jonathan script helped me a lot. Also, Jonathan I am sorry for my ignorance. Thanks again.