Toad 12 for Oracle Read Only - and PUBLIC role/user

Toad for Oracle
1

Hi,

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.

2

I just tested this using Toad 12.0 and 12.1 (releasing Sep 19th).

Toad 12.0 fails as you explain – table T1 won’t allow data edits.

Toad 12.1 works as you expected – table T1 allows data edits.

My guess is Brad, John or one of the other dev gang will roll in later and confirm – plus say known bug and maybe quote a CR #.

I just happened to get up early – 4 AM – so I saw and tested this
J

From: newbie01.oracle [mailto:bounce-newbie01oracle@toadworld.com]

Sent: Tuesday, September 10, 2013 1:04 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Toad 12 for Oracle Read Only - and PUBLIC role/user

Toad 12 for Oracle Read Only - and PUBLIC role/user

Thread created by newbie01.oracle

Hi,

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.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

3

On 10/09/13 07:03, newbie01.oracle wrote:

*Toad 12 for Oracle Read Only - and PUBLIC role/user
Does this thread help any? http://www.toadworld.com/products/toad-for-oracle/f/10/t/19945.aspx
-- Cheers,
Norm. [TeamT]

4

Yeah we started checking for the update privilege in Toad 11.something for reasons I won’t go into now. We forgot about checking for grants made to PUBLIC
at that time, but as Bert mentioned, this is fixed for Toad 12.1.

Any of these will get you around the problem.

Upgrade to Toad 12.1

Do a CTRL+E on the table name in the Schema Browser when you want to edit the data. That will send an editable query to the Editor.

Log in as the table owner to make the edits.

Grant UPDATE ANY TABLE to the users in question.

Grant UPDATE on the tables in question to the users in question

Grant UPDATE on the tables in question to a role, which is granted to the users in question.

From: newbie01.oracle [mailto:bounce-newbie01oracle@toadworld.com]

Sent: Tuesday, September 10, 2013 1:04 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Toad 12 for Oracle Read Only - and PUBLIC role/user

Toad 12 for Oracle Read Only - and PUBLIC role/user

Thread created by newbie01.oracle

Hi,

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.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

5

Hi all,

Thanks a lot everyone for responding, most specially to Bert for doing a quick test.

We will do the upgrade to 12.1 and check how it behaves. John’s suggestion also worked, I’ve tested that before, but doing that to all the databases we support will be a very lengthy and time consuming exercise not to mention that we might inadvertently grant update by mistake to others :(-

I believe the lost in translation with Toad 12 is due to it “deciding” that PUBLIC full access is a no-no and is a security hole and so any PUBLICly granted table should be Read Only.

I am beginning to like this FORUM, I got better, quicker and a friendlier response not to mention Bert doing some tests.

Before I sign off, can anyone confirm whether they’ve used Toad 9 on Windows 7?

Thanks again fellas

6

Glad we could help – the forums have been a great tool and asset for us from way back. We started out on CompuServe, then e-groups which became Yahoo and now
our own Toad World web site. Toad 12.1 is still on track to release next Thursday Sep 19th . It will go live on our support website and you should be able to download it (assuming you are current on maintenance). You just download and install – it
can do side by side install so you can keep current version until you test drive and approve the new version. It should offer option to copy over your settings – and if not remember you can do this via Main Menu -> Utilities -> Copy User Settings
J

I’ve done Toad 9.7 on Windows 7. Remember that regardless of your Windows being 64-bit – prior to 11.6 Toad was 32-bit and as such required 32-bit Oracle client.
See this
blog for more info.

Thanks for the nice reply too – it’s nice to hear our efforts are appreciated.

Bert

From: newbie01.oracle [mailto:bounce-newbie01oracle@toadworld.com]

Sent: Tuesday, September 10, 2013 6:28 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad 12 for Oracle Read Only - and PUBLIC role/user

RE: Toad 12 for Oracle Read Only - and PUBLIC
role/user

Reply by newbie01.oracle

Hi all,

Thanks a lot everyone for responding, most specially to Bert for doing a quick test.

We will do the upgrade to 12.1 and check how it behaves. John’s suggestion also worked, I’ve tested that before, but doing that to all the databases we support will be a very lengthy and time
consuming exercise not to mention that we might inadvertently grant update by mistake to others :(-

I believe the lost in translation with Toad 12 is due to it “deciding” that PUBLIC full access is a no-no and is a security hole and so any PUBLICly granted table should be Read Only.

I am beginning to like this FORUM, I got better, quicker and a friendlier response not to mention Bert doing some tests.

Before I sign off, can anyone confirm whether they’ve used Toad 9 on Windows 7?

Thanks again fellas

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.