Steps to clean duplicate entries in security_privilege table
Article ID: KB0083061
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | - |
Description
Steps to clean duplicate entries in security_privilege table. This causes performance issues for TDV users.
Resolution
1.
CREATE TABLE TEMP AS
SELECT * FROM SECURITY_PRIVILEGES
LIMIT 0;
2.
INSERT INTO TEMP
SELECT * FROM SECURITY_PRIVILEGES P
WHERE NOT EXISTS (
SELECT 1 FROM SECURITY_PRIVILEGES C
WHERE P.RESOURCE_ID = C.RESOURCE_ID AND
P.RESOURCE_CID = C.RESOURCE_CID AND
P.COLUMN_ID = C.COLUMN_ID AND
P.MEMBER_ID = C.MEMBER_ID AND
P.CID < C.CID
);
3.
TRUNCATE TABLE SECURITY_PRIVILEGES;
4.
INSERT INTO SECURITY_PRIVILEGES
SELECT * FROM TEMP;
5.
COMMIT;
6.
DROP TABLE TEMP;
Disclaimer: Before running these steps, It is vital to contact the the Support team. In the later versions (starting 7.0.2 and higher), TDV handles the cleanup through the autovacuum process.
CREATE TABLE TEMP AS
SELECT * FROM SECURITY_PRIVILEGES
LIMIT 0;
2.
INSERT INTO TEMP
SELECT * FROM SECURITY_PRIVILEGES P
WHERE NOT EXISTS (
SELECT 1 FROM SECURITY_PRIVILEGES C
WHERE P.RESOURCE_ID = C.RESOURCE_ID AND
P.RESOURCE_CID = C.RESOURCE_CID AND
P.COLUMN_ID = C.COLUMN_ID AND
P.MEMBER_ID = C.MEMBER_ID AND
P.CID < C.CID
);
3.
TRUNCATE TABLE SECURITY_PRIVILEGES;
4.
INSERT INTO SECURITY_PRIVILEGES
SELECT * FROM TEMP;
5.
COMMIT;
6.
DROP TABLE TEMP;
Disclaimer: Before running these steps, It is vital to contact the the Support team. In the later versions (starting 7.0.2 and higher), TDV handles the cleanup through the autovacuum process.
Issue/Introduction
Steps to clean duplicate entries in security_privilege table
Feedback
Yes
No
Powered by
