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

[felipebz]

Hi,

In your test file you're expecting a message like "Public synonym for XYZ object at Line #4." but in the check code the message uses the following format "Creation of public synonym '%s' is not allowed at Line #%d."

The text inside the Noncompliant {{ }} should be the exact message registered by the check, for example:

-- Noncompliant {{Creation of public synonym 'XYZ_FINANCIALS' is not allowed at Line #4.}}