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.
1) if 10 tables is many, I hope you'll never face a real DB !!
2) just do :
select 'grant select on ' || table_name || ' to user2;'
from dba_tables
where owner = 'user1'
I don't think you can issue a select privilege to a user for a whole schema. You could grant the 'select any table' privilege although this would allow that user to see any table in any schema.
You could write a simple script to create the sql needed to grant the select privilege to all tables in a schema.....
Something like this would work....
spool grant_sel.sql
select 'grant select on '||table_name||' to user2;'|| from user_tables;
spool off
@grant_sel
This should produce a file (grant_sel.sql) that contains a grant statement for each table in the schema of the user that ran the script. The it simply runs the script to grant the privileges....
spool grant_sel.sql
select 'grant select on '||table_name||' to user2;'|| from user_tables;
spool off
@grant_sel
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 )
I would wrap the grant in an AUTONOMOUS TRANSACTION like:
PROCEDURE table_grant(table_name IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
error_tracking VARCHAR2(10);
BEGIN
execute immediate 'grant select on ' || table_name || ' to user2;'
END table_grant;
Then create a trigger that calls the procedure. I think it might work. No garuntees :-)
Reply With Quote
)
Bookmarks