DevChunks

Remove Obsolete Privileges

Security is an important consideration for any computer systems and MySQL is no exception to this rule. One big security problem that administrators can often overlook is obsolete accounts. These accounts were created for people who no longer use them and are big security holes just waiting to be found.

The user table stores user accounts and it is good practice to check this table on a regular basis and disable or delete any accounts that are no longer required. The DROP USER statement deletes user accounts, but you will need to have global CREATE USER privilege or DELETE privilege for the MySQL database. Since version 5.0.2, DROP USER will remove any privileges associated with the user and delete the account. This example will remove an account:

{code type=php}
DROP USER ‘dave’@’localhost’;
{/code}

Not only do you have to worry about users, but if you delete any other object then any privileges assigned to it with a GRANT statement are not automatically deleted. One way around this is the INFORMATION_SCHEMA tables that can help you to identify obsolete privileges. This query is a great example that finds privileges that reference databases that no longer exist:

{code type=php}
mysql> SELECT d.Host, d.Db, d.User
FROM mysql.db as d
LEFT OUT JOIN INFORMATION_SCHEMA.SCHEMATA AS s
ON s.SCHEMA_NAME LIKE d.Db
WHERE s.SCHEME_NAME IS HULL;
{/code}

Good security practice would be to check user accounts and the INFORMATION_SCHEMA tables on a regular basis, and remove any unwanted user accounts and obsolete objects and privileges.

Share:

Leave a Reply

Your email address will not be published.

More Posts

How to use PHP Type Declarations

PHP has developed into a more strictly typed language in recent years. Type Declarations help with that and are a fairly new addition to PHP,

Iteration

If you have come to JavaScript from a background in Java or C, then you will like the variation on the for loop. It is

Context Object Manipulation

One of the key concepts of JavaScript is the keyword this, which signifies the JavaScript context object. If you write an inner function, you can access

Polymorphic Functions

It is possible to define multiple functions with the same name in JavaScript, and the last one of those that you define will be the