The Drupal Security Advisory is a program of the Drupal community (Drupal Security Team) that is dedicated to review reported security flaws, contributed code, and to manage fixing identified security issues and releasing scheduled security updates (in the case of contributed modules together with its maintainers).
For contributed modules, opting into security advisory means, that stable releases of the module may be reviewed for obvious security flaws by the security team. (Note, only stable releases are covered by security advisory, not development versions, or alpha/beta/rc pre-release versions.) Also, available security updates/fixes will be announced within the Security announcements that conveniently are available as RSS feeds and can be subscribed to on Twitter.
New module maintainers can optionally opt-in their module(s) to that program, after they have proven a basic understanding of Drupal Coding Standards and best practices by applying with one of their modules within the Security Advisory Applications queue. The process can sometimes take several months to complete, as it is based on voluntarily conducted reviews by the Drupal developer community and a final approval by a dedicated team of Drupal webmasters that have to take that time for reviewing the source code as well.
A module that still has the banner stating there is no security advisory coverage means that the maintainers are new to the Drupal eco system and therefore do not automatically have the right to opt in as they did not undergo the above mentioned review. (Or they just did not opt into security advisory because there is no stable release of their module yet, or they just forgot about it.)
Now to the security concerns:
A module without security advisory coverage is not automatically bad or has security issues. It may receive bug fixes and further features by its maintainers and/or contributions (patches) from the Drupal community, as all other modules on drupal.org do.
Good indicators always are the module's usage statistics, the activity within its issue queue, and the maintenance status stated on the module page.
A module with high usage is likely to not having many (unidentified) bugs or other issues left. With modules having a low usage (that exist for very special use cases), I'd always go for manual own code reviews to check the code quality and what the module actually does.
As security updates will not be announced within the security announcements, you will want to shortlist/follow the issue queues of any such modules you use within your projects, in order to keep a close eye on important issues/fixes coming up.