Re: Help! tables in oracle...

Somil Mittal wrote:
>
> Hi Guys,
>
> I am new to Orcale 7 and stuck. Here's my problem:
>
> I have to create a table which will be used by around 20 users who
> shall run their SQL queries on it. If I use the CREATE TABLE command,
> I can create the table only for one schema, thus the other users can't
> see the table. How can I create a table such that all users can run
> their SQL queries on them?
>
> Thanks in Advance,
>
> Somil Mittal
>
> ps. I would really appreciate help on this. Am desparate.

This example is Oracle version 6 style grants. In version 7 it can be done this way also, but I like to make all grants to a role, then grant the role to the user(s). I used two sessions to create this example then cut & pasted to get what you see here. The "----" lines are comments. Note that no rows are in the table but the text "no rows selected" indicates a successful select:

• SESSION/WINDOW 1 ---------------------- ------ user ops$oracle creates table my_tab SQL> show user user is "OPS$ORACLE" SQL> create table my_tab(field1 varchar2(20), field2 number); Table created.
• SESSION/WINDOW 2 ---------------------- ----- user bart fails to select from my_tab with or without ----- declaring ops$oracle's schema SQL> show user user is "BART" SQL> select * from my_tab; select * from my_tab * ERROR at line 1: ORA-00942: table or view does not exist SQL> select * from ops$oracle.my_tab; select * from ops$oracle.my_tab * ERROR at line 1: ORA-00942: table or view does not exist
• SESSION/WINDOW 1 ---------------------- ----- user ops$oracle grants select access on my_tab to bart SQL> grant select on my_tab to BART; Grant succeeded.
• SESSION/WINDOW 2 ---------------------- ----- user bart can only select from my_tab if ops$oracle's ----- schema is declared SQL> select * from my_tab; select * from my_tab * ERROR at line 1: ORA-00942: table or view does not exist SQL> select * from ops$oracle.my_tab; no rows selected
• SESSION/WINDOW 1 ---------------------- ----- user ops$oracle create a public synonym for my_tab SQL> create public synonym my_tab for my_tab; Synonym created.
• SESSION/WINDOW 2 ---------------------- ----- user bart can select with or without declaring ----- ops$oracle's schema SQL> select * from my_tab; no rows selected SQL> select * from ops$oracle.my_tab; no rows selected
• SESSION/WINDOW 1 ---------------------- ----- user ops$oracle revokes select access to my_tab ----- from bart SQL> revoke select on my_tab from BART; Revoke succeeded.
• SESSION/WINDOW 2 ---------------------- ----- user bart can no longer select from my_tab even ----- though the public synonym remains SQL> select * from my_tab; select * from my_tab * ERROR at line 1: ORA-00942: table or view does not exist SQL> select * from ops$oracle.my_tab; select * from ops$oracle.my_tab * ERROR at line 1: ORA-00942: table or view does not exist

-- 
                       \\|//
                       (0-0)
           +-----oOO----(_)-----------+
           | Brian P. Mac Lean        |
           | Database Analyst         |
           | brian.maclean_at_teldta.com |
           | http://www.teldta.com    |
           +-------------------oOO----+
                      |__|__|
                       || ||
                      ooO Ooo