Re: How to Grant 100 Tables to User?

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 11 Feb 2003 19:08:27 -0800
Message-ID: <92eeeff0.0302111908.1b9539a@posting.google.com>

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<pan.2003.02.11.23.25.47.894220_at_yahoo.com.au>...
> On Tue, 11 Feb 2003 15:21:08 +0000, Hari Om wrote:
>
> > Hi:
> >
> > I have a DB User LARRY and he has around 100 Tables (and other objects). I
> > want to GRANT ALL these 100 Tables to another User GATES. How can I do
> > that? I know I could do it manually eg: GRANT ALL ON TABLE1 to GATES;
> > GRANT ALL ON TABLE1 to GATES;
> > ...and so on.... but is there any way to do that only once (in one
> > statement)?
> >
> > Secondly, when user GATES accesss Tables from user LARRY (after GRANTS is
> > successful) then GATES has to use LARRY.TABLE1? So is there any way for
> > GATES to access tables without prefixing LARRY? Do I have to CREAT SYNONYM
> > for all the 100 Tables...? Or is there any better approach?
>
> Gates could do an 'alter session set current_schema=LARRY', and then do a
> select * from table1... and he'll be selecting from Larry's table 1.
>
> But synonyms are probably a better bet:
>
> SQL> create public synonym employees for scott.emp;
> Synonym created.
>
> SQL> create user mickey identified by mouse;
> User created.
>
> SQL> grant create session to mickey;
> Grant succeeded.
>
> SQL> grant select on employees to mickey;
> Grant succeeded.
>
> SQL> connect mickey/mouse
> Connected.
> SQL> select * from employees;
>
> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
> ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
> 7369 SMITH CLERK 7902 17-DEC-80 800
> 20
> 7499 ALLEN SALESMAN 7698 20-FEB-81 1600
>
> and so on
>
> Notice the select privilege was granted on the synonym, not the underlying
> table. It suffices.
>
> Regards
> HJR
>
>
>
> >
> > Thanks!
> >
> > HARI OM
To answer first part of you question, here is another approach. Login as Larry into sqlplus and run this script,

DECLARE
   str_run_ VARCHAR2(200);
   CURSOR grant_objects_ IS

      SELECT object_name
      FROM user_objects
      WHERE object_type = 'TABLE';        

BEGIN
   FOR rec_ IN grant_objects_ LOOP

      BEGIN
         str_run_ := 'GRANT ALL ON '||rec_.object_name||' to GATES';
         --
         EXECUTE IMMEDIATE str_run_;
         EXCEPTION
            WHEN OTHERS THEN
               BEGIN
                  NULL;
               END;
      END;                    
   END LOOP;                      

END;
/

Regards
/Rauf Sarwar Received on Tue Feb 11 2003 - 21:08:27 CST