How to make a GRANT persist for a table that's being dropped and re-created?

Question

I'm on a fairly new project where we're still modifying the design of our Oracle 11g database tables. As such, we drop and re-create our tables fairly often to make sure that our table creation scripts work as expected whenever we make a change.

Our database consists of 2 schemas. One schema has some tables with INSERT triggers which cause the data to sometimes be copied into tables in our second schema. This requires us to log into the database with an admin account such as sysdba and GRANT access to the first schema to the necessary tables on the second schema, e.g.

GRANT ALL ON schema_two.SomeTable TO schema_one;

Our problem is that every time we make a change to our database design and want to drop and re-create our database tables, the access we GRANT-ed to schema_one went away when the table was dropped. Thus, this creates another annoying step wherein we must log in with an admin account to re-GRANT the access every time one of these tables is dropped and re-created.

This isn't a huge deal, but I'd love to eliminate as many steps as possible from our development and testing procedures. Is there any way to GRANT access to a table in such a way that the GRANT-ed permissions survive a table being dropped and then re-created? And if this isn't possible, then is there a better way to go about this?

Answer 1

So the reason why the grants get revoked is that the new table is a different object.

SQL> select object_id from user_objects
  2  where object_name = 'T72'
  3  /

 OBJECT_ID
----------
    659195

SQL> drop table t72
  2  /

Table dropped.

SQL> create table t72 (id number)
  2  /

Table created.

SQL> select object_id from user_objects
  2  where object_name = 'T72'
  3  /

 OBJECT_ID
----------
    659212

SQL>

The grants are on the object, not the object's name.

What I don't understand about your problem is this: you have a process which drops and re-creates the tables in schema_two. Why doesn't that process also grant grant privileges on those tables to schema_one? Why do you have an admin account do it instead? I presume you are connecting as schema_two to run the DROP and CREATE statements. Why not just add the GRANT statements to that step?

Because granting privileges on objects is as much a part of the installation as creating the tables. So you ought to have a process which does everything.