how to grant a user Administrative rights using phpMyAdmin4

Get help with installation and running phpBB 3.1.x here. Please do not post bug reports, feature requests, or extension related questions here.
Scam Warning
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: phpBB 3.1.x is at its End of Life stage and support will NOT be provided after July 1st, 2018.
Locked
User avatar
keysearch
Registered User
Posts: 18
Joined: Tue Feb 21, 2012 6:21 pm
Contact:

how to grant a user Administrative rights using phpMyAdmin4

Post by keysearch » Wed May 03, 2017 5:08 pm

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

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50248
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: how to grant a user Administrative rights using phpMyAdmin4

Post by stevemaury » Wed May 03, 2017 5:25 pm

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
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

User avatar
keysearch
Registered User
Posts: 18
Joined: Tue Feb 21, 2012 6:21 pm
Contact:

Re: how to grant a user Administrative rights using phpMyAdmin4

Post by keysearch » Wed May 03, 2017 6:30 pm

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

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50248
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: how to grant a user Administrative rights using phpMyAdmin4

Post by stevemaury » Wed May 03, 2017 10:36 pm

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'
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

User avatar
keysearch
Registered User
Posts: 18
Joined: Tue Feb 21, 2012 6:21 pm
Contact:

Re: how to grant a user Administrative rights using phpMyAdmin4

Post by keysearch » 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
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?

User avatar
david63
Jr. Extension Validator
Posts: 15759
Joined: Thu Dec 19, 2002 8:08 am
Location: Lancashire, UK
Name: David Wood
Contact:

Re: how to grant a user Administrative rights using phpMyAdmin4

Post by david63 » Thu May 04, 2017 3:22 pm

Instead of WHERE Admin2 = 'whatever'; you should be using WHERE username = 'Admin2';[
David
Remember: You only know what you know and - you don't know what you don't know!
My CDB Contributions | How to install an extension
I will not be accepting translations for any of my extensions in Github - please post any translations in the appropriate topic.
No support requests via PM or email as they will be ignored

User avatar
keysearch
Registered User
Posts: 18
Joined: Tue Feb 21, 2012 6:21 pm
Contact:

Re: how to grant a user Administrative rights using phpMyAdmin4

Post by keysearch » Thu May 04, 2017 4:25 pm

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?

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50248
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: how to grant a user Administrative rights using phpMyAdmin4

Post by stevemaury » Thu May 04, 2017 11:12 pm

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'
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

User avatar
3Di
Former Team Member
Posts: 13471
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: how to grant a user Administrative rights using phpMyAdmin4

Post by 3Di » Fri May 05, 2017 12:50 am

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.
Please PM me only to request paid works. Thx.
Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user
Extensions, Scripts, MOD porting, Update/Upgrades
__________________________________
👨‍🏫 | Take a tour to | The Studio | 👨‍🏫

User avatar
RMcGirr83
Recognised Extension Developer
Posts: 21032
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr
Contact:

Re: how to grant a user Administrative rights using phpMyAdmin4

Post by RMcGirr83 » Fri May 05, 2017 9:26 am

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.
In times of change, learners inherit the earth, while the learned find themselves beautifully equipped to deal with a world that no longer exists - Eric Hoffer
Former Modifications/Extensions Team Member | My extensions
Appreciate the extensions/mods/support then buy me a beer
All requests for support via PM will be ignored

Locked

Return to “[3.1.x] Support Forum”

Who is online

Users browsing this forum: No registered users and 11 guests