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.

12 Comments

  1. Avatar

    Is it possible to generate this grants(GRANT SELECT, INSERT, DELETE, UPDATE ON tableA TO roleA;) in data modeler?
    My tableA in the database has the grants and I tried to read this by sync the model with data dictorinary but it dont generate me this in the ddl.

    • thatjeffsmith

      Yes, just make sure your physical model is OPEN, and that you have those grants defined in the PERMISSIONS dialog for your tables, views, etc.

    • Avatar

      Thank you for the answer. It works but I recognized that the rights to sequnce are not generated.
      If I click an table in my model and click on DDL preview. I get this cool code :).

      CREATE SEQUENCE schemaA.seq_A START WITH 1 NOCACHE ORDER;
      CREATE TABLE schemaA.tableA (
      column_id NUMBER(22) DEFAULT schemaA.seq_A.nextval NOT NULL,
      columnA NUMBER(22) NOT NULL
      )
      NO INMEMORY;
      GRANT INSERT, SELECT, UPDATE, DELETE ON schemaA.tableA TO roleA;
      GRANT SELECT ON schemaA.tableA TO roleB;

      But how I can generate SELECT grant to the sequence schemaA.seq_A. Is this possible

  2. Avatar

    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

    • Avatar
      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.

  3. Avatar

    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.

    • Avatar
      P Shetty

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

Write A Comment

RSS
Follow by Email
LinkedIn
Share