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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
If a View has
INVOKER, everybody and his grandmother can
SELECT from that View. My guess is that you would have to set the
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.
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 !!!
TABLE_SCHEMA= 'db_name' AND
TABLE_TYPE= 'VIEW' ;
This will return # of views in a database.
sakila.* TO 'view'@'%' ;