Script Generation Incorrect for Password Protected Role

Toad for Oracle
1

12.2 gets this right with a query including:

Select name, password
from sys.user$
where TYPE# = 0
and NAME = ‘SPF_VIEWER’
and ((password is not null) and password not in (‘EXTERNAL’, ‘GLOBAL’, ‘APPLICATION’));

and produces

CREATE ROLE SPF_VIEWER IDENTIFIED BY VALUES ‘xxxxxxxx’;

12.6.0.53 and 13.0.0.80 Script generation produces:

CREATE ROLE SPF_VIEWER NOT IDENTIFIED;

When it is password protected:

SQL> SELECT role, password_required
FROM dba_roles
WHERE role IN (‘SPF_VIEWER’ )
ORDER BY role

ROLE PASSWORD_REQUIRED

SPF_VIEWER YES

I think it may be related to limited access to sys.user$ by accounts other than SYS. It is also making comparisons inaccurate. It would be better to show it with some undeterminable value vs. balnket not identified when this error is hit.

2

Hey Chuck,

It also depends on Oracle version…but I was able to reproduce this in Toad 13.0 with Oracle 12.1.0.2.

However, the current beta gives

CREATE ROLE PW_ROLE IDENTIFIED BY <password>;

… indicating that Toad knows there is a password but we can’t get even the encrypted version due to lack of privs.

Can you try it in the beta and make sure it works OK for you there?

3

I can’t use the Beta version here. It works in 13 with the explicit GRANT SELECT ON sys.user$ to my read only account. It doesn’t work if granted to our read only role.

A little more concerning is that the role script generation (SB tab and menu option) do not generate the “Object Grant” contents. I expected to see the GRANT SELECT ON sys.user$ after I assigned it to the role, but it isn’t generated.

I’m trying to do role comparisons in 2 separate isolated networks due to 12.2 behavior changes with subroles. I purposefully use read-only accounts in those environments and that role just has CREATE SESSION, EXEMPT ACCESS POLICY, SELECT ANY DICTIONARY, SELECT ANY TABLE, and SELECT_CATALOG_ROLE. I can look over the NOT IDENTIFIED vs. password, but missing grants is a confidence killer.

4

Sorry about that, Chuck.

Both problems were fixed early on in the 13.1 beta.

In 13.0, you can use the “Generate Database Script” window to get the role’s privileges.

5

Thanks for the info. Now I’m even in the dog house with 13. These “major” version number increases, i.e., from 12 to 13, force us into painful and lengthy registration drills before we can see the new toys. I probably made the same general gripe in the 10->11 and 11->12 number bumps.

6

I guess we’re in there together. You can have some of my Alpo. :slight_smile:

Regarding version numbers, you had a nice long ride in the 12’s at least. I was surprised that we didn’t jump to 13 much sooner. They’re only numbers, but 12.10, 12.11, and 12.12 - that was just silly.

1 Like