Re: Grant select on another owners table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/03/21
Message-ID: <351424f7.5846126@192.86.155.100>#1/1

A copy of this was sent to "wayne" <visstar_at_singnet.com.sg> (if that email address didn't require changing) On 21 Mar 1998 13:15:11 GMT, you wrote:

>Logging in as sys or system, I'm
>1) creating a new user,
>2) granting "select on" , "update" etc privilege on tables from different
>schema to a role,
>3) create the appropriate synonyms for the tables
>3) assign the role to the new user
>
>I know I can log in as the owners of the tables and grant the rights to the
>role. But this is not a good way.
>
>How can I do it centrally without login into each schema?
>
>Someone suggested
>Alter session set current schema [username]
>But there is no such syntax.
>
>Pls help.
>bart
>
>wing66_at_yahoo.com

If you have the 2 privs (as SYS would)

• create any procedure
• execute any procedure

Then the following will work:

create or replace procedure scott.do_grant( p_what   in varchar2,
                                            p_onwhat in varchar2,
                                            p_towho  in varchar2 )
as
    exec_cursor     integer default dbms_sql.open_cursor;

    rows_processed number default 0;
begin

    dbms_output.put_line( 'grant ' || p_what ||
                            ' on '   || p_onwhat ||
                            ' to '   || p_towho );
    dbms_sql.parse(exec_cursor, 'grant ' || p_what ||
                                ' on '   || p_onwhat ||
                                ' to '   || p_towho, dbms_sql.native );

    rows_processed := dbms_sql.execute(exec_cursor);     dbms_sql.close_cursor( exec_cursor ); exception

    when others then

      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      raise;

end;
/

create table scott.test_table ( x int ); exec scott.do_grant( 'select', 'test_table', 'public' );

drop procedure scott.do_grant;

You would create a procedure for SCOTT that uses dbms_sql. This procedure can only do 'grants' for scott. Since you have create any procedure, you can create it. You then create objects for SCOTT and execute scott.do_grant for the objects (since you have execute any procedure you can do this). after you are done granting for scott, drop the do_grant procedure to clean up.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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 Mar 21 1998 - 00:00:00 CST