MySQL Query for counting the number of databases a user owns?

Question

Is there a type request

pseudo code:

SELECT databases FROM mysql.databases WHERE owner = 'myUser'

or any request that would do the job?

Answer 1

 SELECT COUNT(*) FROM information_schema.SCHEMATA; 

(runs as user in question)

 SELECT count(*) FROM ( SELECT DISTINCT TABLE_SCHEMA FROM information_schema.SCHEMA_PRIVILEGES WHERE GRANTEE LIKE("'USERNAME'%") GROUP BY TABLE_SCHEMA ) AS baseview; 

(Run as root)

Warning. In MySQL, there is no such thing as an “Owner” for a database; in the above queries, information about the databases to which the user has access will be displayed.

Answer 2

As far as I can tell, in MySQL there is no concept of "Owner" of a database or its objects, as in MS Access and MS SQL Server. I assume this is due to the lack of an owner field anywhere in the mysql system tables. (Http://www.wideman-one.com/gw/tech/mysql/perms/index.htm)

Answer 3

You can calculate how many databases are associated with the user in the mysql database and db table. This is the closest I can think of to “own” a database by a user.

 SELECT count(DISTINCT Db) FROM db WHERE User = 'someuser'; 

Answer 4

Using

 SHOW DATABASES 

You can only see databases for which you have some privilege, if you do not have the global SHOW DATABASES privilege .

Read the SHOW DATABASES Syntax for a better understanding of what you can achieve.

Answer 5

Run sql and then you will see the owner of the table user.

sql select * from information_schema. SCHEMA_PRIVILEGES where TABLE_SCHEMA='myUser' select * from information_schema. SCHEMA_PRIVILEGES where TABLE_SCHEMA='myUser'

Details about information_schema.SCHEMATA