Toad World® Forums

how to suppress grants when generating change script?

Getting started with Toad Data Modeler. I did not see this question previously in the forum.

I would like to have no grant statements when “generating change script.” When I choose “Generate DDL Script” I have an option to “Edit Default Settings” and I can unselect “Permissions to Objects” this works great.

When I choose “Generate Change Script” I do not have a “Edit Default Settings” and I always get grant statements. I do not want any grant statements.

how to suppress grants when generating change script?

Toad Data Modeler 5.4

postgresql 9.4

Hello Mike,

You should be able to do the same thing for Change Script generation. In Select Object Types form of the Generate Change Script wizard, click the Detailed Settings button and then uncheck Permissions to Objects.

Regards,

Lukas

Unfortunately, I am still getting grant statements in the generated sql.

I hadn’t noticed the “detailed settings” Lots of good stuff in there. Thanks for that tip.

Anything else I can do to debug this issue? I looked through all the detail settings but didn’t see anything else that looked like it would apply to this.

I just did more testing with this and found that when I deselect “permissions to object” I don’t get grants unless I change the primary key. I still would like to not have grants, but changing the primary key is rare so not a big problem.

Hello again,

Could you possibly send us an example of situation when grants are generated with the option disabled? For example, by generating a change script for 2 models with one entity each?

Regards,

Lukas

Here is a simple example. Please let me know if you need more information.

CREATE TABLE finance.grant_test
(
id integer NOT NULL,
description text, – description of id
CONSTRAINT grant_test_pk PRIMARY KEY (id)
);

Add a column, this works fine

ALTER TABLE “finance”.“grant_test”
ADD COLUMN “short_nm” Text
;

Now change the name of the primary key column from id to grant_test_id

ALTER TABLE grant_test DROP CONSTRAINT grant_test_pk
;

DROP TABLE grant_test
;

CREATE TABLE grant_test(
grant_test_id Integer NOT NULL,
description Text,
short_nm Text
)
;
COMMENT ON COLUMN grant_test.description IS ‘description of id’
;

ALTER TABLE grant_test ADD CONSTRAINT grant_test_pk PRIMARY KEY (grant_test_id)
;

GRANT SELECT ON grant_test TO db
;
GRANT INSERT ON grant_test TO db
;
GRANT UPDATE ON grant_test TO db
;
GRANT DELETE ON grant_test TO db
;
GRANT REFERENCES ON grant_test TO db
;
GRANT TRIGGER ON grant_test TO db
;
GRANT TRUNCATE ON grant_test TO db
;
GRANT SELECT ON grant_test TO fin_mod
;
GRANT INSERT ON grant_test TO fin_mod
;
GRANT UPDATE ON grant_test TO fin_mod
;
GRANT DELETE ON grant_test TO fin_mod
;
GRANT REFERENCES ON grant_test TO fin_mod
;
GRANT TRIGGER ON grant_test TO fin_mod
;
GRANT TRUNCATE ON grant_test TO fin_mod
;
GRANT SELECT ON grant_test TO fin_read
;

Hello again Mike,

Thank you for the additional information, we have managed to reproduce the behavior. A CR TDM-1468 was created, we will analyze the behavior and see what can we do about it.

Regards,

Lukas