MariaDB file permission error after using Transfer Tool

[billmcollam]

I'm working on transferring accounts from a Centos6 to Centos7 server.
After getting both servers to same CPanel level - I used transfer tool to migrate (first) configurations and then Accounts. The transfers were all clean except for one warning message:

The system failed to grant privileges on the database “acctname_dbname” to the user "acctname_root” because of an error: (XID 6ctc6g) The following is not a valid MySQL privilege: FILE

File privilege is a global privilege - so I wasn't sure why this had been flagged at the database level. And when I tested the account - indeed it had broken. From CLI...logged in as acctname_root:

MariaDB [dbname]> select 'test' into outfile '/home/acctname/backupfolder/tmp/test2.csv';
ERROR 1 (HY000): Can't create/write to file '/home/acctname/backupfolder/tmp/test2.csv' (Errcode: 13 "Permission denied")

So I checked the grants for the acctname... same on the old and new servers....thy both have the Global FILE privilege.

Grants for [email protected]
GRANT FILE, EVENT ON *.* TO `acctname_root`@`localhost` IDENTIFIED BY PASSWORD '*%%%%%%%%%%%%%%%%%%%%%%'

However, I am able to write the same file into /tmp. So I thought perphaps the file permissions had gotten altered during the transfer... but I get this on both the old and new servers:

drwxrwxrwx  2 acctname acctname     4096 Jun  9 21:27 tmp

So it seems the transfer warning did pick something up that wasnt properly transferred - but i cant determine what. Any ideas why I cant write to a file on the new server ?

 

[SamuelM]

Hello @billmcollam

Thank you for contacting cPanel!

FILE is not a supported privilege for cPanel database users. The supported privileges for cPanel database users are:

ALTER	ALTER ROUTINE
CREATE	CREATE ROUTINE
CREATE TEMPORARY TABLES	CREATE VIEW
DELETE	DROP
EVENT	EXECUTE
INDEX	INSERT
LOCK TABLES	REFERENCES
SELECT	SHOW VIEW
TRIGGER	UPDATE

FILE is indeed a global privilege, but cPanel doesn't allow users to assign this privilege, which is the reason the Transfer Tool returned the error message.

With that said, you are free to manually assign the FILE privilege to individual cPanel database users, but we cannot guarantee they will work properly.

In this case however, the Errcode: 13 "Permission denied" error you received is to be expected. I reproduced this error on my test server as well:

MariaDB [samsacco_test]> SELECT * FROM test INTO OUTFILE '/tmp/test.txt';
Query OK, 1 row affected (0.011 sec)

MariaDB [samsacco_test]> SELECT * FROM test INTO OUTFILE '/home/samsaccount/test.txt';
ERROR 1 (HY000): Can't create/write to file '/home/samsaccount/test.txt' (Errcode: 13 "Permission denied")

I was able to use the SELECT ... INTO OUTFILE statement to successfully export a selection to a file in the /tmp directory, but not the cPanel user's home directory. As noted in the MySQL documentation regarding the FILE privilege:

Enables creating new files in any directory where the MySQL server has write access.

The MySQL server does not have write access to cPanel users' home directories. While it would be possible to change the directory permissions to address this, it would not be recommended. I hope this clarifies the matter for you.

Please let us know if you have any questions. Best regards

 

[billmcollam]

No questions. You've explained why the transfer tool threw off the warnings.
I can't figure out why mysql can write to the user directory in production server but not in the new server with identical grants and directory permissions. But I take the point that isnt a cPanel issue. I'll follow it up elsewhere.
Thanks.

 

[hmaddy]

Check mariaDB versions of both servers.