Link to home
Create AccountLog in
Avatar of shaf81

asked on

User Privileges / Grants for "Select into Outfile"

Hello All.

Good day. I have a small clarification on MySQL. I have a MySQL DB and I want to use "select * into outfile" to dump data to the disk. I understand that the user account which issues the statement should have "FILE" privileges granted to it. Before I go ahead and grant privileges, I just checked the grants for the specific user and I also checked the entry in the mysql.user table. below is what i saw: (btw, the username is bililng).

mysql> show grants for billing@x.x.x.x;
| Grants for billing@                                                                                   |
| GRANT USAGE ON *.* TO 'billing'@'x.x.x.x' IDENTIFIED BY PASSWORD '*13883BDDBE566EAGG63642E293303116521A' |
| GRANT ALL PRIVILEGES ON `billingDB`.* TO 'billing'@'x.x.x.x'                                                   |
| GRANT ALL PRIVILEGES ON `backup`.* TO 'billing'@'x.x.x.x'                                              |

mysql> select * from user where user='billing'\G
*************************** 1. row ***************************
                 Host: x.x.x.x
                 User: billing
             Password: *13883BDDBE566EAGG63642E293303116521A
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
          Reload_priv: N
        Shutdown_priv: N
         Process_priv: N
            File_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
         Show_db_priv: N
           Super_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
         Execute_priv: N
      Repl_slave_priv: N
     Repl_client_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
     Create_user_priv: N
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0

On the mysql.user table it shows that the 'billing@x.x.x.x' user doesn't have any privileges, however on the SHOW GRANTS command output, I see ALL PRIVILEGES on billingDB.

1) I understand that the mysql.user table doesn't store all privilege info as the privileges can be granule to DB's and Columns. So where is the fine granule privileges stored for any given user?

2) as the SHOW GRANTS displays ALL PRIVILEGES assigned to this user, however the select into outfile fails (Succeeds on root). Doesn't the FILE PRIVILEGE come under ALL PRIVILEGE? Or is there anything that I'm missing here? What needs to be done to get the SELECT INTO OUTFILE Working for this user for billingDB?

Avatar of Nellios
Flag of Greece image

1) You have to check a few more tables: db, tables_priv,columns_priv. It seems that the user you are checking has no privs server wide but db wide.
2) I guess that you have to make sure that user also has select priv = 'Y' for the db you wish.
Avatar of shaf81

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of shaf81



The FILE grant did in-fact work.

Findings- mysql.user table contains global privileges.
FILE is a Global privilege and cannot be assigned at a specific DB level
table_priv, colum_priv, db etc contain more granular privilege info for tables, columns and db's respectively.