Hi Yogesh,
>> User2 should not have "drop" privillege on his own tables.
They will only get what you give them!
********************************************
>> when user1 will create tables , the user2 will have "select" access on those tables automatically.
Not automatically. You would have to write a DDL trigger. When the user is added, a script launches to grant accress to the tables:
Something like this (not tested)
CODE
[create or replace trigger
add_privs
AFTER DDL ON DATABASE
BEGIN
-- verify that user2 has no user1 privileges
-- If no provileges, collect all tables from user 1 and execute:
spool /tmp/runme.sql
set feedback off
connect user1.pawwrord;
select 'grant select on '||user1'||.||table_name||' to '||'user2||';'|| from user_tables;
spool off;
@/tmp/runme
END;
>> How can i grant "select" on a all tables of a user1 to another schema(user2)
What is your aversion to "select any table"?
CODE
connect user2/pawwrprd;
grant dba to user2;
grant select any table to user1;