Then, edit the definer to Then, you should be able to reload. The basic idea would be to mysqldump the routines alone to a text file. Since a user with only DB access cannot have SUPER, the only thing one can do is change the DEFINER manually in the dump. 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 Then, user1 may work (I make no guarantees). STEP 03) Run this query FLUSH PRIVILEGES STEP 02) Run this query UPDATE er SET Super_Priv='Y' WHERE user='user1' AND host='%' STEP 01) Login to mysql as (should have all privs) There is a hack you can try but I normally would not recommend it. To visualize this in pure SQL terms, login as user1 and run SHOW GRANTS The output will have two lines: Notice that Super_priv does not exist in mysql.db. The database level privileges got populated into mysql.db. Super_priv appears right after Show_db_priv. | password_expired | enum('N','Y') | NO | | N | | | authentication_string | text | YES | | NULL | | | max_user_connections | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_questions | int(11) unsigned | NO | | 0 | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | No connections exist for first-time users. Existing connections are shown when you click the MySQL Connections view from the sidebar. To create a new connection, follow these steps: Launch MySQL Workbench to open the home screen. | Field | Type | Null | Key | Default | Extra | An instance of MySQL server must be installed, started, and accessible to MySQL Workbench before you begin. Type the password for the MySQL root user, check the Save password in keychain, and click the OK button. All other columns (global privileges) were defaulted to 'N'. MySQL Workbench will display a dialog window. You populated the table er with user='user1' and host='%'. When you created the user with grant all privileges on db1.* to with grant option The SUPER privilege is a global privilege, not a database level privilege. The following syntax is used to create a user in the database server.In a politically correct sense, what you just asked for is impossible. In that case, you will create a non-root user and grant them specific privileges to access and modify the database. But, sometimes, you want to give the database access to others without granting them full control. When the MySQL server installation completes, it has a ROOT user account only to access and manage the databases. But if you use, IF NOT EXISTS clause, the statement gives a warning for each named user that already exists instead of an error message. When you create a user that already exists, it gives an error. If you want to use the Create User, it is required to have a global privilege of Create User statement or the INSERT privilege for the MySQL system schema. It also enables us to control the accounts that should be initially locked or unlocked. It provides authentication, SSL/TLS, resource-limit, role, and password management properties for the new accounts. The MySQL Create User statement allows us to create a new user account in the database server. It is essential to create a user in MySQL for accessing and managing the databases. The MySQL user is a record in the USER table of the MySQL server that contains the login information, account privileges, and the host information for MySQL account.
0 Comments
Leave a Reply. |