Wednesday, December 07, 2011

ORA-01031

If you create a user and give it a password, as opposed to identifying it externally, that user can change its password to another value but it cannot make itself externally identified.

If you create a user and make it externally identified, that user cannot give itself a password.

If you want a user to be able to do either of these things, you need to grant it the alter user privilege. That is dangerous, of course, as that user might then decide to change the SYSTEM password and login as SYSTEM instead!

You can see what I mean in the SQL*Plus session below, which I believe I tested on an Oracle 9 database. The SQL*Plus session was started from a UNIX session, where I was logged in as user reida. I haven't included the bit about changing the SYSTEM password as I have already covered it here

SQL> grant create session to reida
  2  identified by reida1
  3  /

Grant succeeded.

SQL> conn reida/reida1
Connected.
SQL> alter user reida identified by reida2
  2  /

User altered.

SQL> alter user reida identified externally
  2  /
alter user reida identified externally
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn system/manager
Connected.
SQL> alter user reida identified externally
  2  /

User altered.

SQL> conn /
Connected.
SQL> show user
USER is "REIDA"
SQL> alter user reida identified by reida3
  2  /
alter user reida identified by reida3
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn system/manager
Connected.
SQL> grant alter user to reida
  2  /

Grant succeeded.

SQL> conn /
Connected.
SQL> show user
USER is "REIDA"
SQL> alter user reida identified by reida4
  2  /

User altered.

SQL> alter user reida identified externally
  2  /

User altered.

SQL>

No comments: