search

darold / ora2pg

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.
http://www.ora2pg.com/
GNU General Public License v3.0
1.01k stars 342 forks source link

Incorrect nested schema/package for grants on packaged procs #1591

Closed smileatom closed 1 year ago

smileatom commented 1 year ago

The GRANT generator is locating procedures in nested schemas as if the code was still in oracle for package body procedures. The new schemas that ora2pg generates to simulate package . dot syntax is being located below the primary schema.

GRANT ALL ON (user schema).(generated pkg schema).proc_name is illegal because it uses nested schema on PG.

The user_schema prefix (from oracle user) is incorrect and must be removed. Only the generated pkg body -> schema.procedure name should be generated.

darold commented 1 year ago

Commit 4ea3e4b fixes this issue. Now privileges export for packages will look as follow:

-- Set priviledge on PACKAGE BODY PCK1
ALTER SCHEMA pck1 OWNER TO hr;
GRANT EXECUTE ON ALL ROUTINES IN SCHEMA pck1 TO hr;
REVOKE ALL ON SCHEMA pck1 FROM PUBLIC;
GRANT USAGE ON SCHEMA pck1 TO scott;
GRANT EXECUTE ON ALL ROUTINES IN SCHEMA pck1 TO scott;
smileatom commented 1 year ago

Thumbs Up!