Script for user mangled if password value contains a space

Howdy!

In 14.0.0.314 ("The Pi Edition™"?), I'm copying users from a 12.1 DB to a 19.6 one. One user, created when the source DB was v11.2, has a password with a raw value containing a space, e.g. "RJ 20160907". I had done this as an audit point to prevent any possible login on that schema, which owns objects accessed by other schemas (like using the newer "NO AUTHENTICATION" now).

When Toad creates a script for this user, it doesn't generate anything after the space, e.g.:

CREATE USER BLEAH
  IDENTIFIED BY VALUES 'RJ
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

...which of course is invalid syntax.

Thanks!
Rich

We recently started using dbms_metadata to get the encrypted password. I wasn't expecting spaces in it. Can you send me your original "create user" script? Or at least one that I can reproduce this with?

Hey John,

I saw that in the script output! The above script should be a good reproducible case, if fixed:

CREATE USER BLEAH
  IDENTIFIED BY VALUES 'RJ 20200507'
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

Let me know if that'll work for you.

Thanks!
Rich

Oh. Duh. You pretty much gave me a script to reproduce it already. Thanks for not making fun of me. :grin:

:mask: :sunglasses: :white_check_mark:

I don't have a specific meaning behind these emojis. It just looked like a fun reply. :smiley:

Does that trick still work for you on 19c? I'm seeing this when I try:
Edit: I also get the same error on 12cR1. Works on 11.2. I guess you kinda mentioned that.

Yeah, I had planned to update those accounts to "NO AUTHENTICATION". I think that update time frame just got accelerated to "right now". :grimacing:

Thanks John!
Rich

I'll fix it anyway, I see C## users with spaces too in the values string.

Giving you a heads up. I was migrating schemas from Oracle 11 to Oracle 19 using expdp/impdp and Oracle did not like my password encrypted value of "NO LOGIN ALLOWED". I ended up having to change the password before the export.

Hi Brian. That sounds like the same thing I ran across with that ORA-02153.