Updated on 2026-11-12 GMT+08:00

DROP OWNED

Function

During routine database O&M, administrators often need to clean up or migrate roles. For example, when an application goes offline, an employee leaves the company, or the system is migrated, you need to delete all database objects (such as tables, views, and sequences) owned by a specific role and revoke its permissions on shared objects. In this case, you can use the DROP OWNED statement to perform these operations at a time, avoiding the tedious process of manually deleting objects one by one.

The DROP OWNED statement is used to delete database objects owned by a database role and revoke all permissions of the role on the current database and shared objects (databases and tablespaces).

DROP OWNED and DROP ROLE are closely related commands in role management. The following table compares their application scenarios.

Command	Function	Object Processing Method	Role Itself	Typical Scenario
DROP OWNED	Delete all objects owned by a role and revoke its permissions.	Delete objects.	Still exists.	Role cleanup: Objects need to be deleted and are no longer required.
DROP ROLE	Delete the role directly.	If the role still has objects, an error is reported.	Delete the role.	Role deletion: Objects must be cleared using DROP OWNED before the role can be deleted.

Precautions

This interface will revoke the role's permissions on all objects in the current database and shared objects (databases and tablespaces).
DROP OWNED deletes only the objects and permissions owned by the role. The role itself is not deleted and is still retained in the system. To delete the role, you need to run the DROP ROLE command after running the DROP OWNED command.
If the CASCADE option is used, all dependent objects are deleted accordingly, which may affect the objects of other roles. Therefore, exercise caution when using this option.

Syntax

     DROP OWNED BY name [, ...] [ CASCADE | RESTRICT ];

Parameter Description

**Table 1** DROP OWNED parameters
Parameter	Description	Value Range
name	Name of the role whose objects are to be deleted and whose permissions are to be revoked.	An existing role name.
CASCADE \| RESTRICT	Specifies how to process related data in the dependent object when a delete operation is performed.	CASCADE: automatically deletes objects that depend on the affected objects. RESTRICT (default): refuses to delete objects with dependent objects.

Example

Create role u1 and a table owned by u1.

     CREATE ROLE u1 IDENTIFIED BY '{password}';
CREATE TABLE u1_table (id int);
ALTER TABLE u1_table OWNER TO u1;

Remove all database objects owned by role u1.

     DROP OWNED BY u1;

Documentation

DROP ROLE

FAQ

Error Message "role "xx" cannot be dropped because some objects depend on it" Is Displayed When DROP ROLE Is Executed

Cause: The role still owns database objects. Therefore, the DROP ROLE statement will be rejected.

Solution: Run the DROP OWNED BY role_name CASCADE statement to clear all objects and permissions, and then run the DROP ROLE statement.

After DROP OWNED Is Executed, Objects in Other Databases Are Not Deleted

Cause: DROP OWNED applies only to the database connected to the current session and does not delete objects across databases.

Solution: Switch to other databases and run the DROP OWNED BY name statement in each database.

Parent Topic: Users and Roles