This content has been marked as final. Show 7 replies
There is no single command for this.
You could, of course, leverage dynamic SQL, i.e.
Of course, if you're doing this sort of thing, you'd normally grant the privileges to a role and grant that role to one or more users.
FOR x IN (SELECT * FROM user_tables) LOOP EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.table_name || ' TO <<someone>>'; END LOOP;
Login as your user name and then run the following
cursor c1 is select table_name from user_tables;
for c in c1 loop
cmd := 'GRANT SELECT ON '||c.table_name||' TO YOURUSERNAME';
execute immediate cmd;
I must thank all (Justin Cave, user617896, Adith) for quick reply. But, i was thinking there may be a command like "GRANT SELECT TO ALL TABLES TO <user_name>;" Thank you so much.
If you still have any doubts you might review GRANT statement;
Also mention your database version while posting questions.
There is no such grant. There is the 'SELECT ANY TABLE' privilege, but if you grant that to a user, he would not be limited to selecting from tables in a specific schema, he would be able to select from any table in any schema in the database, execpt for SYS.
This is a DBA-level privilege, and should not be given to any non-DBA users.
I think Justin's suggestion is your best bet.
I appreciate your suggestion. You threw some more light on this topic. Thank you