2

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?

samg
  • 311
  • 1
  • 8
  • 21
  • 1
    Best solution is - not to drop the objects. Why you need to drop the objects? – Ychdziu Aug 20 '18 at 14:10
  • @Ychdziu it is a part of a procedure for objects to be dropped and re-created. – samg Aug 20 '18 at 14:14
  • 3
    Still, why you would need to re-create them? Whats the purpose? Why "truncate" isn't enough, if you need to purge the data? Cause the only purpose i see from droping and re-creating them is to "clean up forcefully" data. DB objects shouldn't be droped/re-created on the fly, whenever you want - its a bad practice. – Ychdziu Aug 20 '18 at 14:22
  • @Ychdziu that's exactly my initial thought but as a part of a project requirement I have to drop and re-create and so on... That's when I started to think about privileges? – samg Aug 20 '18 at 14:24
  • Shady project, if you ask me. Don't get sucked in to deep - its hard to drop "bad habits" later on. Still, if there is no other way - before droping the objects, save the privileges in some collection/temp table and re-grant them when you re-create the objects. – Ychdziu Aug 20 '18 at 14:28
  • 1
    For VIEW and some other objects you can use `CREATE OR REPLACE ...` syntax. This keeps the privileges. – Wernfried Domscheit Aug 20 '18 at 14:29
  • 2
    What kind of objects are you talking about? For TABLES or SNAPSHOTS you should use `TRUNCATE`, for VIEWS you can use `CREATE OR REPLACE ...`. – Wernfried Domscheit Aug 20 '18 at 14:31
  • Thanks @Ychdziu, that might work for me. – samg Aug 20 '18 at 14:34
  • @WernfriedDomscheit mainly tables. And the discussion in regards to the `TRUNCATE` process is in the comments above. I have to drop and re-create these tables. – samg Aug 20 '18 at 14:35
  • Why do you need (or like) to drop the table? Do you get other columns? This would be even worse design. – Wernfried Domscheit Aug 20 '18 at 14:52
  • It is a project requirement. Although it may be bad design but I have to follow it regardless and that is when I ran into the privileges issue. – samg Aug 20 '18 at 15:14
  • 2
    Then you should convince the project leader to change such stupid requirements. What would be the purpose of it? – Wernfried Domscheit Aug 20 '18 at 15:42
  • @samg As a best practice, on production databases, you should execute scripts to create database objects which comprise of all the associated attributes to the object(DML, DDL, Grants etc.). These scripts can be re-used if needed. DBA's would always insist to have such scripts as developers perhaps will not have access to the production environment. APC has mentioned this in the answer. – Jacob Aug 26 '18 at 11:55

1 Answers1

3

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.

APC
  • 144,005
  • 19
  • 170
  • 281