Thanks for the question, Dinesh.
Asked: July 10, 2021 - 12:32 am UTC
Last updated: July 12, 2021 - 1:54 pm UTC
Version: 19c
Viewed 1000+ times
You Asked
I'm looking for a suggestion. We are pulling data from different SOR's into MS SQL server, so we have created a separate schema for each SOR in MS Sql. Now we are moving to oracle,Oracle DBA says he will not create a separate schema for each SOR. He is asking to keep all SOR's under one schema. Could someone suggest best practice?
and Chris said...
I'm not sure what SOR means - so it's hard to say what the best approach would be here without more context.
Assuming each SOR has its own distinct set of tables and there's little/no common data/functionality between SORs, creating a separate schema for each has a couple of advantages:
- You have finer-grained control over privileges. This makes it easier to follow the principle of least privilege (only give accounts the access they require), which improves security
- It's easier to split SORs into separate databases in the future should you want to
But really this comes down to what a SOR is.
If you've got the data in different schemas in SQL Server, keeping this approach is likely to make the migration easier.
Is this answer out of date? If it is, please let us know via a Comment