Where is the admin data located in Wordpress (WP) in MySQL

In a WordPress installation, the admin data, particularly user information, is stored in the MySQL database. 

What is the WordPress DB Structure?

WordPress follows a specific schema to manage its data, and the admin data is spread across multiple tables within this schema. Here’s a detailed breakdown:

Users Table (wp_users)

Location: The main user data, including admin users, is stored in the wp_users table.

Columns:

ID: A unique identifier for each user.

user_login: The username for login.

user_pass: The hashed password for the user.

user_nicename: A URL-friendly name for the user.

user_email: The user’s email address.

user_registered: The date the user was registered.

user_status: Status of the user (generally not used in standard WordPress).

display_name: The name displayed publicly for the user.

Purpose: This table holds the core information about each user, including their username, email, and hashed password.

Usermeta Table (wp_usermeta)

Location: Additional metadata for users, including admin data, is stored in the wp_usermeta table.

Columns:

umeta_id: A unique identifier for each usermeta record.

user_id: Links to the ID in the wp_users table.

meta_key: The key for the metadata (e.g., wp_capabilities, wp_user_level).

meta_value: The value for the metadata.

Purpose: This table is used to store additional details about users. 

For example, the wp_capabilities meta_key holds the roles and capabilities assigned to the user. An admin user would have a capability like administrator.

User Roles and Capabilities

Roles: The admin role is typically defined in the wp_capabilities field within the wp_usermeta table.

Example: The meta_key might be wp_capabilities, and the meta_value could be something like a:1:{s:13:"administrator";b:1;}, indicating that the user has the administrator role.

Capabilities: These are specific permissions associated with a role. Admin users typically have a broad set of capabilities, such as managing options, editing posts, and managing users.

Options Table (wp_options)

Location: Some admin-related configurations and settings are stored in the wp_options table.

Columns:

option_id: A unique identifier for each option.

option_name: The name of the option (e.g., siteurl, admin_email).

option_value: The value for the option.

autoload: Whether the option should be automatically loaded.

Purpose: This table holds various settings for the WordPress site, some of which are directly related to the admin user experience, like the admin_email setting, which determines the email address for administrative notifications.

Accessing and Managing Admin Data

Using PHP and MySQL:

Fetching Admin User: You can query the wp_users table to get details of a specific admin user, such as:

$admin_user = $wpdb->get_row("SELECT * FROM wp_users WHERE ID = 1");

Updating User Data: You can update an admin user's data directly in the database:

$wpdb->update(    'wp_users',    array(        'user_email' => 'newemail@example.com',    ),    array('ID' => 1) );

Checking Roles: You can check a user’s role by querying the wp_usermeta table:

$user_role = $wpdb->get_var("SELECT meta_value FROM wp_usermeta WHERE user_id = 1 AND meta_key = 'wp_capabilities'");

Security Considerations

Password Storage: Passwords are hashed using the wp_hash_password function before being stored in the wp_users table.

Data Handling: When working with admin data, always ensure that you are following best security practices, such as sanitizing inputs and securing database connections.

Custom User Meta

You can store custom metadata for admin users by adding entries to the wp_usermeta table with custom meta_key values.

Multisite Considerations

In a WordPress Multisite installation, the tables for users and usermeta might be shared across sites, and the table names might include a prefix like wp_ or wp_2_ for the second site in the network.

Summary

Admin data in WordPress is primarily stored in the wp_users and wp_usermeta tables. The wp_users table contains core information, while wp_usermeta holds additional metadata, including role and capability information. Accessing and modifying this data using PHP and SQL can be done with the appropriate queries, keeping security in mind.