MySQL Views Authorization

Question

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?

Answer 1

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 !!!

Answer 2

1. SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db_name' AND TABLE_TYPE = 'VIEW' ;

This will return # of views in a database.

1. You should not give DROP permission to other users and have CREATE VIEW and DROP user permission to user which can DROP a view like this: CREATE USER 'view'@'%' IDENTIFIED BY 'view'; GRANT CREATE VIEW, DROP ON sakila.* TO 'view'@'%' ;