Bug in Toad for Oracle 12.11.0.95

Toad for Oracle & DevOps Toolkit
1

I have encountered a bug in the new Toad for Oracle (12.11.0.95) that was

not in the 12.10 release.

I have not seen this bug reported, so here goes.

When you create a new user using the Clone/Copy option and the username has

a period (".") in it, the user creates correctly, but the roles and synonyms

error out because there are no quotes around the user name in the grant

roles scripts. The quotes do exist in the create user part of the script,

so the user is created correctly, but no roles or synonyms.

Lynn Olpin

2

So, you want to say that your roles have not “usual” naming convention?

I.e. for “normal” naming" - MY_ROLE

I.e. for “non-normal” naming", where you have some special characters like blank in this case - “MY ROLE”

Brg
Damir

3

Hey Lynn,

I just tried this here and for me it is working OK. I created this user and then cloned it to a user called “a.b”. Can you make some changes to my script so that it will fail?

Thanks.

drop user “a.b” cascade;
drop user “User.With.Dot” cascade;
drop role “Role.With.Dot”;
create user “User.With.Dot” identified by userwithdot;
create role “Role.With.Dot”;
grant “Role.With.Dot” to “User.With.Dot”;
grant connect to “User.With.Dot”;
grant select on tables to “User.With.Dot”;
grant create any table to “User.With.Dot”;
create synonym “User.With.Dot”.“Syn.Dot” for scott.emp;

4

The script that Toad is generating for me would be the same, except that when it does the grant and synonym options, there are no quotes around the user name. So if I clone, I get:

create user "User.With.Dot" identified by userwithdot;
grant connect to User.With.Dot;
grant select on tables to User.With.Dot;
grant create any table to User.With.Dot;
create synonym User.With.Dot.emp_syn for scott.emp;

I imported the Toad options from 12.10, so unless something changed, it should be the same.

I just tried again. I got a “ORA-00933: SQL command not properly ended” error right away. I chose Ignore all and Yes to show the output which gave me:

SQL> SET DEFINE OFF

SQL> CREATE USER "TEST.USER"

IDENTIFIED BY Th1s_IS_$_T3st

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

PROFILE IMDB_USER

ACCOUNT UNLOCK

User created.

SQL> -- 4 Roles for "TEST.USER"

SQL> GRANT CONNECT TO TEST.USER

GRANT CONNECT TO TEST.USER

Error at line 11

ORA-00933: SQL command not properly ended

SQL> GRANT IMDB_GENERAL_USER TO TEST.USER

GRANT IMDB_GENERAL_USER TO TEST.USER

Error at line 12

ORA-00933: SQL command not properly ended

SQL> GRANT IMDB_SECURITY TO TEST.USER

GRANT IMDB_SECURITY TO TEST.USER

Error at line 13

ORA-00933: SQL command not properly ended

SQL> GRANT TOAD_RESTRICTIONS_FULL TO TEST.USER

GRANT TOAD_RESTRICTIONS_FULL TO TEST.USER

Error at line 14

ORA-00933: SQL command not properly ended

SQL> ALTER USER "TEST.USER" DEFAULT ROLE ALL

User altered.

I hope this helps.

Lynn

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]
Sent: Thursday, July 27, 2017 5:58 AM
To: toadoracle@toadworld.com
Subject: [Non-DoD Source] RE: [Toad for Oracle - Discussion Forum] Bug in Toad for Oracle 12.11.0.95

RE: Bug in Toad for Oracle 12.11.0.95

Reply by John Dorlon

Hey Lynn,

I just tried this here and for me it is working OK. I created this user and then cloned it to a user called "a.b". Can you make some changes to my script so that it will fail?

Thanks.

create user "User.With.Dot" identified by userwithdot;
grant connect to "User.With.Dot";
grant select on tables to "User.With.Dot";
grant create any table to "User.With.Dot";
create synonym "User.With.Dot".emp_syn for scott.emp;

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

5

Does it work if you add your own double-quotes in the Clone/Copy dialog?

6

IDENTIFIED BY Th1s_IS_$_T3st
Oracle best practice is to put passwpord values in ".

i.e.

IDENTIFIED BY "Th1s_IS_$_T3st"

7

I’ve made a change for next beta so that double-quotes will be added automatically if needed because of special characters. In this case, whatever you type in will be case sensitive. If no special characters are present, we won’t add any quotes and Oracle will uppercase it automatically. Of course you can always add your own double quotes to get exactly what you want.

8

Hi John,

Please for password always put inside double quotes.

Why?

So that last sign of password can be a blank!
This is what I saw on some clients envs…

Brg

Damir

9

Not all environments have case sensitive passwords. User can always add their own double-quotes in password fields.

10

Actually, I take this back. I’m not going to auto-add double quotes. I am changing our global “object name check” routine (which now checks primarily for object name length) so that it now also includes a check to see if double-quotes are required. And if so, it’ll tell you that. This way, the change will apply to everywhere that uses that routine (all the create/alter windows) and not just “clone user”.

11

Hi John,

Maybe I was not clear.

for password "Qwertz " (blank is on the end) you must do the quotes.

:slight_smile: