How to grant super privilege to the user?

  • I have created a user and given privileges to the user1.

    `grant all privileges on db1.* to [email protected]'%' with grant option;
    

    Am using mysql workbench to import dumps to my database. While importing dumps to database db1,error occurs stating that

    ERROR 1227 (42000) at line 49: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
    

    In that dumps all tables are imported successfully but the error occurs while importing routines to the database. Is there anything wrong with the privilege, I have given to the user1. Please advice.

  • In a politically correct sense, what you just asked for is impossible. Why ?

    The SUPER privilege is a global privilege, not a database level privilege.

    When you created the user with

    grant all privileges on db1.* to [email protected]'%' with grant option;
    

    you populated the table mysql.user with user=user1 and host='%'. All other columns (global privileges) were defaulted to 'N'. One of those columns is Super_priv. Here is the table:

    mysql> desc mysql.user;
    +------------------------+-----------------------------------+------+-----+---------+-------+
    | Field                  | Type                              | Null | Key | Default | Extra |
    +------------------------+-----------------------------------+------+-----+---------+-------+
    | Host                   | char(60)                          | NO   | PRI |         |       |
    | User                   | char(16)                          | NO   | PRI |         |       |
    | Password               | char(41)                          | NO   |     |         |       |
    | Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
    | Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
    | Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
    | Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
    | Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
    | Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
    | Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
    | Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
    | Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
    | File_priv              | enum('N','Y')                     | NO   |     | N       |       |
    | Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
    | References_priv        | enum('N','Y')                     | NO   |     | N       |       |
    | Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
    | Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
    | Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
    | Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
    | Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
    | Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
    | Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
    | Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
    | Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
    | Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
    | Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
    | Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
    | Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
    | Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
    | Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
    | Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
    | Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
    | ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
    | ssl_cipher             | blob                              | NO   |     | NULL    |       |
    | x509_issuer            | blob                              | NO   |     | NULL    |       |
    | x509_subject           | blob                              | NO   |     | NULL    |       |
    | max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
    | max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
    | max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
    | max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
    | plugin                 | char(64)                          | YES  |     |         |       |
    | authentication_string  | text                              | YES  |     | NULL    |       |
    | password_expired       | enum('N','Y')                     | NO   |     | N       |       |
    +------------------------+-----------------------------------+------+-----+---------+-------+
    43 rows in set (0.00 sec)
    
    mysql>
    

    Super_priv appears right after Show_db_priv.

    The database level privileges got populated into mysql.db. Here is it:

    mysql> desc mysql.db;
    +-----------------------+---------------+------+-----+---------+-------+
    | Field                 | Type          | Null | Key | Default | Extra |
    +-----------------------+---------------+------+-----+---------+-------+
    | Host                  | char(60)      | NO   | PRI |         |       |
    | Db                    | char(64)      | NO   | PRI |         |       |
    | User                  | char(16)      | NO   | PRI |         |       |
    | Select_priv           | enum('N','Y') | NO   |     | N       |       |
    | Insert_priv           | enum('N','Y') | NO   |     | N       |       |
    | Update_priv           | enum('N','Y') | NO   |     | N       |       |
    | Delete_priv           | enum('N','Y') | NO   |     | N       |       |
    | Create_priv           | enum('N','Y') | NO   |     | N       |       |
    | Drop_priv             | enum('N','Y') | NO   |     | N       |       |
    | Grant_priv            | enum('N','Y') | NO   |     | N       |       |
    | References_priv       | enum('N','Y') | NO   |     | N       |       |
    | Index_priv            | enum('N','Y') | NO   |     | N       |       |
    | Alter_priv            | enum('N','Y') | NO   |     | N       |       |
    | Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
    | Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
    | Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
    | Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
    | Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
    | Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
    | Execute_priv          | enum('N','Y') | NO   |     | N       |       |
    | Event_priv            | enum('N','Y') | NO   |     | N       |       |
    | Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
    +-----------------------+---------------+------+-----+---------+-------+
    22 rows in set (0.00 sec)
    
    mysql>
    

    Notice that Super_priv does not exist in mysql.db.

    To visualize this in pure SQL terms, login as user1 and run SHOW GRANTS; The output will have two lines:

    There is a hack you can try but I normally would not recommend it.

    STEP 01) Login to mysql as [email protected] (should have all privs)

    STEP 02) Run this query

    UPDATE mysql.user SET Super_Priv='Y' WHERE user='user1' AND host='%';
    

    STEP 03) Run this query

    FLUSH PRIVILEGES;
    

    That theoretically should work. Then, user1 may work (I make no guarantees).

    UPDATE 2014-12-19 15:24 EST

    Metafaniel just asked

    Great explanation, thanks. However if you do not reccomend that way to solve the problem, then what other way is the best one to gran a user this Super_priv?? Thanks! – Metafaniel

    Since a user with only DB access cannot have SUPER, the only thing one can do is change the DEFINER manually in the dump. The basic idea would be to mysqldump the routines alone to a text file. Then, edit the definer to [email protected]'%'. Then, you should be able to reload.

    Same Thing for Views

    using mariadb and the update statement "ERROR 1348 (HY000): Column 'Super_priv' is not updatable'

    once i give grant access i still see all default value ( same like *_priv columns)

  • I had the exact same problem. What I did was:

    GRANT SUPER ON *.* TO [email protected]
    

    And problem solved.

    CAVEAT: Is there any danger in granting SUPER privileges to a user?

  • "Access denied" error during the import process might be due to the privileges mismatch for the DEFINERs between the database exported and the database going to be imported. I faced the same situation and below command works well for me to import the database successfully.

    cat db1.sql | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -u user1 -p db1
    

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM