Hello,
In my stored procedure, I am using current_user() while inserting data into a table like the following.
INSERT INTO myTable
select current_user(), list of other columns
from anotherTable
where condition;
When any other user executes this procedure, instead of inserting his/her user_id into the table, it is always my id. It is always the ID for the user who created the SP. How do I fix this issue. I am using 7.1.4.
Thanks.
Hello,
That is happening due to the stored procedure invocation model which is DEFINER by default. In 7.6 you can use CURRENT_USER invocation model instead, but please note that the procedure will run as a current user and it may fail if the user lacks some privileges.
memsql> select current_user();
+----------------+
| current_user() |
+----------------+
| test_user@% |
+----------------+
1 row in set (0.025 sec)
memsql> show create procedure sp;
+-----------+-------------------+---------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection |
+-----------+-------------------+---------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| sp | STRICT_ALL_TABLES | CREATE OR REPLACE PROCEDURE `sp`() RETURNS void AUTHORIZE AS CURRENT_USER AS begin echo select current_user(); end; | utf8 | utf8_general_ci |
+-----------+-------------------+---------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.002 sec)
memsql> call sp();
+----------------+
| current_user() |
+----------------+
| test_user@% |
+----------------+
1 row in set (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Please let me know if this was helpful.
Hello,
Well, can you execute the same SP using another login and post the result. My question was that if I create the SP and someone else executes it. Instead of showing the other user name we are always seeing mine. - Thanks.
@sid2sarkar I already did. This SP is defined by root but invoked as test_user. All you need is to add AUTHORIZE AS CURRENT_USER to the stored procedure (please check show create procedure sp output for more details), however, this option is part of 7.6 release.
Without this special option the procedure is always running as a definer user, so current_user() will return the definer instead.
If that applies only to the current_user() selection I think I can propose a kind of workaround for older SingleStore versions.
Please try this one:
delimiter $
create or replace procedure sp() as
declare
user tinytext = current_user();
begin
echo select user, current_user();
end$
It will give you the following output
+-----------+----------------+
| user | current_user() |
+-----------+----------------+
| test@% | admin@% |
+-----------+----------------+
Where admin is the definer user, test is the user that is currently connected. You can assign a local variable and use it instead of calling current_user() in the stored procedure body.
Please note that this is a workaround. The behavior is changed in 7.6. You can rely on this behavior only for older versions.
Thanks Tigran. Looks like this is not the same in 7.1.4.
Yes, in 7.1.4 it will be different due to the moment the user swap was actually happening, but please bear in mind that this is not going to work anymore after upgrading to 7.6. Use this only as a temporary workaround and after upgrading to 7.6 you will be able to use the new AUTHORIZE AS option instead.
What is the workaround to tag userID for specific rows created by him? So (s)he can only manage his/her rows. So every SQL will have the current_user() as a predicate.
For example .
GET_SP:
SELECT list of columns
FROM TableName
WHERE userID = current_user()
and other predicates;
DELETE_SP:
DELETE FROM TableName
WHERE userID = current_user()
and other predicates;
Any workaround? And what AUTHORIZATION is necessary on 7.6 onwards?
Add
current_user_var tinytext = current_user();
to the declare block and use current_user_var instead of current_user() function.
CREATE OR REPLACE PROCEDURE get_sp() AS
DECLARE
current_user_var tinytext = current_user();
BEGIN
ECHO SELECT
list of columns
FROM
TableName
WHERE
userID = current_user_var;
END
Does this work for you?
In 7.6 you would need to use AUTHORIZE AS CURRENT_USER and ensure that users have proper privileges to SELECT/INSERT/DELETE tables you are referring to in your stored procedures as the procedure will not use definer’s privileges anymore.
Are you saying, I need to also grant select/insert/update/delete permissions beside just giving exec permission on the SP?
Looks good now. Thank you.