Debian Codegraph @

mysql-test/suite/service_udf_registration/t/udf_reg_unreg_permissions.test

raw
1 # Check if register/unregister deoends on privileges and root.
2
3 # Save the initial number of concurrent sessions
4 --source include/count_sessions.inc
5
6 # Create users and assign privileges to them.
7 --enable_connect_log
8 CREATE USER test@localhost;
9 CREATE USER test_2@localhost;
10 GRANT CREATE,INSERT,DELETE,DROP,SELECT ON test.* TO test@localhost;
11 GRANT CREATE,INSERT,DELETE,DROP,SELECT ON test.* TO test_2@localhost;
12
13 # Register UDF as root: Must work.
14 INSTALL COMPONENT "file://component_udf_reg_avg_func";
15
16 # Shows the component name.
17 SELECT component_urn from mysql.component;
18
19 # Open session as normal user. Privileges see above.
20 connect(c1,localhost,test,,);
21
22 # No right to access performance_schema.
23 --error ER_TABLEACCESS_DENIED_ERROR
24 SELECT * FROM performance_schema.user_defined_functions
25 WHERE (udf_name LIKE 'myfunc%') or (udf_name LIKE 'avgcost%')
26 ORDER BY udf_name;
27
28 # Check UDFs can be executed by normal user with right privileges..
29 create table t1(sum int, price float(24));
30 insert into t1 values(100, 50.00), (100, 100.00);
31 select avgcost(sum, price) from t1;
32 delete from t1;
33 insert into t1 values(100, 54.33), (200, 199.99);
34 select avgcost(sum, price) from t1;
35 drop table t1;
36
37 # Not allowed actions.
38 --error ER_TABLEACCESS_DENIED_ERROR
39 SELECT component_urn from mysql.component;
40 --error ER_TABLEACCESS_DENIED_ERROR
41 UNINSTALL COMPONENT "file://component_udf_reg_avg_func";
42
43 # Give access to mysql.component to normal user.
44 connection default;
45 GRANT CREATE,INSERT,DELETE,DROP,SELECT ON mysql.component TO test@localhost;
46
47 # Now, components can be shown and components can be unloaded..
48 connection c1;
49 SELECT component_urn from mysql.component;
50 UNINSTALL COMPONENT "file://component_udf_reg_avg_func";
51
52 # Register another UDF by normal user with right privileges.
53 connection default;
54 disconnect c1;
55 INSTALL COMPONENT "file://component_udf_reg_int_func";
56
57 # Try to see UDF_USAGE_COUNT with a count>1. As it is increased only for a short time,
58 # It will be mostly 1.
59 connect(c2,localhost,test_2,,);
60 send SELECT myfunc_int(23);
61
62 connect(c1,localhost,test,,);
63 send SELECT myfunc_int(23);
64
65 # Check of UDF_USAGE_COUNT.
66 connection default;
67 SELECT udf_name,udf_return_type,udf_type,udf_library,udf_usage_count>0
68 FROM performance_schema.user_defined_functions
69 WHERE (udf_name LIKE 'myfunc%') or (udf_name LIKE 'avgcost%')
70 ORDER BY udf_name;
71
72 # execution of sent commands.
73 connection c1;
74 reap;
75
76 connection c2;
77 reap;
78
79 #Clean up.
80 connection default;
81 UNINSTALL COMPONENT "file://component_udf_reg_int_func";
82 disconnect c1;
83 disconnect c2;
84 DROP USER test@localhost;
85 DROP USER test_2@localhost;
86 --disable_connect_log
87 # Wait till all disconnects are completed
88 --source include/wait_until_count_sessions.inc