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

.

Relevant Pages

  • Re: AIX Performance issue
    ... this is how we take backup ... Same for Oracle 7.3.4? ... application, in this database, on this Operating System? ... Perhaps the scripts and commands you're running ...
    (comp.unix.aix)
  • Re: [SLE] sudoers file
    ... I like it because it logs all commands, ... # This file MUST be edited with the 'visudo' command as root. ... # Oracle user is oracle on some machines, ...
    (SuSE)
  • Re: Spool in SQL*Plus without SQL statments
    ... ECHO does not affect the display of commands ... Oh and to get rid of the column heading underline I asked about ... web; not oracle docs though). ...
    (comp.databases.oracle.misc)
  • Re: Call Oracle Stored Proceedure VB.NET
    ... You also have to ensure the Commands CommandType is set to stored procedure. ... Oracle which is already written to do more work on this data, ... Dim objcnn As New Data.OracleClient.OracleConnection ... Dim objcom As Data.OracleClient.OracleCommand ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Password change
    ... > alter user xx indentified by yyyy; ... > If this is a vendor application then there should be a way from the vendor. ... regulars will want to brush up on answering 'where to find [name your fav ... Oracle but not relevant to the charters. ...
    (comp.databases.oracle.server)