Grants Now Included in SQL Developer SQL Pages

thatjeffsmith SQL Developer 8 Comments

Tell Others About This Story:

Quickly copying objects to a new environment is easy in SQL Developer. You simply go to the object in the tree and access the SQL page.

Get the DDL to re-create your objects on the SQL page

However a few of our users were complaining that we weren’t including the object privileges that had been GRANTed in the SQL script. So after re-creating the object in another system, they would have to look at the Grants page and manually recreate those scripts.

WHO can do WHAT to my beer, <cough>, table?

So in version 3.2, you can now include the GRANT statements in your object scripts.

Voila!

You now have the code necessary to recreate your object privileges

But Wait, I Still Don’t See My Grants!

There’s no trick here. But, you must first tell SQL Developer what you want included in your scripts. Whenever SQL Developer generates a script for an object, it consults the preferences.

Ensure that ‘Grants’ is checked to generate your privilege statements

Another very small change that I believe will vastly improve the end user’s experience – and save you a lot of time!

Tell Others About This Story:

Comments 8

  1. Hello Jeff, thanks for the trick, it’s very helpful.

    I’m looking the way to obtain the grant of privileges but separated by commas.

    Actually i get this:
    GRANT UPDATE ON “BDI_GIS_OWNER”.”TABLE1″ TO “BDI_GIS_APLICACION”;
    GRANT SELECT ON “BDI_GIS_OWNER”.”TABLE1″ TO “BDI_GIS_APLICACION”;
    GRANT INSERT ON “BDI_GIS_OWNER”.”TABLE1″ TO “BDI_GIS_APLICACION”;
    GRANT DELETE ON “BDI_GIS_OWNER”.”TABLE1″ TO “BDI_GIS_APLICACION”;

    and I want get this:
    GRANT UPDATE,SELECT,INSERT,DELETE ON “BDI_GIS_OWNER”.”TABLE1″ TO “BDI_GIS_APLICACION”;

    It’s posible?

    Thanks

    1. thatjeffsmith Post
      Author
      1. Thanks for your quick response.

        If you don’t mind, could you guide me to start writing the code?

        I’m a little lost.

    1. thatjeffsmith Post
      Author
  2. Hi Jeff,
    Thanks for these updates. It is very helpful.

    However with Grants , there seems to be an issue where in if I do not login as the owner of the table, the Grant scripts are not generated. Usually one would expect some one like a DBA user to generate the SQL DDL for any Schemas

    Cheers

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *