Purpose
This statement is used to drop a row-level security (RLS) policy on a specified object.
Privilege requirements
To execute the DROP POLICY statement, the current user must have the object-level ALTER privilege on the target table. Having the database-level or global ALTER privilege that covers the table also satisfies the requirement. For more information, see Permission classification.
Limitations and considerations
- The deletion policy takes effect immediately.
- A drop policy permanently removes the configuration. If you only need to temporarily disable table-level RLS, execute
ALTER TABLE ... DISABLE ROW LEVEL SECURITYinstead ofDROP POLICY. - When a table, normal view, or materialized view is dropped, its associated strategies are cascaded and dropped as well.
Syntax
DROP POLICY [IF EXISTS] policy_name ON [schema_name.]table_name;
Parameters
Parameters |
Note |
|---|---|
| IF EXISTS | No error is returned when the policy does not exist. |
| policy_name | The name of the policy to be deleted. |
| table_name | The name of the object to which the policy belongs. You can use database_name.table_name Specify the database explicitly. |
The instructions and limitations are as follows:
- The matching rules for policy names and target tables are the same as those in CREATE POLICY. (Within the same table, policy names must be unique. You can use
database_name.table_nameto specify the object.)
Examples
The following examples are for reference only. Adjust them according to your actual situation when using them:
-- Deletion Policy (No error reported if not present)
DROP POLICY IF EXISTS emp_dept_policy ON hr.employees;
-- Delete Manager-related Write Strategy
DROP POLICY IF EXISTS mgr_write_policy ON hr.employees;