ROW level privacy or row level security how to implement in starrocks.
I tried but i getting error
Code is
# --- SQLAlchemy Engine ---
engine = create_engine(
f"mysql+pymysql://{STARROCKS_ADMIN_USER}:{STARROCKS_ADMIN_PASS}@{STARROCKS_HOST}:{STARROCKS_PORT}/",
echo=False
)
log.info(f"SQLAlchemy engine created for: mysql+pymysql://{STARROCKS_ADMIN_USER}:***@{STARROCKS_HOST}:{STARROCKS_PORT}/")
# --- Helper Function to Execute SQL ---
def execute_sql(sql_command, params=None, db_context=None, fetch_results=False):
"""Transaction-safe SQL execution with null handling, using engine.begin()"""
with engine.begin() as conn:
try:
if db_context:
conn.execute(text(f"USE {db_context};"))
log.debug(f"Switched to database context: {db_context}")
log.info(f"Executing SQL:\n{str(sql_command).strip()}")
result = conn.execute(sql_command, params)
if fetch_results:
return result.fetchall()
return result
except SQLAlchemyError as e:
log.error(f"StarRocks SQL execution failed: {e}")
raise
except Exception as e:
log.error(f"An unexpected error occurred: {e}")
raise
# --- Simple RLP Test Logic ---
if __name__ == "__main__":
test_db_name = "rlp_test_db_confirm"
test_table_name = "rlp_test_table_confirm"
test_policy_name = "my_rlp_policy_confirm"
log.info("Starting simple test to confirm CREATE ROW POLICY support.")
try:
# Create test database
log.info(f"Creating test database: {test_db_name}")
execute_sql(text(f"CREATE DATABASE IF NOT EXISTS {test_db_name};"))
# Use test database
execute_sql(text(f"USE {test_db_name};"))
# Create test table
log.info(f"Creating test table: {test_table_name}")
create_table_sql = f"""
CREATE TABLE IF NOT EXISTS {test_table_name} (
id INT,
customer_id VARCHAR(50)
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 10
PROPERTIES (
"replication_num" = "1"
);
"""
execute_sql(text(create_table_sql))
# --- ATTEMPT TO CREATE ROW POLICY ---
log.info(f"Attempting to create ROW POLICY '{test_policy_name}' on {test_db_name}.{test_table_name}...")
create_rlp_sql = f"""
CREATE OR REPLACE ROW POLICY {test_policy_name} ON {test_table_name}
AS PERMISSIVE
FOR SELECT USING (customer_id = 'test_id');
"""
execute_sql(text(create_rlp_sql))
log.info(f"RESULT: Row Policy '{test_policy_name}' was created successfully. This indicates RLP is supported.")
except Exception as e:
log.error(f"RESULT: Failed to create Row Policy. Error: {e}")
print(f"\n--- CONFIRMATION OF RLP LACK OF SUPPORT ---")
print(f"Error: {e}")
print("This confirms that 'CREATE OR REPLACE ROW POLICY ... AS PERMISSIVE' syntax is NOT supported in your StarRocks environment.")
finally:
# Clean up: Drop test database
log.info(f"Attempting to drop test database {test_db_name}...")
try:
execute_sql(text(f"DROP DATABASE IF EXISTS {test_db_name};"))
log.info(f"Test database {test_db_name} dropped successfully.")
except Exception as e:
log.warning(f"Failed to drop test database {test_db_name}: {e}")
output:
Starting simple test to confirm CREATE ROW POLICY support.
Creating test database: rlp_test_db_confirm
Executing SQL:
CREATE DATABASE IF NOT EXISTS rlp_test_db_confirm;
Executing SQL:
USE rlp_test_db_confirm;
Creating test table: rlp_test_table_confirm
Executing SQL:
CREATE TABLE IF NOT EXISTS rlp_test_table_confirm (
id INT,
customer_id VARCHAR(50)
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 10
PROPERTIES (
"replication_num" = "1"
);
Attempting to create ROW POLICY 'my_rlp_policy_confirm' on rlp_test_db_confirm.rlp_test_table_confirm...
Executing SQL:
CREATE OR REPLACE ROW POLICY my_rlp_policy_confirm ON rlp_test_table_confirm
AS PERMISSIVE
FOR SELECT USING (customer_id = 'test_id');
StarRocks SQL execution failed: (pymysql.err.ProgrammingError) (1064, "Getting syntax error at line 2, column 26. Detail message: No viable statement for input 'CREATE OR REPLACE ROW'.")
[SQL:
CREATE OR REPLACE ROW POLICY my_rlp_policy_confirm ON rlp_test_table_confirm
AS PERMISSIVE
FOR SELECT USING (customer_id = 'test_id');
]
(Background on this error at: https://sqlalche.me/e/14/f405)
RESULT: Failed to create Row Policy. Error: (pymysql.err.ProgrammingError) (1064, "Getting syntax error at line 2, column 26. Detail message: No viable statement for input 'CREATE OR REPLACE ROW'.")
[SQL:
CREATE OR REPLACE ROW POLICY my_rlp_policy_confirm ON rlp_test_table_confirm
AS PERMISSIVE
FOR SELECT USING (customer_id = 'test_id');
]
(Background on this error at: https://sqlalche.me/e/14/f405)
--- CONFIRMATION OF RLP LACK OF SUPPORT ---
Error: (pymysql.err.ProgrammingError) (1064, "Getting syntax error at line 2, column 26. Detail message: No viable statement for input 'CREATE OR REPLACE ROW'.")
[SQL:
CREATE OR REPLACE ROW POLICY my_rlp_policy_confirm ON rlp_test_table_confirm
AS PERMISSIVE
FOR SELECT USING (customer_id = 'test_id');
]
(Background on this error at: https://sqlalche.me/e/14/f405)
This confirms that 'CREATE OR REPLACE ROW POLICY ... AS PERMISSIVE' syntax is NOT supported in your StarRocks environment.
Attempting to drop test database rlp_test_db_confirm...
Executing SQL:
DROP DATABASE IF EXISTS rlp_test_db_confirm;
Test database rlp_test_db_confirm dropped successfully.