Re: How to Grant 100 Tables to User?

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
Received on Tue Feb 11 2003 - 17:25:52 CST