Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an existing table and I want to update host part in privileges using Grant command

mysql> show grants
    -> ;
+------------------------------------------------------------------------------------------------------------+
| Grants for ssc@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ssc'@'localhost' IDENTIFIED BY PASSWORD '*ABCDEFABCDEF' |
| GRANT ALL PRIVILEGES ON `ssc`.* TO 'ssc'@'localhost' WITH GRANT OPTION                                     |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

How do I change 'localhost' to the *.*

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

How do I change 'localhost' to the *.*

That part of the question is not clear, since 'localhost' refers to a hostname, while '*.*' refers to all databases, all objects.

If you are wanting to give access to the username 'ssc' from any IP or host, not just 'localhost' -- that's what it sounds like -- then there are two different approaches.

First, though, you need to understand that an individual "user" in MySQL is the combination of username and host specification -- not just the username. A user called 'ssc'@'%' would be a user with username 'ssc' who could log in from any host ('%' is the host wildcard, not '*'); however, that's not really the same "user" as 'ssc'@'localhost' as far as MySQL is concerned.

Technically, you could just update the grant tables, changing entries for that user to the wildcard hostname '%'. These are in the mysql schema, tables named user, db, tables_priv, columns_priv, procs_priv, and (in newer versions) proxies_priv.

These can be updated with standard queries (e.g., UPDATE mysql.user SET host = '%' where host = 'localhost' AND user = 'ssc', and a similar query for the 'db' table).

After you update them you have to issue the FLUSH PRIVILEGES; statement to reload the already-in-memory privilege structures from the tables.

The other approach, of course, is the DROP USER to remove the old user and GRANT the desired privileges to the new user. This can be accomplished even if you don't know the user's password, because the IDENTIFIED BY {password} clause in the GRANT statement will also accept an already-encrypted password (which you can get from the mysql.user table).

The potential problem you'll have with both of these approaches is the way some objects have a DEFINER user, that establishes the privileges associated with the object, no matter who is actually using the object at the time. Stored programs (triggers, procedures, functions, and events) and views are all affected by this. Base tables and databases themselves are not.

Since 'ssc'@'localhost' and 'ssc'@'%' (or another other host spec) are not really "the same user," it follows that making the changes using either method above means you will have to identify and then redefine any objects the previous user has defined in the database, using a new DEFINER declaration (or allowing the definer to be set implicitly) ... or they will not work, once you remove or redefine the user.

As an example, here's a view that was created by user 'hr2'@'foo'. I'll drop that user, then examine the view.

mysql> drop user hr2@foo;
Query OK, 0 rows affected (0.00 sec)

mysql> show create view hr2v;
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                                                                                                            | character_set_client | collation_connection |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| hr2v | CREATE ALGORITHM=UNDEFINED DEFINER=`hr2`@`foo` SQL SECURITY DEFINER VIEW `hr2v` AS select `test`.`t1`.`id` AS `id` from `t1` | utf8                 | utf8_general_ci      |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------+
| Level | Code | Message                                                         |
+-------+------+-----------------------------------------------------------------+
| Note  | 1449 | The user specified as a definer ('hr2'@'foo') does not exist |
+-------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

Note that I am logged in with SUPER privilege, but now that I've dropped the user who was DEFINER of the view, I am prevented from opening that view.

mysql> select * from hr2v;
ERROR 1449 (HY000): The user specified as a definer ('hr2'@'foo') does not exist

Aside: if you end up in a circumstance where this comes up, the first fix is to simply re-create the user. Unlike in some systems where a user has a hidden identifier beyond their visible username, MySQL treats the recreated user as identical, since it's just matching strings.

But you can prevent this problem by verifying that no objects are associated with the user you'll be removing, or by finding them and fixing them first.

SELECT * FROM information_schema.views WHERE definer ='user@host';
SELECT * FROM information_schema.triggers WHERE definer ='user@host';
SELECT * FROM information_schema.routines WHERE definer ='user@host'; # stored procs and functions
SELECT * FROM information_schema.events WHERE definer ='user@host';

There are two important notes here, also. First, note that the quoting is different. Instead of 'user'@'host' these are expressed as 'user@host' (or 'user@%'). Also, unlike the grant tables, which really are tables, the information_schema "tables" are not tables at all, but rather representations of server internals, queryable via the SQL interface... and as such, they cannot be updated. Each object you identify has to be dropped and redefined so its security context will be associated with a valid user, at which time the DEFINER shown in the respective table in information_schema will show the new values also.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.