AboutPeter Choi Expertise I am a senior Oracle DBA, PeopleSoft Administrator and Project Manager with
10+ years experience. I have been working with PeopleSoft (HRMS 5, 7, 7.x and
8.9), Oracle RDBMS (7.3 - 11gR1) on various Unix and Windows platforms, and some Oracle Application Server (9i/10gR2). I also have experience with the configuration and administration of BEA`s Tuxedo and WebLogic for PeopleSoft 8.x.
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
