dbschema not showing 'U' type users

Hi all, Using dbschema -p <username> -d <database> you get permissions that the user has on a specifig database's objects - including obviously a type of database-level right (one of dba/resource/connect). There's also one more, not well documented, user type apart from these 3 which caused a bit of trouble for me recently. It's a 'U' (next to 'D', 'R', 'C') type of a user. Nothing strange (apart from not many words about this in the documentation) would be in this if that the 'dbschema' doesn't show such 'U' users at all. That's what I'm checking: # dbschema -VIBM Informix Dynamic Server Version 11.50.FC6W4WE Software Serial Number AAA#B000000 # dbschema -p all -d sei_copy_focus -ss | grep livenetapps1 # dbschema -p livenetapps1 -d sei_copy_focus -ss | grep -v revokeDBSCHEMA Schema Utility INFORMIX-SQL Version 11.50.FC6W4WE (...blank lines here...) No permissions for user livenetapps1. # echo "select * from sysusers where username = 'livenetapps1';" | dbaccess sei_copy_focus username livenetapps1 usertype U priority 5 password defrole app_net This type of a user is created when you're granting a user with default role: GRANT DEFAULT ROLE 'IT' TO 'username'; It causes some troubles when you're about to clear the permissions on a particular database out as this 'U' right gives a user actually 'connect' permission (according to ServerStudio and that I can connect). I'm not sure is it a bug or just the way 'dbschema' works. Did anybody face this problem before and could tell me is 'dbschema' a reliable tools anymore? ;-)

I'm running 1150FC3X6 on Ubuntu and dbschema is showing the grants for a type 'U' user I have defined for testing myschema (which also produces correct output). It may be a bug in your particular version. Open a PMR with IBM. Meanwhile, you can use my dbschema replacement utility, myschema, which is included in the package utils2_ak that you can download from the IIUG Software Repository. > select * from sysusers where usertype = 'U'; username freddy usertype U priority 5 password defrole tester 1 row(s) retrieved. > $ dbschema -d art |fgrep freddygrant "tester" to "freddy" ; grant default role "tester" to "freddy" ; $ myschema -d art |fgrep freddy GRANT DEFAULT ROLE "tester" TO "freddy";GRANT "tester" TO "freddy"; The only difference is which is reported first the grant <role> or the grant default role... Obviously I think that myschema is doing it correctly. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jun 8, 2010 at 10:29 AM, WALDEMAR ZNOINSKI <waldek@znoinski.pl>wrote: > Hi all, > Using dbschema -p <username> -d <database> you get permissions that the > user > has on a specifig database's objects - including obviously a type of > database-level right (one of dba/resource/connect). There's also one more, > not > well documented, user type apart from these 3 which caused a bit of trouble > for me recently. It's a 'U' (next to 'D', 'R', 'C') type of a user. Nothing > strange (apart from not many words about this in the documentation) would > be > in this if that the 'dbschema' doesn't show such 'U' users at all. > That's what I'm checking: > > # dbschema -V> IBM Informix Dynamic Server Version 11.50.FC6W4WE Software Serial Number > AAA#B000000 > > # dbschema -p all -d sei_copy_focus -ss | grep livenetapps1> > # dbschema -p livenetapps1 -d sei_copy_focus -ss | grep -v revoke> DBSCHEMA Schema Utility INFORMIX-SQL Version 11.50.FC6W4WE > (...blank lines here...) > No permissions for user livenetapps1. > > # echo "select * from sysusers where username = 'livenetapps1';" | dbaccess > sei_copy_focus> > username livenetapps1 > usertype U > priority 5 > password > defrole app_net > > This type of a user is created when you're granting a user with default > role: > GRANT DEFAULT ROLE 'IT' TO 'username';> > It causes some troubles when you're about to clear the permissions on a > particular database out as this 'U' right gives a user actually 'connect' > permission (according to ServerStudio and that I can connect). > > I'm not sure is it a bug or just the way 'dbschema' works. Did anybody face > this problem before and could tell me is 'dbschema' a reliable tools > anymore? > ;-) > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd76164c1c00b048887eb74