How to make GRANT persistence for a table that is deleted and recreated?

Question

I am in a fairly new project where we are still modifying the design of the Oracle 11g database tables. This way, we pretty often drop and re-create our tables to make sure the table creation scripts work as expected when we make the changes.

Our database consists of 2 schemes. One schema has several tables with triggers INSERTthat force data to sometimes be copied to tables in our second schema. This requires us to enter the database administrator account, such as sysdba, and GRANTaccess to the first scheme, the necessary tables in the second scheme, for example.

GRANT ALL ON schema_two.SomeTable TO schema_one;

Our problem is that every time we make changes to our database design and want to drop and recreate our database tables, we GRANT-ed access schema_onedisappeared when the table was deleted. Thus, this creates another annoying step in which we must log in with an administrator account to return GRANTaccess every time one of these tables is deleted and recreated.

This is not a huge deal, but I would like to eliminate as many steps as possible from our development and testing procedures. Is there a way to GRANTaccess the table so that GRANT-ed permissions save the table that was dropped and then recreated? And if this is not possible, is there a better way to do this?

Answer 1

select any table, insert any table .. schema_one, , (). , schema_two? , - . , - ...

Answer 2

, , , , - .

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>

, .

, , : , schema_two. schema_one? ? , schema_two DROP CREATE. GRANT ?

, . , .

Answer 3

, ALTER TABLE?

, , - , ​​ , . IE:

CREATE VIEW table_vw AS
  SELECT t.* 
    FROM DISAPPEARING_TABLE t

* , , .

Answer 4

DDL , , . .

Answer 5

, , .

.