Mysql – Changed a user name in MySQL, but user is still able to login using previous user name

MySQLpermissions

I created a user with user name user_dev. Then created a database sampledb and granted some privileges to this user on this database. Then I decided to edit username and changed it to user_local, and granted same privileges to this new name on same database.
Now both user_dev and user_local are able to login, but user_dev has no access to sampledb and privileges for user_local are correct.

SELECT `user` FROM `mysql`.`user`

does not show user_dev.
My question is, why user_dev is still able to login? Should I take any more steps to prevent this user from loging in?

Best Answer

There are several possibilities:

If you edited the privileges manually, you need to execute:
```
FLUSH PRIVILEGES;
```
To reload the privileges cache on memory (needed to speedup the checks on every query).
If you edited the privileges manually, you may have omitted an entry. Do not trust the SHOW GRANTS FOR, because it may omit the privileges edited directly on tables. Make sure that there are no user_dev not only on the user table, but on all these.
The fact that there is no user_dev user literally does not mean that a user_dev user cannot login. I will explain myself. The user that you submit is compared against the user privileges tables, so user_dev (let's say that is on localhost) matches:
```
'user_dev'@'localhost'
'user%'@'127.0.0.1'
''@'%'
'%'@'localhost'
```
In fact, by default, anonymous users from localhost can login, and all users can read and write on the test database. Run mysql_secure_installation to avoid that.

Related Solutions

PostgreSQL Backup – Restoring Access Privileges After pg_dump

Why are the access privileges on the database itself not restored?

It's a bug, or a design oversight. Though the responder to that report doesn't think so.

pg_dumpall --globals-only doesn't dump rights on the database. Neither does pg_dump as part of the database dump.

So grants on databases only get included in a full pg_dumpall.

I'll make some noise about it by poking this thread.

SQL Server – How to Change Default Security for Database Access

You could create a domain group that you could use to create as login and user on your SQL Server and YourDatabase. After creating the domain group, you could do something like:

USE [master]
GO
CREATE LOGIN [DOMAIN\AllUsersForYourDatabase] FROM WINDOWS
GO
USE [YourDatabase]
GO
CREATE USER [DOMAIN\AllUsersForYourDatabase] 
FOR LOGIN [DOMAIN\AllUsersForYourDatabase] 
GO

You can then make the logins on your domain members of that group. All the logins will inherit the permissions granted to the group that gives them access. (You could include Domain Users in the group if you truly wanted everyone in the domain to have access.)

Of course, you need to grant the needed rights to the YourDatabase user [DOMAIN\AllUsersForYourDatabase] so that everybody in the group gets the same rights.

If you need some users to have additional rights, those can be individually granted. Or you can create another Group that would include only the users with the extra rights. The users will get the aggregate of all rights from all groups for that login.

Best Answer

Related Solutions

PostgreSQL Backup – Restoring Access Privileges After pg_dump

SQL Server – How to Change Default Security for Database Access

Related Question