I am hoping that someone is having the same problem that I have and have a workaround to it via some Toad config settings instead of the current workaround that we may need to do.
We’ve recently upgraded from Windows XP to Windows 7 and upgraded Toad from Version 9 to 12. Oracle Client 11g is used with Toad 12, with Toad 9, we are using Oracle10g as Oracle11g is not supported on that version of Toad.
After the upgrade, some tables that has been granted full access to PUBLIC are now showing as Read Only. If we grant full access to these tables to a role that a user has already been granted to, then the table does not show as Read Only on Toad.
To illustrate this, for example, we have tables T1 and T2 and role ROLE01 for username USER01
- FULL access, SELECT, INSERT, UPDATE, DELETE has been granted to table T1 to PUBLIC
- FULL access, SELECT, INSERT, UPDATE, DELETE, has been granted to table T2 to the role ROLE01
- USER01 has been granted the role ROLE01
- When using Toad 12 and USER01, Data for T1 is showing as Read Only, T2 is not showing as Read Only. We do not have this same behaviour with Toad 9
- If we grant FULL access to T2 to ROLE01, then Data for T1 and T2 is NOT showing as Read Only
So, in theory, the current workaround on the database end is to either revoke FULL ACCESS from PUBLIC and grant them to the roles or grant PUBLIC to the user. While this is a secure solution in a sense, it will involves a lot of time consuming testing. Toad has been working as desired in the last 10 years with the database the way it is, so we are hoping we can make the change in Toad instead of in the database end.
Can you please advise if this is the expected behaviour on this version of Toad? If not, can anyone please advise if there is any settings that we can change to get around this so that tables that has been granted FULL access to PUBLIC but not to user-granted roles will not show as Read Only.
Any advise will be much appreciated. Thanks in advance.