Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jan 23 '12 at 13:50

This question came from our site for professional and enthusiast programmers.

    
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. –  Konerak Jan 23 '12 at 7:18
    
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. –  neeraj Jan 23 '12 at 7:23
add comment

2 Answers

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

share|improve this answer
add comment
  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'@'%' ;
share|improve this answer
    
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 –  neeraj Jan 23 '12 at 7:46
    
oops sakila is a database name here used for example. –  Mahesh Patil Jan 23 '12 at 9:03
    
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. –  Mahesh Patil Jan 23 '12 at 14:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.