Help to identify suspicious users or users with no roles in a journal


Would it be possible to develop a script that would alert OJS administrators or journal manager of new user profiles? An alert would enable journal managers and system admins to intercept attempts by suspicious users intending to place harmful content in folders (e.g., public/site/images). Also, is there a way to query a MySQL database in the cpanel to identify users who are not authors, editor, or reviewers? Is there or would it be possible to create a script for such a query? Thanks! – asabhar

Hi @asabhar,

For the second part - not in MySQL but in the user interface for OJS, you can search for users who are not affiliated with a journal (e.g. have no roles assigned): Learning Open Journal Systems 3.3 - Users and Roles

There is likely a way to do this via SQL queries, so someone else may suggest a way to do this, but others may have to suggest this.

Best regards,

PKP Team

Thank you @rcgillis. Yes, the OJS user search can help but the drop down list does not have a “no roles” category, and it is limited to a single journal. An SQL query will probably work better and it will also show me when they logged in the last time or when they created their profile. I did find the SQL tab under phpmyadmin, so I am about to re-learn SQL queries, which I have not done since 2003. Is there a cpanel manual for SQL queries?

Best wishes!

Hi @asabhar,

I think you’d have to be familiar with the table structures, or at least have a close look at them, in order to generate specific queries like that. I’ll see if I can get someone from our development team to suggest how to best go about this when they are available.

Best regards,

PKP Team

I had worked extensively with relational databases in MS Access and have learned more about SQL in 2003. I have not followed the development on SQL since then, but the concept is familiar: I have looked through the tables but it would be helpful to see those relationships mapped in some sort of a visualization layer. True: I would need to locate the users, roles, journals tables and then bring them into a query. So if there is a way to see existing SQL queries in OJS cpanels, I can try to use those as models for my queries. I will appreciate any suggestions, pointers, or working scripts. Thanks, Roger!

Arjun @asabhar

To find out the users who are not associated with any journal, I use this query:
SELECT * FROM .users WHERE user_id NOT IN (SELECT user_id FROM .user_user_groups WHERE user_group_id IN ( SELECT user_group_id FROM .user_groups WHERE context_id > 0 ));

1 Like

Thank you @ugp2. This is very helpful, as it would have taken me much longer to build this SQL statement. I went into phpmyadmin in cpanel and copied your statement, but not having used the cpanel in this way, I would need to know how I use SQL queries on a regular basis. How can I save this query for future uses? I have attached a screenshot of what I see.

Also, I am at, but my databases is still showing _ojsdb_3.2.x in the navigation menu. Does it need to show 3.3.x. Many thanks! – Arjun (@asabhar)
Screenshot 2022-02-14 at 09-01-12

Follow-up. Never mind the previous reply. I have realized that I needed to go into the “Ouery” tab, so I copied your statement, and it did produce a list of suspects.
Screenshot 2022-02-14 at 09-44-09
Screenshot 2022-02-14 at 09-45-19

I would still like to know how I save and perform this query periodically, or is there a cpanel function that can send me a report periodically? Many thanks! – Arjun (@asabhar)