Expert: Peter Choi - 10/16/2009

Question
Hi there!
we are using oracle 8.0.5...we have all tables in one schema called I_local..how can i map all users (schema) in this shared schema?

pelase help!

thanks in advance,
beth

Answer
Hi Lilibeth,

You can create public synonyms and then grant privileges (SELECT, INSERT, UPDATE, DELETE) to a role.

Then grant the role to the users. This way if you were to add or drop a table, updating one role is easier than re-visiting all the users.

I generally build a script that queries the Oracle Data Dictionary to create the public synonym and then grant access to the role.

In this example, my ROLENAME = myRole (assume that the role has been created).

spool create_syn.sql;
SELECT "CREATE PUBLIC SYNONYM ON " || <tablename> ||" FOR " <owner> || "." || <tablename> ||";"
FROM DBA_TABLES
WHERE OWNER="I_LOCAL"

SELECT "GRANT SELECT ON " || <tablename> ||" TO " myRole ||";"
FROM DBA_USERS
WHERE USERNAME="<list of users>";
spool off;

@create_syn.sql


Once the role has populated, all you need to do is issue a grant user to the role

GRANT USER lilibeth to MYROLE;

Hope this helps.

Peter