0

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?

2

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.

5
  • Thanks for the answer! I would love to grant the privileges to a role, but Oracle wont let me. I can grant select, but apparently grant references is only allowed when the grantee is a user, not a role. Maybe there is a workaround but I couldn't find anything
    – AJPerez
    May 18 '21 at 16:16
  • Well, if you were to show the exact GRANT statement that failed, and the exact error you got in return, then we could address that. "Doesn't work" contains zero actionable information. I suspect you are trying to apply MSSQL lessons to oracle, and that will never work.
    – EdStevens
    May 18 '21 at 16:55
  • BTW, I hope you are not trying to grant user/schema "A" the ability to create objects in user/schema "B". There should be no reason to do this.
    – EdStevens
    May 18 '21 at 16:59
  • I did show the exact grant statement and the exact error code. They are right there in my question. So.... yeah.
    – AJPerez
    May 18 '21 at 17:52
  • OK, after a bit more reading, no it cannot be granted to a role. But also after reviewing what it does (I've never looked at granting REFERENCES before) I'd say you shouldn't be needing to grant it. That's not a priv that your application schemas should need. Creating FK should be done as part of creating a table (by the table owner) in the first place. Why do you need to grant REFERENCES to someone else?
    – EdStevens
    May 18 '21 at 21:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.