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!

thatjeffsmith
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

8 Comments

  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

    • Raimundo

      Thanks for your quick response.

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

      I’m a little lost.

  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

    • thatjeffsmith

      This is a known bug and it’s already been fixed for our next release. You’re absolutely right in your expectation and we’ll get this taken care for you ASAP.

    • P Shetty

      Thank you for the update. looking forward to the next release.
      Cheers

Write A Comment