Prev: tahiti.oracle.com down again?
Next: 2 QA Analyst and 1 QA Lead - New york city,NY
From: gs on 12 May 2010 11:27
One of the databases I recently converted to 11Gr2 is showing me errors
when an outside process tries to update a table via ODBC connection.
This is a simple SQL update statement and is not from within a stored
procedure. I am getting ORA-01031 after pointing the process to the new
database, I checked the users roles, object priv's etc. and is identical
to 9i, role basically gives said user read/write/update/delete on all of
another users tables, one of which is the problem table.

I tried the statement directly from sql as the user, and still threw a
ORA-01031, so I granted update on the table directly and it was ok.

Why would this be different in 11G? I'm also getting some strange errors
where some of these processes are throwing "table not found" errors when
using a public synonym for the table, yet the synonyms are all in
place and work fine from SQL prompt when using them with the same user
acct the processes use.


thanks

From: Michel Cadot on 12 May 2010 12:08

"gs" <gs(a)gs.com> a �crit dans le message de news: kNzGn.3820$Z6.3665(a)edtnps82...
| One of the databases I recently converted to 11Gr2 is showing me errors
| when an outside process tries to update a table via ODBC connection.
| This is a simple SQL update statement and is not from within a stored
| procedure. I am getting ORA-01031 after pointing the process to the new
| database, I checked the users roles, object priv's etc. and is identical
| to 9i, role basically gives said user read/write/update/delete on all of
| another users tables, one of which is the problem table.
|
| I tried the statement directly from sql as the user, and still threw a
| ORA-01031, so I granted update on the table directly and it was ok.
|
| Why would this be different in 11G? I'm also getting some strange errors
| where some of these processes are throwing "table not found" errors when
| using a public synonym for the table, yet the synonyms are all in
| place and work fine from SQL prompt when using them with the same user
| acct the processes use.
|
|
| thanks
|

Assuming you didn't miss anything the cause may be that
roles identified by password or package are no more activated by
default even if they are declared as default roles.

Regards
Michel


From: gs on 12 May 2010 14:24
Michel Cadot wrote:
> "gs" <gs(a)gs.com> a �crit dans le message de news: kNzGn.3820$Z6.3665(a)edtnps82...
> | One of the databases I recently converted to 11Gr2 is showing me errors
> | when an outside process tries to update a table via ODBC connection.
> | This is a simple SQL update statement and is not from within a stored
> | procedure. I am getting ORA-01031 after pointing the process to the new
> | database, I checked the users roles, object priv's etc. and is identical
> | to 9i, role basically gives said user read/write/update/delete on all of
> | another users tables, one of which is the problem table.
> |
> | I tried the statement directly from sql as the user, and still threw a
> | ORA-01031, so I granted update on the table directly and it was ok.
> |
> | Why would this be different in 11G? I'm also getting some strange errors
> | where some of these processes are throwing "table not found" errors when
> | using a public synonym for the table, yet the synonyms are all in
> | place and work fine from SQL prompt when using them with the same user
> | acct the processes use.
> |
> |
> | thanks
> |
>
> Assuming you didn't miss anything the cause may be that
> roles identified by password or package are no more activated by
> default even if they are declared as default roles.
>
> Regards
> Michel
>
>
I checked the users that have default roles in dbcontrol and it shows
them as being default, so I assumed they were activated.

Also after some more digging I found that one role is identified by
password, and are not default roles assigned to users but are set when
logging in via application. Talking to the developer I find out they are
set to uppercase because he used powerbuilder and it turns out the main
user account that the app uses was not connecting so I set the
sec_case_sensitive logon to false and that fixed that problem, but I
still am getting errors when users try to update tables that have
sequences populating fields. I checked the role that the user has as
default and it has select on all the sequences.
From: Michel Cadot on 12 May 2010 14:29

"gs" <gs(a)gs.com> a �crit dans le message de news: QnCGn.3824$Z6.2712(a)edtnps82...
| Michel Cadot wrote:
| > "gs" <gs(a)gs.com> a �crit dans le message de news: kNzGn.3820$Z6.3665(a)edtnps82...
| > | One of the databases I recently converted to 11Gr2 is showing me errors
| > | when an outside process tries to update a table via ODBC connection.
| > | This is a simple SQL update statement and is not from within a stored
| > | procedure. I am getting ORA-01031 after pointing the process to the new
| > | database, I checked the users roles, object priv's etc. and is identical
| > | to 9i, role basically gives said user read/write/update/delete on all of
| > | another users tables, one of which is the problem table.
| > |
| > | I tried the statement directly from sql as the user, and still threw a
| > | ORA-01031, so I granted update on the table directly and it was ok.
| > |
| > | Why would this be different in 11G? I'm also getting some strange errors
| > | where some of these processes are throwing "table not found" errors when
| > | using a public synonym for the table, yet the synonyms are all in
| > | place and work fine from SQL prompt when using them with the same user
| > | acct the processes use.
| > |
| > |
| > | thanks
| > |
| >
| > Assuming you didn't miss anything the cause may be that
| > roles identified by password or package are no more activated by
| > default even if they are declared as default roles.
| >
| > Regards
| > Michel
| >
| >
| I checked the users that have default roles in dbcontrol and it shows
| them as being default, so I assumed they were activated.
|
| Also after some more digging I found that one role is identified by
| password, and are not default roles assigned to users but are set when
| logging in via application. Talking to the developer I find out they are
| set to uppercase because he used powerbuilder and it turns out the main
| user account that the app uses was not connecting so I set the
| sec_case_sensitive logon to false and that fixed that problem, but I
| still am getting errors when users try to update tables that have
| sequences populating fields. I checked the role that the user has as
| default and it has select on all the sequences.

As far as I know sec_case_sensitive has no relation with role activation
no more with the case of their name just the case of password.
If they are identfied by password they are not activated by default.

Now if your application activates the role et you still have the error
then you missed something.

Regards
Michel


From: Mladen Gogala on 12 May 2010 20:18
On Wed, 12 May 2010 15:27:12 +0000, gs wrote:

> One of the databases I recently converted to 11Gr2 is showing me errors
> when an outside process tries to update a table via ODBC connection.
> This is a simple SQL update statement and is not from within a stored
> procedure. I am getting ORA-01031 after pointing the process to the new
> database, I checked the users roles, object priv's etc. and is identical
> to 9i, role basically gives said user read/write/update/delete on all of
> another users tables, one of which is the problem table.
>
> I tried the statement directly from sql as the user, and still threw a
> ORA-01031, so I granted update on the table directly and it was ok.
>
> Why would this be different in 11G? I'm also getting some strange errors
> where some of these processes are throwing "table not found" errors when
> using a public synonym for the table, yet the synonyms are all in
> place and work fine from SQL prompt when using them with the same user
> acct the processes use.
>
>
> thanks

Did you switch to the new 11G security? 11G can distinguish between upper
case and lower case so your password may no longer match.



--
http://mgogala.byethost5.com
 |  Next  |  Last
Pages: 1 2
Prev: tahiti.oracle.com down again?
Next: 2 QA Analyst and 1 QA Lead - New york city,NY