MySQL Views Authorization

authorizationMySQLview

Questions:

  • Is there a way, using SQL, to identify how many views are in the database per user?

  • How can you authorize/deny users access to delete views created by another user?

Best Answer

Here is the definition of information_schema.views:

mysql> show create table information_schema.views\G
*************************** 1. row ***************************
       Table: VIEWS
Create Table: CREATE TEMPORARY TABLE `VIEWS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VIEW_DEFINITION` longtext NOT NULL,
  `CHECK_OPTION` varchar(8) NOT NULL DEFAULT '',
  `IS_UPDATABLE` varchar(3) NOT NULL DEFAULT '',
  `DEFINER` varchar(77) NOT NULL DEFAULT '',
  `SECURITY_TYPE` varchar(7) NOT NULL DEFAULT '',
  `CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '',
  `COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

Here is how you can count the views per database per user

SELECT COUNT(1) ViewCount,table_schema,definer
FROM information_schema.views
GROUP BY table_schema,definer;

I am not that sure about access/denial of views. In the information_schema.views table, there is field called SECURITY_TYPE (which have values DEFINER and INVOKER).

If a View has SECURITY_TYPE as INVOKER, everybody and his grandmother can SELECT from that View. My guess is that you would have to set the SECURITY_TYPE to DEFINER to restrict everyone from running a SELECT from that View. Of course any user with SUPER privilege and the definer can a SELECT on that View.

Changing the SECURITY_TYPE is a little painful.

You can mysqldump all views. Here is a post I made back on July 26, 2011 : Modify DEFINER on Many Views. @DTest had an even more concise answer using ALTER VIEW.

Edit the output file in vi or some other editor to change the SECURITY TYPE of any View you wish. Then, jus reimport the view from that text file.

Give it a Try !!!

Related Question