Re: update table priv's in roles not working in 11G
Date: Tue, 18 May 2010 03:21:59 -0700 (PDT)
Message-ID: <8ccc5fa6-ed0d-4331-ac9c-7d9884cd7d0c_at_e28g2000vbd.googlegroups.com>
On May 13, 8:50 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "gs" <g..._at_gs.com> a écrit dans le message denews: jZVGn.3891$Z6.1250_at_edtnps82...| Michel Cadot wrote:
>
> | > "gs" <g..._at_gs.com> a écrit dans le message denews: 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
Hmm... How so? The default role should still be enabled automatically on grantee logon. Here's excerpt from the 11.2 docs:
[QUOTE]
DEFAULT ROLE Clause
Specify the roles granted by default to the user at logon. This clause
can contain only roles that have been granted directly to the user
with a GRANT statement, or roles created by the user with the CREATE
ROLE privilege. You cannot use the DEFAULT ROLE clause to specify:
•Roles not granted to the user
•Roles granted through other roles
•Roles managed by an external service (such as the operating system), or by the Oracle Internet Directory
•Roles that are enabled by the SET ROLE statement, such as passwordauthenticated
roles and secure application roles
[/QUOTE]
So if roles in question are directly granted to the user, are not
password-authenticated and are not managed externally, they should be
enabled on logon. However, I am not sure if indirect roles (that is,
roles granted to default roles) will be enabled in this case (they
should be, but maybe they are not.) So the OP should check
DBA_ROLE_PRIVS for all directly granted roles and make sure the list
is exhaustive.
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Tue May 18 2010 - 05:21:59 CDT