I have created one user, 'user1' and have created (say 10) tables.Now, if i want another user (say 'user2') to view all the tables created by the user 'user1' How do i do it.Kindly enlighten me on this.
Sorrry, i for the sake of example i said 10 tables.What if i have so many tables and i can't keep giving grant statement for each & every table?Is there a way to grant 'select' previlege on the whole for the Schema of one user.
select 'grant select on '||table_name||' to user2;'|| from user_tables;
will work only for the existing set of tables.But actually i have around 1000 tables and what about the tables that will be created after executing the above script.I have been using only this to do.But this should be done after every addition of new tables and the tracing should be done for incremental grant.
As I said before... I'm not aware of the ability to grant a select privilege on all tables in a schema which would automatically grant the privilege to all new tables as they are created....
As far as I can see you have a couple of options....
1. grant 'user2' the 'select any' privilege. This will allow him/her to select from any table in the database including tables that are created after you issue the grant statement.
2. Run the script posted previously to grant the 'select' privilege on all existing tables in user1 schema (If run when connected as user1). Issue a grant for all new tables when they are created.
3. Run the script posted previously throughout the day....Pick a satisfactory time interval and schedule the script to run after the time interval has elapsed.
As far as I know, you can't have DDL statements in a DDL trigger (e.g. AFTER CREATE ON SCHEMA) and "GRANT SELECT ON table TO user" is a DDL right? (I am not sure on this one though! - please correct me if I am wrong )