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

What are you doing that couldn't be handled by ALTER TABLE statements?

The next best option might be to create a view that references the table that occaisionally disappears - the view won't disappear, you'd just get an error if the table doesn't exist in such a situation. IE:

CREATE VIEW table_vw AS
  SELECT t.* 
    FROM DISAPPEARING_TABLE t

Using * notation would also mean you don't have to continually update the view to expose columns in the table.

Answer 2

You could grant select any table, insert any table, etc to schema_one, but that seems like overkill and won't reflect what you do in production (hopefully). Why can't you issue the grant at the same time as you create the table, while logged in as schema_two? I've always done that in the creation scripts, and only ever had to use an admin account to grant third-party or system privs. I suspect I'm missing something...

Answer 3

You could have a DDL trigger or a batch job that runs every few minutes that grants privileges automatically. But that is a bit of a security hole and won't represent production.

Answer 4

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.

Answer 5

I suggest that you might give the account which you use to create the tables the ability to run the grants as well.

Share and enjoy.