Custom rule to flag usage of CREATE PUBLIC SYNONYM

[csrvsk]

Hi Filepe,

Here is my code public class PreventPublicSynonymCheck extends PlSqlCheck {

private static final Logger LOGGER = Logger.getLogger(PreventPublicSynonymCheck.class.getName());

@Override
public void init() {
    subscribeTo(DdlGrammar.CREATE_SYNONYM);
    LOGGER.info("Subscribed to CREATE_SYNONYM nodes for PreventPublicSynonymCheck");
}

@Override
public void visitNode(AstNode node) {
    LOGGER.info("Visiting a node for PreventPublicSynonymCheck");
    LOGGER.info("Node type: " + node.getType());

    // Check if the node is a CREATE_SYNONYM statement
    if (node.is(DdlGrammar.CREATE_SYNONYM)) {
        boolean isPublicSynonym = false;
        String synonymName = "";

        // Iterate through the children of the CREATE_SYNONYM node
        for (AstNode child : node.getChildren()) {
            // Check if the child is of type UNIT_NAME
            if (child.is(PlSqlGrammar.UNIT_NAME)) {
                synonymName = child.getTokenOriginalValue().toUpperCase();
            }
        }

        // Check if the synonym is public
        if (!synonymName.isEmpty()) {
            int lineNumber = node.getTokenLine();
            String errorMessage = String.format("Creation of public synonym '%s' is not allowed at Line #%d.", synonymName, lineNumber);

            LOGGER.info("Generated Error Message: " + errorMessage);
            LOGGER.warning(errorMessage);
            addIssue(node.getToken(), errorMessage);
        }
    }
}

}

Here my test class: public class PreventPublicSynonymCheckTest {

@Test
public void testPreventPublicSynonymCheck() {
    String testFilePath = "src/test/resources/PreventPublicSynonymTest.sql"; // Path to your test SQL file
    PreventPublicSynonymCheck check = new PreventPublicSynonymCheck();

    try {
        PlSqlCheckVerifier.Companion.verify(testFilePath, check);
    } catch (AssertionError e) {
        e.printStackTrace();
        fail(e.getMessage());
    }
}

}

Here is my sql test case:

-- PreventPublicSynonymTest.sql

CREATE PUBLIC SYNONYM XYZ_Financials FOR some_schema.financial_table; -- Noncompliant {{Public synonym for XYZ object at Line #4.}}

CREATE PUBLIC SYNONYM XYZ_Reports FOR some_schema.report_table; -- Noncompliant {{Public synonym for XYZ object at Line #7.}}

-- Compliant: Private synonym, should not trigger a violation CREATE SYNONYM Local_Financials FOR some_schema.financial_table;

CREATE PUBLIC SYNONYM NonXYZ_Public FOR some_schema.other_table; -- Noncompliant {{Public synonym for XYZ object at Line #13.}}

With this I am getting the following error,

Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck init INFO: Subscribed to CREATE_SYNONYM nodes for PreventPublicSynonymCheck Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode INFO: Visiting a node for PreventPublicSynonymCheck Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode INFO: Node type: CREATE_SYNONYM Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode INFO: Generated Error Message: Creation of public synonym 'XYZ_FINANCIALS' is not allowed at Line #4. Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode WARNING: Creation of public synonym 'XYZ_FINANCIALS' is not allowed at Line #4. Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode INFO: Visiting a node for PreventPublicSynonymCheck Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode INFO: Node type: CREATE_SYNONYM Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode INFO: Generated Error Message: Creation of public synonym 'XYZ_REPORTS' is not allowed at Line #7. Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode WARNING: Creation of public synonym 'XYZ_REPORTS' is not allowed at Line #7. Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode INFO: Visiting a node for PreventPublicSynonymCheck Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode INFO: Node type: CREATE_SYNONYM Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode INFO: Generated Error Message: Creation of public synonym 'LOCAL_FINANCIALS' is not allowed at Line #10. Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode WARNING: Creation of public synonym 'LOCAL_FINANCIALS' is not allowed at Line #10. Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode INFO: Visiting a node for PreventPublicSynonymCheck Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode INFO: Node type: CREATE_SYNONYM Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode INFO: Generated Error Message: Creation of public synonym 'NONXYZ_PUBLIC' is not allowed at Line #13. Jan 24, 2024 2:34:54 PM com.company.plsql.PreventPublicSynonymCheck visitNode WARNING: Creation of public synonym 'NONXYZ_PUBLIC' is not allowed at Line #13. java.lang.AssertionError: Bad message at line 4 at org.sonar.plsqlopen.checks.verifier.PlSqlCheckVerifier$Companion.assertEquals(PlSqlCheckVerifier.kt:145) at org.sonar.plsqlopen.checks.verifier.PlSqlCheckVerifier$Companion.verifyIssue(PlSqlCheckVerifier.kt:133) at org.sonar.plsqlopen.checks.verifier.PlSqlCheckVerifier$Companion.verify(PlSqlCheckVerifier.kt:113) at org.sonar.plsqlopen.checks.verifier.PlSqlCheckVerifier$Companion.verify$default(PlSqlCheckVerifier.kt:101) at org.sonar.plsqlopen.checks.verifier.PlSqlCheckVerifier$Companion.verify(PlSqlCheckVerifier.kt) at com.company.plsql.PreventPublicSynonymCheckTest.testPreventPublicSynonymCheck(PreventPublicSynonymCheckTest.java:17) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Not sure what am I misisng? To investigate I have added logger statements to see what is the expected error message and what is the actual error message being generate. From the output it is evident that both are matching. But it still saying "Bad message at Line 4."

Please check it and guide me how to fix it??

Thanks csrvsk