Re: HELP with grants

Hello Thomas

    I must be misunderstanding something. This is a quote from the Oracle documentation "Once an object in the Oracle database has been created, it can be administered by either the creator of the table or by a user who has GRANT ANY PRIVILEGE available to them. Administration of a database object consists of granting privileges that will allow users to manipulate the object by adding, changing, removing, or viewing data in the database object."

    In enterprise manager I can see that both SYS and Tom have this system privilege. In Tom's case it was granted first through a role, then explicitly by SYS.

    Neither Tom nor SYS can grant update etc on the objects owned by another user.
Thanks for the other info,

Van

Thomas Kyte wrote in message <36a92d48.1103737_at_192.86.155.100>...
>A copy of this was sent to "Van Messner" <vmessner_at_netaxis.com>
>(if that email address didn't require changing)
>On Fri, 22 Jan 1999 20:44:14 -0500, you 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.
>
>there is no privilege "grant any privilege" in oracle.
>
>> 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?
>
>There is no privilege to grant the initial 'grant' to yourself. There is a
way
>to do it tho -- if you have CREATE ANY PROCEDURE and EXECUTE ANY PROCEDURE
(dba
>does). Consider the following:
>
>SQL> create or replace procedure scott.run_grant( grant_stmt in varchar2 )
> 2 as
> 3 exec_cursor integer default dbms_sql.open_cursor;
> 4 rows_processed number default 0;
> 5 begin
> 6 dbms_sql.parse(exec_cursor, 'grant '||grant_stmt,
dbms_sql.native);
> 7 rows_processed := dbms_sql.execute(exec_cursor);
> 8 dbms_sql.close_cursor( exec_cursor );
> 9 dbms_output.put_line( 'grant ' || grant_stmt );
> 10 exception
> 11 when others then
> 12 if dbms_sql.is_open(exec_cursor) then
> 13 dbms_sql.close_cursor(exec_cursor);
> 14 end if;
> 15 raise;
> 16 end;
> 17 /
>
>Procedure created.
>
>SQL>
>SQL> show user
>user is "TKYTE"
>
>SQL> exec scott.run_grant( 'all on emp to public' );
>grant all on emp to public
>
>PL/SQL procedure successfully completed.
>
>
>so, that created a procedure in scotts schema (tkyte has create any
procedure,
>he can do that). that procedure runs with scotts identity. that procedure
can
>only execute grants. tkyte can then run that (he has execute any
procedure).
>that grants the privs on emp to public....
>
>>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.
>>
>
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Service Industries
>Reston, VA USA
>
>--
>http://govt.us.oracle.com/ -- downloadable utilities
>
>---------------------------------------------------------------------------
-
>Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>
>Anti-Anti Spam Msg: if you want an answer emailed to you,
>you have to make it easy to get email to you. Any bounced
>email will be treated the same way i treat SPAM-- I delete it.
Received on Sat Jan 23 1999 - 15:21:09 CST