Get CURRENT_USER in MySQL trigger returns incorrect value

Question

I have an old application ( no source code available ) that connects to a productive MySQL database. Due to business reasons we have to limit the access to the database. For this reason I have written a MySQL Trigger that will limit the users who have access to the database based on a ID.

The users authenticate against the database through the application with their own ID and their private passwords.

The problem appears inside the trigger were I have a SELECT...WHERE clause and the WHERE clause equals to: SUBSTRING_INDEX(CURRENT_USER(), @, 1)

Basically each time a hit is done on a certain database / table by the user in the application I would like to capture that User ( which is also shows up in the Processes of MySQL server ) and based on the capture execute a SELECT statement which will return a UNIQUE ID for that given user. Based on that ID a IF check will be executed and access for saving data will be granted or revoked.

The problem is, even if I log in the application with User X the CURRENT_USER() function of MySQL somehow captures my domain user no matter what I do. It should capture the domain user that executes that trigger.

Full trigger:

DROP TRIGGER dbname.disable_order_insert_trigger;

DELIMITER $
use dbname$

CREATE TRIGGER disable_order_insert_trigger BEFORE INSERT ON 
dbname.dbtable

FOR EACH ROW
BEGIN
    DECLARE compCode INT unsigned DEFAULT 0;        
    SET @compCode = 1;

    SELECT COMPCODE
    INTO @compCode
    FROM dbname.user_info_table
    WHERE dbname.user_info_table.USERID = SUBSTRING_INDEX(CURRENT_USER(), "@", 1) 
    AND dbname.user_info_table.COMPCODE = 1172 LIMIT 1; 
    SELECT CURRENT_USER() INTO OUTFILE 'C:\\temp\\compCode.txt';

IF @compCode = 1172 THEN 
    CALL something;
END IF;

END;
$
DELIMITER ; 

SHOW TRIGGERS;

My colleague logged into the MySQL Database through the application using he's PC / username and still in the OUTFILE my username gets written. Shouldn't CURRENT_USER() capture his username ?

The version of MySQL Database is: 5.1.19-beta-community-nt-debug. I have also tried to leave the Definer trigger property empty, still only my username gets captured and written to the file.

Answer 1

Like wchiquto suggested, use USER() rather than CURRENT_USER().

In a sp, CURRENT_USER() presents the name of the sp's definer.