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?

• Konerak: 1. You should check the database information_schema. 2. No, a user has DROP permissions or not. You can store permissions on a table-based level though.
• neeraj: 1. I want to list the view from information_schema on user basis. Is it possible to get it through query. Can you provide such query 2. Okay.

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'@'%' ;

• neeraj: 1. Mahesh, somehow i want to list the view on the basis of users, but when i use the command SELECT * FROM information_schema.TABLES WHERE TABLE_TYPE LIKE 'VIEW' AND TABLE_SCHEMA LIKE 'database_name'; then i can see that information schema does not contain the name of the definer (user) who actually created that view. Eventually i need to use the command "USE information_schema;" and then i can get the list of the views on the basis of user like "SELECT * FROM views WHERE DEFINER LIKE "delta@localhost";" 2. PLEASE EXPLAIN WHAT IS SAKILA.* IN YOUR EXAMPLE
• Mahesh Patil: oops sakila is a database name here used for example.
• Mahesh Patil: This is exact query you are looking for i guess, SELECT COUNT(*), TABLE_NAME, DEFINER FROM information_schema.views WHERE TABLE_SCHEMA = 'database_name' GROUP BY Definer ; Note: You do not have to use USE INFORMATION_SCHEMA; everytime instead you can use fully qualified queries.