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

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 12 May 2010 20:29:33 +0200
Message-ID: <4beaf38d$0$3702$426a74cc_at_news.free.fr>


"gs" <gs_at_gs.com> a écrit dans le message de news: QnCGn.3824$Z6.2712_at_edtnps82...
| Michel Cadot wrote:
| > "gs" <gs_at_gs.com> a écrit dans le message de news: kNzGn.3820$Z6.3665_at_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 Received on Wed May 12 2010 - 13:29:33 CDT

Original text of this message