Defining a query-based test

Create a test based on a query that runs an SQL statement.

1. Open the Assessment Builder by clicking Harden > Vulnerability Assessment > Assessment Builder.
2. From the User-defined tests, click Query-based Tests.
3. Click New, Clone or Modify to open the Query-based Test Builder.
4. Enter a unique Test Name.
5. Select a Database Type.
6. Select a Category.
7. Select a Severity.
8. Optional: Enter a Short Description for the test.
9. Optional: Enter an External Reference for the test.
10. Enter the Result text for pass that will be displayed when the test passes.
11. Enter the Result text for fail that will be displayed when the test fails.
12. Enter the SQL statement that will be run for the test.

    Use the following convention to add and reference group members within a SQL statement:

    For example:

    To reference a group of users defined for the group MyUsersGroup and replace it with the actual members of the group use:

     Select ... from DBA_GRANTS where ... AND USER in (~~G~MyUsersGroup~~) and ... 

    This will result in a SQL Statement such as the following where U1, U2, etc are the members of the MyUsersGroup group:

    Select ... from DBA_GRANTS where ... AND USER in ('U1','U2','U3',...) and ... 

    If the group has no members, the database returns an error. In this case the reference is replaced with a single pair of quotation marks, like this:

    Select ... from DBA_GRANTS where ... AND USER in ('') and ... 

    Use the following convention to replace a reference to a specific alias (of a specific group type) with the actual alias:

    For example:

    Select ... from USER_OBJECTS where ... AND OBJECT_TYPE = '~~A~GroupType~TYPE~~'

    If there is an alias to TYPE of group type GrouptType it will replace the string and the resulting SQL will look like:

    Select ... from USER_OBJECTS where ... AND OBJECT_TYPE = 'TYPE'

    where TYPE is the actual ALIAS

13. Optional: Enter a SQL Statement for Detail, a SQL statement that retrieves a list of strings to generate a detail string of Detail prefix + list of strings. See the example in Detail prefix.
    Note: The detail generated is only displayed when the query-based test fails; allowing the user to enter a SQL statement that can retrieve the information that caused the test to fail and help identify the cause of failure.

    Note: Detail string can be seen within a Security Assessment Results by clicking on the Assessment Test Name and also queried through the Result Details attribute of the Test Result Entity.
14. Optional: Enter a Pre-test check SQL statement. This statement is run before running the test. If the statement returns 0, the test is not run. If the test returns 1 or an error, the test is run.
15. Optional: Enter a Pre-test fail message. This message is inserted into the assessment results if the test is not run due to the SQL statement returning 0.
16. Optional: In Loop databases, enter a list of databases through which the test should loop. The test returns the union or sum of the results returned from all the specified databases. You can use this function only when the test returns an integer value, and only with these database types: Informix, SQL Server, Sybase SE, PostgreSQL and MySQL. The looping is performed if the DB loop flag box is checked.
    One or more of the specified databases might be unavailable when the test is run. In that case the test will either skip that database and continue, or stop and issue a failure message, depending on whether the Skip on error box is checked.
17. Optional: Enter a Detail prefix that will appear at the beginning of the detail string.

    Example for SQL Statement for Detail & Detail prefix: 
    Test that checks for objects with certain grants. 
    Detail prefix: "Objects found with certain GRANT:" 
    SQL Statement for Detail: SELECT object FROM....--returning 4 records: 
          Obj1 
          Obj2 
          Obj3 
          Obj4 
    ==> Details: Objects found with certain GRANT: Obj1, Obj2, Obj3, Obj4 
    

18. Optional: Check the Bind output variable check box if the entered text in SQL statement is a procedural block of code that will return a value that should be bound to an internal Guardium® variable that will be used in the comparison to the Compare to value.

    Example (Oracle):
    declare
        retval integer := 0;
        strval varchar2(255) := '';
        nver number;
        sver varchar2(255) := '';
    begin
        select VERSION
        into sver
        from V$INSTANCE;
        nver := to_number(substr(sver,1,(instr(sver,'.',1,2) - 1)));
        if nver >= 11.1 then
            select VALUE
            into strval
            from V$PARAMETER
            where NAME = 'sec_case_sensitive_logon';
        end if;
        if (nver < 11.1 or strval = 'TRUE') then
            retval := 0;
        else
            retval := 1;
        end if;
        ? := retval;
    end;
    

19. Select the Return type that will be returned from the SQL statement.
20. Select the operator that will be used for the condition.
21. Enter in a Compare to value that will be used to compare against the return value from the SQL statement using the compare operator. It is this comparison that determines whether this test have passed or failed. You may also click on the RE (regex) to define a regular expression for the compare value.
22. Do one of the following:
    • Click Back to cancel changes and return to the previous screen.
    • Click Apply to save the query-based test.