Options
identical grant is not recognized as identical
peterzhao
Posts: 2
I compare base db with schema {css_plt_admin, css_plt} and target db with schema {foitest06_admin, foitest06}. Both db have the same synonym: ac_asmt.
the synonym on base db side :
CREATE OR REPLACE SYNONYM "CSS_PLT"."STATION_AMADEUS" FOR "CSS_PLT_ADMIN"."STATION_AMADEUS";
the synonym on target db side:
CREATE OR REPLACE SYNONYM "FOITEST06"."STATION_AMADEUS" FOR "FOITEST06_ADMIN"."STATION_AMADEUS";
Since the schema mapping is like this:
css_plt_admin <==> foitest06_admin
css_plt <-==> foitest06
the synonym should be regarded as identical.
However, they are not regarded as identical. The script generated by the Schema Compare will grant permission to the synonym and then revoke it:
GRANT SELECT ON foitest06_admin.station_amadeus TO foitest06;
GRANT INSERT ON foitest06_admin.station_amadeus TO foitest06;
GRANT UPDATE ON foitest06_admin.station_amadeus TO foitest06;
GRANT DELETE ON foitest06_admin.station_amadeus TO foitest06;
REVOKE SELECT ON foitest06_admin.station_amadeus FROM foitest06;
REVOKE INSERT ON foitest06_admin.station_amadeus FROM foitest06;
REVOKE UPDATE ON foitest06_admin.station_amadeus FROM foitest06;
REVOKE DELETE ON foitest06_admin.station_amadeus FROM foitest06;
After the script is deployed, we lost the permission of the synonym on target db.
The grant script is generated because the tool thinks the grant only exists on base db and the revoke script is generated because the tool thinks the grant only exists on target db. They should be regarded as identical to avoid this issue. I think this is a bug.
the synonym on base db side :
CREATE OR REPLACE SYNONYM "CSS_PLT"."STATION_AMADEUS" FOR "CSS_PLT_ADMIN"."STATION_AMADEUS";
the synonym on target db side:
CREATE OR REPLACE SYNONYM "FOITEST06"."STATION_AMADEUS" FOR "FOITEST06_ADMIN"."STATION_AMADEUS";
Since the schema mapping is like this:
css_plt_admin <==> foitest06_admin
css_plt <-==> foitest06
the synonym should be regarded as identical.
However, they are not regarded as identical. The script generated by the Schema Compare will grant permission to the synonym and then revoke it:
GRANT SELECT ON foitest06_admin.station_amadeus TO foitest06;
GRANT INSERT ON foitest06_admin.station_amadeus TO foitest06;
GRANT UPDATE ON foitest06_admin.station_amadeus TO foitest06;
GRANT DELETE ON foitest06_admin.station_amadeus TO foitest06;
REVOKE SELECT ON foitest06_admin.station_amadeus FROM foitest06;
REVOKE INSERT ON foitest06_admin.station_amadeus FROM foitest06;
REVOKE UPDATE ON foitest06_admin.station_amadeus FROM foitest06;
REVOKE DELETE ON foitest06_admin.station_amadeus FROM foitest06;
After the script is deployed, we lost the permission of the synonym on target db.
The grant script is generated because the tool thinks the grant only exists on base db and the revoke script is generated because the tool thinks the grant only exists on target db. They should be regarded as identical to avoid this issue. I think this is a bug.
Comments
Yes this is a bug. I'll add it to our tracking system. In the meantime you can actually work around it by selecting the option "ignore index names" in the edit project dialog.
This option was only supposed to allow you to ignore the names of indexes when comparing but it actually has a side effect where it will ignore the names of any objects and just search for a matching object.
Thanks,
Neil