Adding default configurations for many-to-many tables

Question

MySQL here. I have the following users table:

describe users;
+----------------------------------+---------------------+------+-----+---------+----------------+
| Field                            | Type                | Null | Key | Default | Extra          |
+----------------------------------+---------------------+------+-----+---------+----------------+
| user_id                          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_status_id                   | bigint(20) unsigned | NO   | MUL | NULL    |                |
| profile_id                       | bigint(20) unsigned | YES  | MUL | NULL    |                |
+----------------------------------+---------------------+------+-----+---------+----------------+

I would know like to add a simple RBAC model where:

• Users can have 0+ Roles
• Roles have 0+ Permissions

My proposed table structure:

[roles] table
---
role_id : BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT
role_name : VARCHAR(50)

[permissions] table
---
permission_id : BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT
permission_name : VARCHAR(50)

[roles_x_permissions] many:many table
---
roles_x_permissions_id : BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT
role_id : BIGINT UNSIGNED FOREIGN KEY to [roles] table
permission_id : BIGINT UNSIGNED FOREIGN KEY to [permissions] table

[users_x_roles] many:many table
---
users_x_roles_id : BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT
user_id : BIGINT UNSIGNED FOREIGN KEY to [users] table
role_id : BIGINT UNSIGNED FOREIGN KEY to [roles] table

And the actual Roles & Permissions the app will support (at least initially):

INSERT INTO permissions (
  permission_name
) VALUES (
  'CanDoSomeBasicStuff'
);

INSERT INTO roles (
  role_name
) VALUES (
  'BasicUser'
);

INSERT INTO roles_x_permissions (
  role_id,
  permission_id
) VALUES (
  1,    // BasicUser
  1     // CanDoSomeBasicStuff
);

INSERT INTO users_x_roles (
  user_id,
  role_id
) VALUES (
  ???
);

Where I'm struggling is this: by default, all existing users will have the BasicUser role. I need an elegant way to assign all my existing users to this BasicUser role in a single command, but I can't figure what the SQL command needs to look like:

INSERT INTO users_x_roles uxr (
  user_id,
  role_id
) VALUES (
  ???,   # How to do this for each user record in the users table?
  ???    # How to fetch the correct role_id for the BasicUser role?
)

Any ideas what this SQL command might look like?

Answer 1

The INSERT ... VALUES syntax inserts a single record. You need to use INSERT ... SELECT syntax, like this:

INSERT INTO users_x_roles (
  user_id,
  role_id
)
 SELECT 
  user_id, 
  (SELECT MAX(role_id) FROM roles where role_name='BasicUser')
 FROM users;

Or, if you do not like the subquery:

INSERT INTO users_x_roles (
  user_id,
  role_id
)
 SELECT 
  u.user_id, 
  r.role_id 
 FROM users u CROSS JOIN roles r where r.role_name='BasicUser';

Update: the typo was in the INSERT statement. I have copied your code and used the syntax like: INSERT INTO users_x_roles uxr, which is not valid: you can't use aliases in the Insert Into clause.

Now it all works fine. Check the SQL Fiddle