I have a data warehouse that consists of a specific schema for reporting. Anyone who is granted access to it will be given privileges to objects in that schema. However, whenever I drop objects, those privileges are lost.
What are the best solutions for such issue to retain existing privileges?
Dropping an object when we have granted access on it to other schemas is extremely problematic. No matter how small the window between dropping the table and re-granting privileges there is a period of time in which other users will have queries fail or dependent objects invalidated. The only way to avoid that happening is to take the database offline. Which might inconvenience considerably more people, so we probably don't want to do that.
(It's also a problem even when our schema is the only schema using the dropped table, but usually we have slightly more control over that.)
What are the best solutions for such issue to retain existing privileges?
The best solution is to not drop the objects. Continually dropping and re-creating objects, especially tables, is bad practice. There is no need to do this in a live environment. The issue this practice thinks it's addressing can be better solved with either the correct command, TRUNCATE, or the appropriate data structure, GLOBAL TEMPORARY TABLE.
However, it appears you find yourself on a pigheaded project which insists on the rightness of this bad practice. So you have to drop and recreate the tables, and then you need to re-grant privileges to the users of those tables**(*)**. This ought to be quite simple: the script to create table - which you should be referencing for this exercise - should also contain the statements to grant necessary privileges.
Of course this presumes that you project is following good practice regarding source control of DDL scripts. Under the circumstances my hopes are not high.
Of course there are cases when dropping and re-creating a table is a sound solution. For instance, when staging a large amount of data preparatory to a partition exchange operation, drop-and-recreate may be more efficient than truncate-and-insert. But such use cases are niche ones, and not normally associated with the granting of privileges to other schemas.
(*) The need to do this is one reason why dropping the tables is bad practice.