7/31/2023 0 Comments Error 1142 mysql create view![]() | portaluser | % | jsportal | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view | | portaluser | localhost | jsportal | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view | Mysql> select user,host,db,table_priv from tables_priv Your MySQL connection id is 13 to server version: 5.0.19-Debian_3-log You can turn off this feature to get a quicker startup with -A Reading table information for completion of table and column names Mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i486) using readline mysql -u root -p mysql Below is my tables_priv and the command with the resulting 1142 error. I've given 'grant all' to my user account, but still cannot create a table. I'm having problems setting up user access to a database (jsportal), and cant figure out what i've missed. ![]() VIEW_DEFINITION: select `db1`.`tb1`.`id` AS `id` from `db1`.I'm running mysql 5.0.19 on current Ubuntu Dapper. Mysql> SELECT * FROM information_schema.views WHERE TABLE_NAME='vtb1'\G Your MySQL connection id is 3 to server version: 5.0.19-debugĮRROR 1142 (42000): SHOW VIEW command denied to user for table 'vtb1' Mysql> grant select on db1.* to identified by 'user' Mysql> create view vtb1 as select * from tb1 Your MySQL connection id is 2 to server version: 5.0.19-debug MySQL Verification Team bin/mysqladmin -uroot create bin/mysql -uroot db1 Any user can still see the existence and other properties of the view Is intended to hide the definition for unauthorized users. It seems to me that thee expression for the VIEW_DEFINITION column: ( SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME WHERE ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ) IN = ( T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME ) WHERE ( V.TABLE_CATALOG, V.TABLE_SCHEMA, V.TABLE_NAME ) SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, Just look at the definition of the VIEWS information_shema view (ISO/IEC 9075-11:2003 (E) 5.76 VIEWS view, page 98): Roland Bouman Actually, as far as I can see, ANSI IEC 9075:2003 allows the user to see the view, but blanks out the VIEW_DEFINITION unless the current user is (either directly or indirectly) owner of the view. MySQL should only show information_schema.views (or at least the contents) to users that have the needed privilleges. * SELECT * FROM information_schema.views WHERE View='test' => shows what SHOW CREATE hides. * SHOW CREATE VIEW mydb.test => results in an error * Grant the new user only the SELECT privillege on the VIEW `test` * Create (as some kind of administrator) a VIEW let's sayĬREATE VIEW test AS SELECT customers.status AS st, ername AS user, passwords.md5 AS password FROM customers INNER JOIN users ON customers.id=users.cid INNER JOIN passwords ON passwords.uid=users.id ![]() This issue was also posted in the mysql-forums where I was adviced to open this bug report. I think that this information should not be given as long as the user does not have the SHOW CREATE (Show_view_priv) permission. I am able to forbid a user to do so and SHOW CREATE VIEW results in an error message.īut (that's the bug, imho) the user is still able to view the complete command used to create the view by issuing Therefore, a privillege exists, called 'Show_view_priv'. If I create a VIEW that produces a virtual table as a combination of several other tables, then it may be desired that the user that accesses the view should only be able to use the given VIEW but should not be able to see how it works internally (so he should not be able to see a wide range of the database layout).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |