How to fix ER_ROLE_NOT_GRANTED (3530): Role is not granted to user in MySQL

-- Check current user
SELECT CURRENT_USER(), USER();

-- List all roles granted to current user
SHOW GRANTS FOR CURRENT_USER();

-- Alternative: query the grants table
SELECT * FROM mysql.role_edges WHERE TO_USER = USER();

-- Check which roles are currently active
SELECT CURRENT_ROLE();

-- List all available roles in the database
SELECT * FROM mysql.roles_mapping;

-- Check role grants with details
SELECT 
    GRANTEE,
    GRANTED_ROLE,
    IS_GRANTABLE,
    IS_DEFAULT
FROM information_schema.APPLICABLE_ROLES
WHERE GRANTEE = CURRENT_USER();

-- Grant role to user (requires appropriate privileges)
GRANT 'app_user_role' TO 'username'@'host';

-- Grant with admin option (allows user to grant to others)
GRANT 'app_user_role' TO 'username'@'host' WITH ADMIN OPTION;

-- Make role default (automatically active on login)
GRANT 'app_user_role' TO 'username'@'host';
SET DEFAULT ROLE 'app_user_role' FOR 'username'@'host';

-- Or set all granted roles as default
SET DEFAULT ROLE ALL FOR 'username'@'host';

-- Verify the grant worked
SHOW GRANTS FOR 'username'@'host';

-- Check from different perspective
SELECT * FROM mysql.role_edges 
WHERE FROM_USER = 'username' AND FROM_HOST = 'host';

-- Activate specific role
SET ROLE 'app_user_role';

-- Activate multiple roles
SET ROLE 'role1', 'role2';

-- Activate all granted roles
SET ROLE ALL;

-- Activate all except some roles
SET ROLE ALL EXCEPT 'admin_role';

-- Check current active roles
SELECT CURRENT_ROLE();

-- Verify privileges are now available
SHOW GRANTS FOR CURRENT_USER();

-- Test with a privilege check
SELECT 
    PRIVILEGE_TYPE,
    IS_GRANTABLE
FROM information_schema.USER_PRIVILEGES
WHERE GRANTEE = CURRENT_USER();

-- Set specific role as default for user
SET DEFAULT ROLE 'app_user_role' TO 'username'@'host';

-- Set all granted roles as default
SET DEFAULT ROLE ALL TO 'username'@'host';

-- Set no default roles (manual activation required)
SET DEFAULT ROLE NONE TO 'username'@'host';

-- Check current default role configuration
SELECT 
    DEFAULT_ROLE_USER,
    DEFAULT_ROLE_HOST,
    DEFAULT_ROLE_ROLE
FROM mysql.default_roles
WHERE DEFAULT_ROLE_USER = 'username' AND DEFAULT_ROLE_HOST = 'host';

-- Alternative: check through performance schema
SELECT * FROM performance_schema.default_roles;

-- Apply changes globally (requires appropriate privileges)
FLUSH PRIVILEGES;

-- Check role-to-role grants
SELECT 
    FROM_USER AS parent_role,
    TO_USER AS child_role
FROM mysql.role_edges 
WHERE FROM_USER LIKE '%role%' AND TO_USER LIKE '%role%';

-- Check if role has required parent role grants
WITH RECURSIVE role_tree AS (
    SELECT TO_USER AS child_role, FROM_USER AS parent_role
    FROM mysql.role_edges
    WHERE TO_USER = 'target_role'
    UNION ALL
    SELECT r.TO_USER, r.FROM_USER
    FROM mysql.role_edges r
    INNER JOIN role_tree rt ON r.TO_USER = rt.parent_role
)
SELECT * FROM role_tree;

-- Grant role to another role (creating hierarchy)
GRANT 'parent_role' TO 'child_role';

-- Check effective privileges through role hierarchy
SHOW GRANTS FOR 'username'@'host' USING 'app_user_role';

-- Revoke and regrant if hierarchy is broken
REVOKE 'app_user_role' FROM 'username'@'host';
GRANT 'app_user_role' TO 'username'@'host';
SET DEFAULT ROLE 'app_user_role' FOR 'username'@'host';

-- Check connection attributes (for debugging)
SELECT * FROM performance_schema.session_connect_attrs
WHERE PROCESSLIST_ID = CONNECTION_ID();

-- Check current session role state
SELECT 
    CURRENT_USER(),
    CURRENT_ROLE(),
    @@activate_all_roles_on_login
FROM DUAL;

-- MySQL configuration for automatic role activation
SET GLOBAL activate_all_roles_on_login = ON;

-- Or in my.cnf:
[mysqld]
activate_all_roles_on_login = ON

# Python - Ensure role activation on connection
import mysql.connector

config = {
    'user': 'username',
    'password': 'password',
    'database': 'mydb',
    'host': 'localhost',
    'port': 3306,
    'autocommit': True,
    'connection_timeout': 10
}

connection = mysql.connector.connect(**config)
cursor = connection.cursor()

# Activate role after connection
cursor.execute("SET ROLE 'app_user_role'")
connection.commit()

// PHP - Role activation
$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'username', 'password');
$pdo->exec("SET ROLE 'app_user_role'");