ORACLE :Are grants removed when an object is dropped?

Question

Hi,

I currently have 2 schemas, A and B.

B has a table, and A executes selects inserts and updates on it.

In our sql scripts, we have granted permissions to A so it can complete its tasks.

grant select on B.thetable to A
etc,etc

Now, table 'thetable' is dropped and another table is renamed to B at least once a day.

rename someothertable to thetable

After doing this, we get an error when A executes a select on B.thetable.

ORA-00942: table or view does not exist

Is it possible that after executing the drop + rename operations, grants are lost as well?

Do we have to assign permissions once again ?

update

someothertable has no grants.

update2

The daily process that inserts data into 'thetable' executes a commit every N insertions, so were not able to execute any rollback. That's why we use 2 tables.

Thanks in advance

Answer 1

No : "Oracle Database automatically transfers integrity constraints, indexes, and grants on the old object to the new object." http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9019.htm#SQLRF01608

You must have another problem

Answer 2

Yes, once you drop the table, the grant is also dropped.

You could try to create a VIEW selecting from thetable and granting SELECT on that.

Your strategy of dropping a table regularly does not sound quite right to me though. Why do you have to do this?

EDIT

There are better ways than dropping the table every day.

1. Add another column to thetable that states if the row is valid.

2. Put an index on that column (or extend your existing index that you use to select from that table).

3. Add another condition to your queries to only consider "valid" rows or create a view to handle that.

4. When importing data, set the new rows to "new". Once the import is done, you can delete all "valid" rows and set the "new" rows to "valid" in a single transaction.

If the import fails, you can just rollback your transaction.

Answer 3

Perhaps the process that renames the table should also execute a procedure that does your grants for you? You could even get fancy and query the dictionary for existing grants and apply those to the renamed table.