hI,

How can I generate a script from the Oracle data dictionary, to grant privileges to all users, to all tables in a specific schema ?

Hi,
One of the methods that i can think of is that
create public synonym for all the tables and give all grants to public.
That way all users will have all access to all tables in a particular
schema.
The SQL to generate this script is as follows...

SELECT 'CREATE PUBLIC SYNONYM '||OBJECT_NAME||' FOR '||OBJECT_NAME||';'
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
UNION ALL
SELECT 'GRANT ALL ON '||OBJECT_NAME||' TO PUBLIC;'
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
ORDER BY 1
/

You can change this SQL to suit yourself. That if you want grant a
specific permission or role, you can do it. using this script.

hi,

thanks for this response.
this looks more like a query rather than the actual sql that is going to grant privileges to all users, to all tables in a specific schema. Am I correct or is this going to actually grant privileges to all users?

It is SQL that generates SQL. You run that SQL and the output is something like:

GRANT ALL ON aaa TO PUBLIC;
GRANT ALL ON bbb TO PUBLIC;
...

If you spooled that output to a file, you could then run that file to do the creates and grants:

SQL> @myfile.txt

An alternative that does it all in one go would be:

BEGIN
FOR r IN (SELECT table_name FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT ALL ON '||r.table_name||' TO PUBLIC';
END LOOP;
END;
/