Re: Quick role test

Now that I understand the cause of the ORA-600, if anyone wishes to test on a throw-away instance and open a TAR to followup with Oracle support then I will explain the problem with the details as I seem to understand.

To reproduce simply follow the first 10 steps as DBA listed under "-- ora-600" in the original post. I strongly caution this may create a user account that can not be deleted, nor modified, and attempts to do so will create further ORA-600. The target user account may be denied login as well, which is why I tested on a meaningless 'bob1' schema. However, since learning the cause of the problem, I do not suggest running those steps in any db with any value. I don't know the extent of the versions effected, and I'm not interested to further test for reasons which Sybrand has cautioned. Other than my vmware development instance which is throw-away I don't have other instances/versions available to readily restore and recover.

I believe, the crux of the bug is caused by the interaction of these two lines:
alter user bob1 default role all except foo33 identified using schemax.packagey ;
alter user bob1 default role none ;

After the combined commands, the sys metadata is corrupt. E.g. sys.user$ account for bob1 has password = 'APPLICATION', however there is no corresponding row in sys.approle$ (and should be). Thereafter, simple commands like 'alter user' may cause ORA-600 whenever Oracle's source attempts any dependent transaction. Since these tables are undocumented, I offer only trial-and-error observation, analysis, hypothesis.

Using 10046 trace it appears the bug exists in transaction details combining the commands "default role all except" including "alter user...using schemax.packagey" and subsequent "default role none", which, in my case, creates a the missing sys.approle$ row(s). I again caution that these commands may create an ORA-600 situation, and denial of service to the effected schema.

As suggested, maybe someone else can submit details for Oracle's benefit. Surely Oracle development tests all sorts of things, but I stumbled into this with an extremely simple test case of command syntax and I hope its just a bug rather than incompetent design and code review. I don't care either way, but I do wish that Oracle has a chance to fix it (as I'm unable to followup further for personal reasons).

Best wishes and thanks for patience.

Mike Received on Mon Feb 20 2006 - 21:37:27 CST