Page 1 of 1

how to grant a user Administrative rights using phpMyAdmin4

Posted: Wed May 03, 2017 5:08 pm
by keysearch
Hey guys, I'm hosting a forum board using PHPBB, unfortunately I had lost the password to the board and the domain I sued for the e-mail of that administrator's account was also lost. I've changed the email admin's address from the config.php file but that didn't change a thing, I've registered with a new user recently and now my question is: how to grant a PHPBB user Administrative rights on a forum board through or MySQL or phpMyAdmin4? Which field from what table should I modify [phpMyAdmin4 Screen][1] [1]: https://i.stack.imgur.com/kVhQg.png I'm pretty much new to all this so please make the answers as details as possible, (preferably with pictures)

If question is off topic please direct me to the correct section.
Anticipating thanks

Re: how to grant a user Administrative rights using phpMyAdmin4

Posted: Wed May 03, 2017 5:25 pm
by stevemaury
In phpmyadmin, run the following, which will create an admin user named Admin1 with a password of admin. From that point you should be able to get into the ACP. Change your table prefix if it is not phpbb_.

Code: Select all

INSERT INTO phpbb_users (user_type, group_id, username, username_clean, user_regdate, user_password, user_email, user_lang, user_style, user_rank, user_colour, user_posts, user_permissions, user_ip, user_birthday, user_lastpage, user_last_confirm_key, user_post_sortby_type, user_post_sortby_dir, user_topic_sortby_type, user_topic_sortby_dir, user_avatar, user_sig, user_sig_bbcode_uid, user_from, user_icq, user_aim, user_yim, user_msnm, user_jabber, user_website, user_occ, user_interests, user_actkey, user_newpasswd) VALUES (3, 5, 'Admin1', 'admin1', 0, '21232f297a57a5a743894a0e4a801fc3', 'admin@yourdomain.com', 'en', 1, 1, 'AA0000', 1, '', '', '', '', '', 't', 'a', 't', 'd', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
Executing SQL Queries in phpMyAdmin

As soon as you have done this, use the temporary admin account to change the details on the original admin account, then delete the temporary account.
This is because:
  1. anyone could use that account to log in to your board if you didn’t change the password.
  2. this temporary user has not been fully set up (e.g. it is not a member of the “Registered users” group, so it won’t have normal access to your forums).
To remove the account you will first need to remove “founder” status from it: ACP > USERS AND GROUPS tab > Manage Users > Admin1 > Overview > Founder = No

Re: how to grant a user Administrative rights using phpMyAdmin4

Posted: Wed May 03, 2017 6:30 pm
by keysearch
Thanks a lot
stevemaury
but I've followed the instructions to the letter but I'm still unable to log in. I've used username: admin1 password admin and also tried with username Admin1 with the same password and got the same result. Maybe there is something else that I'm missing can you please help me?
ture.PNG
I know that it was done right because when I've tried to do it again I got the duplicate error message from MYSQL

Re: how to grant a user Administrative rights using phpMyAdmin4

Posted: Wed May 03, 2017 10:36 pm
by stevemaury
The error message means you've already successfully run the query once. What happens when you try to log in?

You can try running this query on your existing username you mentioned in your first post:

Code: Select all

UPDATE phpbb_users SET user_type = 3 WHERE username = 'whatever';
UPDATE phpbb_users SET user_group = 5 WHERE username = 'whatever'

Re: how to grant a user Administrative rights using phpMyAdmin4

Posted: Thu May 04, 2017 3:10 pm
by keysearch
Alright now since I had tried with Admin1 yesterday and that didn't work for me so this is what I've done today.

Code: Select all

INSERT INTO phpbb_users (user_type, group_id, username, username_clean, user_regdate, user_password, user_email, user_lang, user_style, user_rank, user_colour, user_posts, user_permissions, user_ip, user_birthday, user_lastpage, user_last_confirm_key, user_post_sortby_type, user_post_sortby_dir, user_topic_sortby_type, user_topic_sortby_dir, user_avatar, user_sig, user_sig_bbcode_uid, user_from, user_icq, user_aim, user_yim, user_msnm, user_jabber, user_website, user_occ, user_interests, user_actkey, user_newpasswd) VALUES (3, 5, 'Admin2', 'admin3', 0, '21232f297a57a5a743894a0e4a801fc3', 'admin@yourdomain.com', 'en', 1, 1, 'AA0000', 1, '', '', '', '', '', 't', 'a', 't', 'd', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
meaning
username: Admin2
password: admin3
but this is what I was getting. When tried to login to the forum
You have specified an incorrect username. Please check your username and try again. If you continue to have problems please contact the Board Administrator.
so I've moved on to the new piece of information that i had

Code: Select all

UPDATE phpbb_users SET user_type = 3 WHERE Admin2 = 'whatever';
UPDATE phpbb_users SET user_group = 5 WHERE admin3 = 'whatever'
and that's what I'm getting
sql.PNG
Am I missing something here? what's going on? can anybody help me?

Re: how to grant a user Administrative rights using phpMyAdmin4

Posted: Thu May 04, 2017 3:22 pm
by david63
Instead of WHERE Admin2 = 'whatever'; you should be using WHERE username = 'Admin2';[

Re: how to grant a user Administrative rights using phpMyAdmin4

Posted: Thu May 04, 2017 4:25 pm
by keysearch
Instead of WHERE Admin2 = 'whatever'; you should be using WHERE username = 'Admin2';
ok great I got it, now what would the password be? or where do I declare or assign the password?

Re: how to grant a user Administrative rights using phpMyAdmin4

Posted: Thu May 04, 2017 11:12 pm
by stevemaury
You are running this on an existing user, right? So the password would be whatever it was before. You don't need to specify it. In this query, JUST substitute the user's username for 'whatever':

Code: Select all

UPDATE phpbb_users SET user_type = 3 WHERE username = 'whatever';
UPDATE phpbb_users SET user_group = 5 WHERE username = 'whatever'

Re: how to grant a user Administrative rights using phpMyAdmin4

Posted: Fri May 05, 2017 12:50 am
by 3Di
keysearch wrote:
Thu May 04, 2017 3:10 pm
Alright now since I had tried with Admin1 yesterday and that didn't work for me so this is what I've done today.

Code: Select all

INSERT INTO phpbb_users (user_type, group_id, username, username_clean, user_regdate, user_password, user_email, user_lang, user_style, user_rank, user_colour, user_posts, user_permissions, user_ip, user_birthday, user_lastpage, user_last_confirm_key, user_post_sortby_type, user_post_sortby_dir, user_topic_sortby_type, user_topic_sortby_dir, user_avatar, user_sig, user_sig_bbcode_uid, user_from, user_icq, user_aim, user_yim, user_msnm, user_jabber, user_website, user_occ, user_interests, user_actkey, user_newpasswd) VALUES (3, 5, 'Admin2', 'admin3', 0, '21232f297a57a5a743894a0e4a801fc3', 'admin@yourdomain.com', 'en', 1, 1, 'AA0000', 1, '', '', '', '', '', 't', 'a', 't', 'd', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
meaning
username: Admin2
password: admin3
Playing with the DB without having the necessary skills sometime could be very dangerous, in this case you are misinterpretating the code (query) itself.

Code: Select all

'Admin2', 'admin3', 0, '21232f297a57a5a743894a0e4a801fc3'
means

Code: Select all

username, usename_clean,user_regdate, user_password
All of this been said, you tried to create an user with 2 usernames at once, basicly. Errn.


To be clear, the first two are respectively the name displayed (ie.: 3Di) and the username clean in the DB (i.e.: 3di). The third one is the registration date, we don't need that that's why is set to zero. The latter IS the PW in md5 hashed form.

See: http://md5now.com/
try and insert admin and generate the md5 (utf-8), you will see that the corresponding hash generated is 1232f297a57a5a743894a0e4a801fc3 (the same as per the above suggested query, which I suspect is for 3.0.x).

If you insert passwordforgotten as the desired hashed password would result in 46650caa32f2fc242005ee7dafeb7894

Let's say you want now to have an admin named LetItBeTheAdmin with as the password passwordforgotten .. the query will be

Code: Select all

INSERT INTO phpbb_users (user_type, group_id, username, username_clean, user_regdate, user_password, user_email, user_lang, user_style, user_rank, user_colour, user_posts, user_permissions, user_ip, user_birthday, user_lastpage, user_last_confirm_key, user_post_sortby_type, user_post_sortby_dir, user_topic_sortby_type, user_topic_sortby_dir, user_avatar, user_sig, user_sig_bbcode_uid, user_from, user_icq, user_aim, user_yim, user_msnm, user_jabber, user_website, user_occ, user_interests, user_actkey, user_newpasswd) VALUES (3, 5, 'LetItBeTheAdmin', 'letitbetheadmin', 0, '46650caa32f2fc242005ee7dafeb7894', 'admin@yourdomain.com', 'en', 1, 1, 'AA0000', 1, '', '', '', '', '', 't', 'a', 't', 'd', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
Next time, for the posterity, this issue can be easily solved (having FTP access) with this automated Tool I wrote one year ago: [3.0] or [3.1][3.2][TOOL] SOS Admin. Which uses the Common DBAL methods, means compliant with all of the phpBB's supported DBs.

Re: how to grant a user Administrative rights using phpMyAdmin4

Posted: Fri May 05, 2017 9:26 am
by RMcGirr83
keysearch wrote:
Wed May 03, 2017 5:08 pm
I've registered with a new user recently and now my question is: how to grant a PHPBB user Administrative rights on a forum board through or MySQL or phpMyAdmin4?
You simply have to give that user a user_type of 3 in the database. That gives them founder access which, bascially, gives the user access to everything in the ACP.