Wie behebe ich den Fehler „user cannot be dropped“ in Amazon Redshift?

Kurzbeschreibung

Wenn Sie versuchen, einen Benutzer in Amazon Redshift zu droppen, wird möglicherweise eine der folgenden Fehlermeldungen angezeigt:

• ERROR: user "username" cannot be dropped because some objects depend on it
• ERROR: user "username" cannot be dropped because the user has a privilege on some object
• ERROR: user "username" cannot be dropped because the user owns some object

Diese Fehler können auftreten, wenn Sie versuchen, diese Benutzertypen zu droppen:

• Einen Besitzer oder Zielbenutzer mit Standardberechtigungen.
• Einen Besitzer eines beliebigen Objekts (wie Datenbanken, Schemas, Tabellen, Ansichten, Prozeduren und Bibliotheken).
• Einen Benutzer, der Berechtigungen für Objekte hat.

Um diese Fehlermeldungen zu beheben, müssen Sie zunächst alle Benutzerberechtigungen entfernen. Übertragen Sie dann den Objektbesitz oder entfernen Sie die Gruppeneigenschaft der Objekte.

Wichtig: Sie müssen Benutzer- und Gruppenberechtigungen für alle Datenbanken im Amazon Redshift-Cluster entziehen.

Lösung

Installieren von Skripten zum Erstellen von Ansichten

Laden Sie die Skripte v_generate_user_grant_revoke_ddl.sql und v_find_dropuser_objs.sql aus dem GitHub-Repository von AWS-Übungen herunter und installieren Sie sie. Die Ansichten für die Skripte v_generate_user_grant_revoke_ddl.sql und v_find_dropuser_objs.sql verwenden das Admin-Schema in ihrer Definition. Wenn kein Admin-Schema auf dem Amazon-Redshift-Cluster vorliegt, ändern Sie die Definition in einem vorhandenen Schema. Oder erstellen Sie ein Admin-Schema.

Wenn in Ihrer Ansichtsdefinition Spaltenänderungen auftreten, droppen Sie Ihre Ansicht, bevor Sie eine neue Ansicht und Definition erstellen. Wenn Sie versuchen, eine neue Ansicht zu erstellen, bevor Sie die alte Ansicht gedroppt haben, erhalten Sie einen Fehler not valid table.

Droppen eines Benutzers

Führen Sie die folgenden Schritte aus:

1. Suchen Sie nach Benutzern, um alle erteilten Benutzerberechtigungen zu finden, die Sie droppen möchten. Erteilen Sie diese Berechtigungen dann erneut als ein anderer Benutzer. Es hat sich bewährt, dass dieser Benutzer ein Superuser ist.

   select regexp_replace(ddl,grantor,'<superuser>') from v_generate_user_grant_revoke_ddl where grantor='<username>' and ddltype='grant' and objtype <>'default acl' order by objseq,grantseq;

   Hinweis: Um die Berechtigungen erneut zu gewähren, müssen Sie ein Benutzer mit Berechtigungen für das Objekt sein. Sie müssen außerdem über die Berechtigung verfügen, einem anderen Benutzer Berechtigungen zu erteilen. Wenn Sie kein Recht zur Gewährung von Berechtigungen haben, können Sie die Berechtigungen als Superuser erneut gewähren.

2. Suchen Sie nach allen Berechtigungen, die dem Benutzer gewährt wurden, und widerrufen Sie sie:

   select ddl from v_generate_user_grant_revoke_ddl where ddltype='revoke' and (grantee='<username>' or grantor='<username>') order by objseq, grantseq desc;

   Hinweis: Ersetzen Sie Grantor durch den Namen des Benutzers, der die Berechtigungen gewährt. Ersetzen Sie Empfänger durch den Namen des Benutzers, der die Berechtigungen erhält.
   Wenn Ihre Abfrage keine Datensätze zurückgibt oder der Befehl DROP USER fehlschlägt, führen Sie diese Abfrage aus:

   select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and ddl ilike '%<user-to-be-dropped>%' order by objseq, grantseq desc;

   Die obige Abfrage listet die Berechtigungen auf, die Sie dem Benutzer entziehen müssen, bevor Sie ihn droppen können. Widerrufen Sie diese Berechtigungen, bevor Sie mit dem nächsten Schritt fortfahren.

