Home » Developer & Programmer » Forms » ORA-01031: insufficient privileges in Forms (Forms 11G)
ORA-01031: insufficient privileges in Forms [message #672121] Wed, 03 October 2018 07:51 Go to next message
RaShi.Raj
Messages: 82
Registered: May 2014
Location: Bangalore
Member
Hi all,

I have new table (my_table) used in a new form which is given all required grants and privileges. When forms user, same as oracle user ( user1 ) tries to enter some data in forms and save, forms throw error 'ORA-01031 - Insufficient Privileges' while executing INSERT INTO statement.
If I login to oracle via back end as the same user (user1) and run the same INSERT statement, it is successfully executed.
Any idea what is happening in forms ?

Thanks,
Rashi
Re: ORA-01031: insufficient privileges in Forms [message #672124 is a reply to message #672121] Wed, 03 October 2018 08:23 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
Maybe there is a forms trigger that is trying to use another table or worse, a database trigger that is does not have access to a table.
I hope you are not using an INSERT INTO in the code of the form to insert into the table the block is based on, because that is not how forms works.
Re: ORA-01031: insufficient privileges in Forms [message #672125 is a reply to message #672124] Wed, 03 October 2018 08:42 Go to previous messageGo to next message
RaShi.Raj
Messages: 82
Registered: May 2014
Location: Bangalore
Member
Hi Joy,

I'm using "insert into table_1...." which is not used as a DB block. If there a is database trigger that does not have access to a table then INSERT via back end should also throw this error right? BTW, this is happening in one of the environments.

Thanks,
Rashi
Re: ORA-01031: insufficient privileges in Forms [message #672126 is a reply to message #672125] Wed, 03 October 2018 10:01 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Try logging into sqlplus as the user then issue:
set role none;

then run the insert.
Re: ORA-01031: insufficient privileges in Forms [message #672127 is a reply to message #672126] Wed, 03 October 2018 10:18 Go to previous messageGo to next message
RaShi.Raj
Messages: 82
Registered: May 2014
Location: Bangalore
Member
Unfortunately this is happening at client's system which i don't have access to. However, I can ask them to try these if I know what I'm trying to do with this. Could you please tell me that? also, execute_query on a block in the same form is failing (no records fetched). I can recreate that on my environment by revoking 'SELECT' privilege from that table. But I'm sure they have the required privilege because they can access via back end but not on forms.
Re: ORA-01031: insufficient privileges in Forms [message #672130 is a reply to message #672127] Wed, 03 October 2018 11:55 Go to previous messageGo to next message
BlackSwan
Messages: 26763
Registered: January 2009
Location: SoCal
Senior Member
RaShi.Raj wrote on Wed, 03 October 2018 08:18

But I'm sure they have the required privilege because they can access via back end but not on forms.

You can be sure & wrong at the same time.
Privilege acquired via ROLE do NOT apply within named PL/SQL procedure (but work OK in anonymous PL/SQL block).
Re: ORA-01031: insufficient privileges in Forms [message #672131 is a reply to message #672125] Wed, 03 October 2018 12:45 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
RaShi.Raj wrote on Wed, 03 October 2018 09:42
Hi Joy,

I'm using "insert into table_1...." which is not used as a DB block. If there a is database trigger that does not have access to a table then INSERT via back end should also throw this error right?
Good point, but...after reading further in this posting, you said it happens at a client site that you do not have access to. If so, then how do you know a regular insert works?
Differences in environments can have any of the following:

different roles
different grants
etc.

You would have to compare everything between the tow environments
Re: ORA-01031: insufficient privileges in Forms [message #672136 is a reply to message #672131] Thu, 04 October 2018 00:54 Go to previous messageGo to next message
RaShi.Raj
Messages: 82
Registered: May 2014
Location: Bangalore
Member
joy_division wrote on Wed, 03 October 2018 23:15
RaShi.Raj wrote on Wed, 03 October 2018 09:42
Hi Joy,

I'm using "insert into table_1...." which is not used as a DB block. If there a is database trigger that does not have access to a table then INSERT via back end should also throw this error right?
Good point, but...after reading further in this posting, you said it happens at a client site that you do not have access to. If so, then how do you know a regular insert works?
Differences in environments can have any of the following:

different roles
different grants
etc.

You would have to compare everything between the tow environments
Joy, we asked them to try insert via backend for the same user where they get error on forms and insert worked just fine. Role_tab_privs table shows all privilege for that table and role.
Re: ORA-01031: insufficient privileges in Forms [message #672137 is a reply to message #672130] Thu, 04 October 2018 00:58 Go to previous messageGo to next message
RaShi.Raj
Messages: 82
Registered: May 2014
Location: Bangalore
Member
BlackSwan wrote on Wed, 03 October 2018 22:25
RaShi.Raj wrote on Wed, 03 October 2018 08:18

But I'm sure they have the required privilege because they can access via back end but not on forms.

You can be sure & wrong at the same time.
Privilege acquired via ROLE do NOT apply within named PL/SQL procedure (but work OK in anonymous PL/SQL block).
So, granting privilege explicitly for that particular user instead through role is worth trying?
Re: ORA-01031: insufficient privileges in Forms [message #672139 is a reply to message #672137] Thu, 04 October 2018 03:19 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd try my test first.
I think forms can work with roles, but I'm not sure and can't check.

As stated above, something has to be different between failing system and the working test - user, privs or code.
Does the insert use any functions?
Are you absolutely sure it's the insert that throws that error rather than some other code?
Re: ORA-01031: insufficient privileges in Forms [message #672142 is a reply to message #672139] Thu, 04 October 2018 04:38 Go to previous messageGo to next message
RaShi.Raj
Messages: 82
Registered: May 2014
Location: Bangalore
Member
Thanks you All,

We are now able to insert after giving privileges explicitly to the particular user (instead through role). Is this something that should be taken care of in the problem environment ?
Re: ORA-01031: insufficient privileges in Forms [message #672144 is a reply to message #672142] Thu, 04 October 2018 05:46 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you don't take care of it how are you going to make it work?
Re: ORA-01031: insufficient privileges in Forms [message #672148 is a reply to message #672144] Thu, 04 October 2018 06:29 Go to previous messageGo to next message
RaShi.Raj
Messages: 82
Registered: May 2014
Location: Bangalore
Member
cookiemonster wrote on Thu, 04 October 2018 16:16
If you don't take care of it how are you going to make it work?
I mean then its the responsibility of the client to take care of the issue. Wondering just in case is this anything to do with forms build as this was the first time we are delivering a new form since we migrated to 11G from 6i.
Re: ORA-01031: insufficient privileges in Forms [message #672158 is a reply to message #672148] Thu, 04 October 2018 08:31 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
privs are in the DB not the forms build.
Re: ORA-01031: insufficient privileges in Forms [message #672159 is a reply to message #672148] Thu, 04 October 2018 08:31 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
There would be no difference between for 6i, 11g or 12c as far as needed privileges. It has nothing to do with Forms. Forms runs a a user, sqlplus runs as a user. Forms can be seen as a front-end to sqlplus. Yes, there are difference as to what can be done in forms vs sqlplus, but as far as privileges, no difference.
Re: ORA-01031: insufficient privileges in Forms [message #672160 is a reply to message #672159] Thu, 04 October 2018 08:43 Go to previous message
RaShi.Raj
Messages: 82
Registered: May 2014
Location: Bangalore
Member
Thank you both Smile

[Updated on: Thu, 04 October 2018 08:43]

Report message to a moderator

Previous Topic: FRM:40508 unable to insert record.
Next Topic: run_report_object with parameter form
Goto Forum:
  


Current Time: Fri May 14 18:55:14 CDT 2021