Created a user with the Database | Create | User menu, granting Connect and Resource.
ONLY "GRANT" checked and the "DEFAULT" column was not checked.
Connecting with that same user using Session | New connection generates error: 'User lacks create session'.
Creating the same user in SQLPlus granting connect and resource there is no problem connecting.
Similar results are generated with granting DBA to an user.
Doing a SQL Monitor, if the "DEFAULT" column is not checked when creating a user in Toad, it runs the following statement:
ALTER USER <username> DEFAULT ROLE NONE;
And with that statement, the "GRANT" does not seem to take affect.
However, if one checks the "DEFAULT" columns in Toad along with the "GRANT" column then Toad will run this:
ALTER USER ray DEFAULT ROLE "CONNECT", "RESOURCE"
With this statement, the user has the ability to connect.
The same results are obtained with the DBA role privilege.
WORKAROUND:
Check the "DEFAULT" column along with "GRANT".
STATUS:
Waiting for fix in a future version of Toad for Oracle.
If one executes the SQL monitored from Toad, manually in SQL Plus, one can prove the above findings:
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 3 08:06:32 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create user a identified by a;
User created.
SQL> grant resource to a;
Grant succeeded.
SQL> alter user a default role none;
User altered.
SQL> conn
Enter user-name: a
Enter password: *
ERROR:
ORA-01045: user A lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn
Enter user-name: danny
Enter password: *****
Connected.
SQL> drop user a;
User dropped.
SQL> create user a identified by a;
User created.
SQL> grant connect to a;
Grant succeeded.
SQL> grant resource to a;
Grant succeeded.
SQL> conn
Enter user-name: a
Enter password: *
Connected.
SQL>
