Preserving Objects Owned by a Dropped User
Jim_Knicely
- Select Field - Administrator
When you drop a user with the CASCADE option, all objects owned by that user a lost forever! If you’d prefer to save those objects, you first set the GlobalHeirUserName security parameter to a user who will inherit objects after their owners are dropped. This setting ensures preservation of data otherwise lost.
Example:
dbadmin=> CREATE USER previous; CREATE USER dbadmin=> CREATE USER current; CREATE USER dbadmin=> GRANT CREATE ON SCHEMA public TO previous, current; GRANT PRIVILEGE dbadmin=> \c - previous You are now connected as user "previous". dbadmin=> CREATE TABLE public.a_table (c INT); CREATE TABLE dbadmin=> \c - dbadmin You are now connected as user "dbadmin". dbadmin=> SELECT table_schema, owner_name dbadmin-> FROM tables dbadmin-> WHERE table_name = 'a_table'; table_schema | owner_name --------------+------------ public | previous (1 row) dbadmin=> ALTER DATABASE test_db SET GlobalHeirUserName = 'current'; ALTER DATABASE dbadmin=> DROP USER previous CASCADE; DROP USER dbadmin=> SELECT table_schema, owner_name dbadmin-> FROM tables dbadmin-> WHERE table_name = 'a_table'; table_schema | owner_name --------------+------------ public | current (1 row)
Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/ConfiguringTheDB/SecurityParameters.htm
Have fun!
0