Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: HELP with grants
Van
If I'm reading your problem correctly, you already have the answer in your PS. Security requirements (not Oracle, but to allow C1?? level security - forgive me if the numbers are incorrect) tell us that to be able to grant access to someone else's objects, that someone else MUST grant you direct access first. In other words, let's say SYS wants to grant access to FRED's objects. Even with all the privileges SYS has, FRED has to grant access with grant option to SYS, before SYS can pass these on.
What this means of course is you must be able to log on as FRED. To do this in your situation, the only approach that I know of is to change FRED's password, log on as FRED, run the grants, log back on as SYS and change FRED's password back to what it was. Sorry, that's the way life is! There is no elegant above board solution I'm aware of.
HTH. Pete
Van Messner wrote:
> I started this week at a company with six decent sized databases. Most
> of the objects in each have one owner - but there is some variation. The
> password is known for only one of the owners. I have been told that some
> older applications have the passwords coded and encrypted so I can't change
> the passwords of any of the object owners in the databases.
>
> I have access to SYS and to a fully privileged DBA user Tom who has a
> complete set of system privileges including GRANT ANY PRIVILEGE, INSERT ANY
> TABLE etc. All have the WITH ADMIN OPTION.
>
> When a user who is not the owner wants to get object rights to some
> object neither SYS nor Tom can grant them. Insufficient privileges says
> Oracle. I tried giving Tom all his system privileges directly in Enterprise
> Manager rather than through the DBA and other roles. Still no luck. I
> can't give Tom object privileges in Oracle manager says Oracle.
>
> Unless I'm mis-reading the documentation, I should be able to grant
> these object privileges given the system privileges already held by SYS and
> by Tom. Have I missed something along the way?
>
> TIA
>
> Van
>
> PS I know how to do it using the backdoor approach of cutting and pasting
> the encrypted passwords. But this is inelegant and prone to disastrous
> error if I do it enough times. I want to be able to do it in an aboveboard
> way.
--
Regards
Pete
Peter Sharman Email: psharman_at_us.oracle.com WISE Course Development Manager Phone: +1.650.607.0109 (int'l) Worldwide Internal Services Education (650)607 0109 (local)San Francisco
"Controlling application developers is like herding cats."
Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!"
Bruce Pihlamae, long term ORACLE DBA