change the permission of all users

[gacon]

Hello all,

I would like to change the permission of all users on my site from author to registered, which table and field I should modify?

I'm using CB component.

Thanks,

[bascherz]

Open (browse) the table named jos_core_acl_aro_groups. It will list the various types of users. Write down the group_id for the user type "Registered". For the sake of example, let's say that number is 18. Next, enter the following SQL command:

Code: Select all

UPDATE jos_users SET gid=18, usertype='Registered' WHERE usertype='Author';

That should do the trick for you.

Of course, always make a backup of the table you are modifying and it's always a good idea to take your site offline while you do this kind of stuff.

[gacon]

I have used this command:

UPDATE jos_users SET gid=18, usertype='Registered' WHERE id > 63;

When i check in the PhpMyAdmin window:



But when i use User Manage of Joomla, nothing change:




thanks for your help!

[gacon]

This is the content of jos_core_acl_groups_aro_map





Note: I have removed the CB component


Thanks for your help!

[bascherz]

In the response I ended up deleting, I was trying to write an SQL statement that would set jos_core_acl_groups_aro_map.group_id to 18 for every user whose jos_users.id is > 67 (or whose jos_core_acl_aro.value > 67). I believe that's what is needed, though I am struggling to create a single SQL statement that does it. I am not sure it can be done entirely in SQL, but I have reached the limit of my MySQL expertise trying to solve this. It may require some coding (PHP or otherwise).

The following invalid SQL statement captures the spirit of what is needed, but does not work:

Code: Select all

...
UPDATE
(SELECT group_id FROM jos_core_acl_aro AS acl
 INNER JOIN jos_core_acl_groups_aro_map AS map
 ON acl.aro_id=map.aro_id
 WHERE map.value>67 AND map.group_id=19)
AS temp_table
SET temp_table.group_id=18
...

I apologize for leading you down this path only to get lost. Negative karma for me!

[jcisio]

Something like this should work:

Code: Select all

UPDATE users, acl_map, aro
SET users.type='Registered', users.gid=18, acl_map.gid=18
WHERE users.type='Author' AND users.id=aro.userid AND aro.acl=acl_map.aro

(from http://forum.joomla.org/index.php/topic,213582.0.html)

[gacon]

Something like this should work:

Code: Select all

UPDATE users, acl_map, aro
SET users.type='Registered', users.gid=18, acl_map.gid=18
WHERE users.type='Author' AND[b] users.id=aro.userid[/b] AND aro.acl=acl_map.aro

(from http://forum.joomla.org/index.php/topic,213582.0.html)

You have tried? There is no the field userid in the table jos_core_acl_aro (I made in bold in your code).

[jcisio]

Something like this should work:

Code: Select all

UPDATE users, acl_map, aro
SET users.type='Registered', users.gid=18, acl_map.gid=18
WHERE users.type='Author' AND[b] users.id=aro.userid[/b] AND aro.acl=acl_map.aro

(from http://forum.joomla.org/index.php/topic,213582.0.html)

You have tried? There is no the field userid in the table jos_core_acl_aro (I made in bold in your code).

I'm pretty sure, at least it's syntaxically correct.

ARO has a relation Access <-> Request (User Id, Group Id), feel free to modify to fit your need.

[gacon]

not yet successful. Anyone can help me?

[jcisio]

not yet successful. Anyone can help me?

Have you tried my query ? Please post the one you tried if it didn't work.

[gacon]

please review my previous post.

There is no the field userid in the table jos_core_acl_aro (I made in bold in your code).

[jcisio]

please review my previous post.

There is no the field userid in the table jos_core_acl_aro (I made in bold in your code).

Change to the appropriate field (as I said before). The query shouldn't work and I don't have Joomla! db structure here, but if you change it, it will work. I just figure the idea, hope you get it.

[ircmaxell]

This will sync your jos_core_acl_groups_aro_map with jos_users

Code: Select all

UPDATE jos_core_acl_groups_aro_map AS a
JOIN jos_core_acl_aro AS b ON a.aro_id = b.aro_id
JOIN jos_users AS c ON b.value = c.id
SET a.group_id = c.gid

[gacon]

Thanks so much 

It's well done with this command.

This will sync your jos_core_acl_groups_aro_map with jos_users

Code: Select all

UPDATE jos_core_acl_groups_aro_map AS a
JOIN jos_core_acl_aro AS b ON a.aro_id = b.aro_id
JOIN jos_users AS c ON b.value = c.id
SET a.group_id = c.gid