Help - Search - Members - Calendar
Full Version: privilege to user
Oracle DBA Forums > Oracle > Oracle Forum
yogesh.chandane73
Dear All,


User1 is having 10000 tables in his schema

How can i grant "select" on a all tables of a user1 to another schema(user2) so that in future when user1 will create tables , the user2 will have "select" access on those tables automatically.

I dont want user2 to have "select any table" privillege.

User2 should not have "drop" privillege on his own tables.


please help me
Thanks
burleson
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;
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.