Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
While migrating privileges from Oracle to PosgreSQL loses 1 needed component in inheritance rights tree:
In Oracle there is no concept of "GRANT USAGE ON SCHEMA", but PostrgeSQL has this concept.
I think, that once Ora2Pg grants any privileges on tables in any schema, it should also grant usage of that schema.
This functionality works with granting privileges on packages (in postgre they become schemas), but doesn't work with tables.
darold
commented
1 year ago
While migrating privileges from Oracle to PosgreSQL loses 1 needed component in inheritance rights tree: In Oracle there is no concept of "GRANT USAGE ON SCHEMA", but PostrgeSQL has this concept. I think, that once Ora2Pg grants any privileges on tables in any schema, it should also grant usage of that schema. This functionality works with granting privileges on packages (in postgre they become schemas), but doesn't work with tables.
Oracle code:
PosgreSQL code:
PosgreSQL code should be like: