Re: update table priv's in roles not working in 11G

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 13 May 2010 18:50:48 +0200
Message-ID: <4bec2dea$0$20663$426a74cc_at_news.free.fr>


"gs" <gs_at_gs.com> a écrit dans le message de news: jZVGn.3891$Z6.1250_at_edtnps82...
| Michel Cadot wrote:
| > "gs" <gs_at_gs.com> a écrit dans le message de news: YzTGn.3888$Z6.49_at_edtnps82...
| > | Mladen Gogala wrote:
| > | > 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.
| > | >
| > | >
| > | >
| > | I did, and that was actually causing one of our problems, in that a user
| > | account could not connect as the password it was passing on was
| > | uppercase. However the case sensitive is for logons only, I still have
| > | the issue of one user acct connecting ok, but not able to update some
| > | tables, it turns out it is not seeing some sequences to populate a
| > | table, granting select on the sequences in question directly fixed this,
| > | but I'm still curious why this is, as these priv's are in a role that
| > | the user has assigned, the role is default, and it worked fine in 9i..
| >
| > What about my previous answers?
| > The role is not activated even if it is a default one.
| >
| > Regards
| > Michel
| >
| >
| yes I'm still checking into that, there was a rush to get it working so
| I took the short way of granting "selects on x" etc. for users to get
| processes up and running, now I have to stand back and (re)look at
| things now to figure out what/why things didn't work, ie. why default
| roles were not activated in 11G

Because it is the correct behaviour and the previous one was wrong. In short, they fix a bug.

Regards
Michel Received on Thu May 13 2010 - 11:50:48 CDT

Original text of this message