Re: update table priv's in roles not working in 11G
From: gs <gs_at_gs.com>
Date: Wed, 12 May 2010 18:24:48 GMT
Message-ID: <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.
Date: Wed, 12 May 2010 18:24:48 GMT
Message-ID: <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. Received on Wed May 12 2010 - 13:24:48 CDT