Are there any downsides to referencing tables from another schema using synonyms

oracleschemasynonyms

We are developing a modular system composed by an administration module and a few web apps. The database model is also split in that way: we have a schema for the admin module and another for each application. For example:

  • Users will be shared across the system (you register and are given access to one or more applications), so the Users table is in the admin schema.
  • Application ACME will generate reports and store them, so the ACME Reports table is in the ACME schema.

Simple enough, except ACME reports are associated with a user. We could simply have copies of the Users table in each schema, and update them each time the "users master table" (admin schema) is changed, but that would be extremely painful to work with. Instead, we are sharing the table, and creating synonyms to abstract the code from that fact.

So, in our admin schema:

create table admin_users ( ... );
grant select, references on admin_users to acme;

And in our ACME schema:

create synonym acme_users for admin.admin_users;
alter table acme_reports add constraint fk_user foreign key (user_id)
      references acme_users (id);

This works just fine and will keep the referential integrity across schemas, but are there any downfalls to this approach we should be aware of, or any best practices we should follow here? With regards to either accessing tables from another schema, or using the (private) synonyms.

All the schemas will be on the same Oracle database, so there shouldn't be any significant performance impact (compared to having all the tables in the same schema). Right?

If this approach is valid, is there any practical way to manage the grants? This works:

grant select, references on admin_table to app_schema

But the number of tables which need to be granted will grow over time, and so will the number of schemas. Whenever we add a new schema, we would have to grant it the permissions for all the shared tables.

We thought of granting the permissions to a role instead, and just giving that role to the new schemas. But apparently that doesn't work:

ORA-01931: cannot grant REFERENCES to a role

Is there any other way to manage it?

Best Answer

There is no performance issue in having an app access tables from multiple schemas. You are correct in that you do not want to duplicate data across multiple schemas. As for managing the privileges, grant the privs to a role, not the individual user. Then grant the role to the user(s). When new objects are created, grant the appropriate privs to the role, and any user to which the role was previously granted will pick up the new privs in any future sessions they create.

Related Question