Re: HELP with grants

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 23 Jan 1999 02:05:10 GMT
Message-ID: <36a92d48.1103737@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 Fri Jan 22 1999 - 20:05:10 CST