Saturday, February 11, 2012

SYSDBA user gets ORA-01031: insufficient privileges updating user$

I was merrily following note "Instructions for Clearing pre-11g Database Password Hashes [ID 463999.1]" to remove the pre-11g password hashes from sys.user$. I connected as SYSDBA, and when trying to run the update statement I got ORA-01031:

SQL> update sys.user$ set password=NULL;
update sys.user$ set password=NULL
*
ERROR at line 1:
ORA-01031: insufficient privileges

I tried another user with SYSDBA privilege and got the same thing. These users can do everything else as SYSDBA but not it seems update user$. What's going on?


Well...


The software install was a default install with the "Enterprise Edition" option selected. As it turns out this includes installing the binaries for Database Vault. Even though Database Vault is not configured, the binaries still have an effect. So Database Vault is enabled even though you have not configured it, there are no Database Vault schemas etc.


To remedy the issue you have to relink Oracle without Database Vault on. This is documented in The Database Vault Administrators guide.

Disabling and Enabling Oracle Database Vault
http://docs.oracle.com/cd/E11882_01/server.112/e23090/dvdisabl.htm

From the doc: Disable Oracle Database Vault as follows:
  • UNIX: Run the following commands:
    $ cd $ORACLE_HOME/rdbms/lib
    $ make -f ins_rdbms.mk dv_off ioracle
    
  • Windows: In the ORACLE_HOME\bin directory, rename the oradvll.dll file to another name, such as oradvll.dll.dbl.
After disabling database vault I was successfully able to update the password file and remove the pre-11g password hashes.


Word to the wise: Use the custom install option when installing Oracle software and only pick the components you really need and are licensed for. This has the added benefit of upgrades going faster due to not having a bunch of unnecessary packages and tables.

1 comment:

  1. There is so much trouble with missing dll files. It became annoying lately. There are a lot of fake apps in a web which are crashes all the time and doesn't work properly. Personally, I use fix4dll source to solve such problems, for e.g. http://fix4dll.com/msvcr71_dll . For now, there is no way better, hope that in future we will have more reliable free apps.

    ReplyDelete