3. Führen Sie die folgenden Abfragen aus, um nach leeren Zugriffssteuerungslisten (ACLs) zu suchen:

   select * from pg_user where usename = '<username-to-be-dropped>';
   select * from pg_default_acl where defacluser= <user-id>;
   select pg_get_iam_role_by_user('<user-name>');

   Um den Benutzernamen und die Benutzer-ID abzurufen, suchen Sie die Spalteneinträge usename und usesysid in der Tabelle PG_USER_INFO.
   Hinweis: Wenn die Tabelle PG_DEFAULT_ACL Benutzereinträge enthält, können Sie den Benutzer nicht droppen.

4. Wenn der Benutzer immer noch über Berechtigungen für einige Objekte verfügt, überprüfen Sie, ob dem Benutzer assumerole-Berechtigungen erteilt wurden:

   select pg_get_iam_role_by_user('<user-name>');

   Wenn der Benutzer assumerole-Berechtigungen hat, entziehen Sie zunächst der Öffentlichkeit assumerole. Führen Sie dann den folgenden Befehl aus, um den username zu widerrufen:

   revoke assumerole on all from public for all;
   revoke assumerole on all from <user-name> for all;

5. (Optional) Wenn der Benutzer noch über Berechtigungen für einige Objekte verfügt, überprüfen Sie, ob der Benutzer Teil einer anderen Gruppe ist. Der Benutzer verfügt möglicherweise über Berechtigungen, die ihm von dieser Gruppe erteilt wurden. Oder der Benutzer verfügt möglicherweise über Berechtigungen, die der Gruppe ÖFFENTLICH erteilt wurden.
   Führen Sie die folgenden Abfragen aus, um zu überprüfen, welche Berechtigungen dem Benutzer noch gewährt werden:

   select * from pg_user where usename = '<username-to-be-dropped>';
   select * from pg_group;

   Prüfen Sie in der Ausgabe der pg_group-Abfrage, ob Einträge vorhanden sind, die den Benutzer in der Spalte grolist auflisten. Wenn der Benutzer einer anderen Gruppe angehört, wird seine Benutzer-ID aufgeführt. Überprüfen Sie die Berechtigungen, die dieser Gruppe gewährt wurden:

   select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and grantee= 'group <group-name>' ;
   select * from admin.v_generate_user_grant_revoke_ddl where objname='timecards' and schemaname='postgres' and grantee='PUBLIC' and ddltype='revoke';

   Hinweis: Ersetzen Sie objname und schemaname durch Ihre Tabelle und Ihr Schema. Standardmäßig werden der Gruppe ÖFFENTLICH erteilte Berechtigungen allen Benutzern gewährt.

6. Finden Sie alle Objekte, die der Benutzer besitzt, und übertragen Sie den Besitz dann auf einen anderen Benutzer oder Administrator:

   select ddl||'<newuser>;' as ddl from admin.v_find_dropuser_objs where objowner = '<username-to-be-dropped>';

   In der Ausgabe des obigen Befehls sind die Befehle aufgeführt, die Sie verwenden müssen, um den Besitz an einen neuen Benutzer zu übertragen.

7. Wiederholen Sie die Schritte 2–7 in jeder Datenbank im Amazon-Redshift-Cluster.

8. Führen Sie den folgenden Befehl aus, um den Benutzer aus der Datenbank zu entfernen:

   drop user <username-to-be-dropped>;

Droppen einer Gruppe

Hinweis: Bevor Sie eine Gruppe droppen, müssen Sie alle Berechtigungen widerrufen, die die Gruppe für Objekte besitzt.

Führen Sie die folgenden Schritte aus:

1. Suchen Sie nach allen Berechtigungen, die der Gruppe gewährt wurden, und widerrufen Sie sie:

   select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and grantee= 'group <group-name>';

2. Wiederholen Sie Schritt 2 in jeder Datenbank im Amazon-Redshift-Cluster. Vergewissern Sie sich, dass die Berechtigungen der Gruppe in allen Datenbanken aufgehoben wurden.

3. Verwenden Sie den Befehl DROP GROUP, um die Benutzergruppe zu entfernen.