Check if user is drop, Object created by user dropped or exists in MySQL / MariaDB

On checking if you dropped a user in MariaDB / MySQL then its created objects are dropped or not with the user.

Solution: The answer is NO. Its objects exist after the user drop.

Used case as follows: We are showing you by creating a user “user1” with tables and functions. Then we drop the user “user1” in following the used case but our objects are not deleted with the “user1”.

-- Create User
MariaDB [(none)]> create user user1 identified by 'password';
Query OK, 0 rows affected (0.013 sec)

-- Grant privileges to databases
MariaDB [(none)]> grant all privileges on *.* to user1@'%';
Query OK, 0 rows affected (0.011 sec)

-- Connect with user1 user:
C:\WINDOWS\System32>mysql -u user1 -p
Enter password: ********
Welcome to the MariaDB monitor.  Commands end with ; or \g.
MariaDB [(none)]>

-- Go to Test database and create table and procedure
MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table test_purpose (id int);
Query OK, 0 rows affected (0.195 sec)
MariaDB [test]> CREATE or replace FUNCTION `sf_escapechars_test`(p_text varchar(255)) RETURNS varchar(255)
    -> begin
    -> return REPLACE(REPLACE(REPLACE(p_text,"\'","\\\'"),"_","\_"),"%","\%");
    -> end //
Query OK, 0 rows affected (0.023 sec)

-- Drop the user1 from root user:
MariaDB [(none)]> drop user user1;
Query OK, 0 rows affected (0.013 sec)

-- Check the object which you created for user1 : test_purpose table name and --- sf_escapechars_test function name
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| c1             |
| test1          |
| test_purpose   |
+----------------+
8 rows in set (0.003 sec)


-- Check function exists or not after user drop
MariaDB [test]> show create function sf_escapechars_test;
+----------+----------+-----------------+-------+------+--------------------+
| Function | sql_mode | Create Function                                                                                                                                                                         | character_set_client | collation_connection | Database Collation |
+-----------+----------+----------------+------+-----------+--------------+
| sf_escapechars_test | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`user1`@`%` FUNCTION `sf_escapechars_test`(p_text varchar(255)) RETURNS varchar(255) CHARSET latin1
begin
return REPLACE(REPLACE(REPLACE(p_text,"\'","\\\'"),"_","\_"),"%","\%");
end | cp850                | cp850_general_ci     | latin1_swedish_ci  |
+-------+--------+--------------+-----------+---------+----------------------+
1 row in set (0.001 sec)

Note: In the show definer role is user1 it’s not changing but the object exists. So we need to change the definer role to use this procedure. Please use the link to change the definer role: Change definer Role for existing function or procedure