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
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.
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?
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.
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!
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 ));
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 3.3.0.6, 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)
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.
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)
