search

felipebz / zpa

Parser and static code analysis tool for PL/SQL and Oracle SQL.
https://zpa.felipebz.com
GNU Lesser General Public License v3.0
211 stars 77 forks source link

Custom rule to flag usage of CREATE PUBLIC SYNONYM #177

Open csrvsk opened 7 months ago

csrvsk commented 7 months ago

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 commented 7 months ago

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.}}