[bvo]

hi,

I have a problem to open and/or describe table. The errors I had are


mysql_stgssl>desc ATL1_USER_DETAILS_VW;
ERROR 1356 (HY000): View 'atl1.ATL1_USER_DETAILS_VW' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql_stgssl>select * from ATL1_USER_DETAILS_VW;
ERROR 1356 (HY000): View 'atl1.ATL1_USER_DETAILS_VW' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql_stgssl>

thanks,

bvo

[aschk]

You have your error right there.
Seems fairly obvious.
1) You are trying to describe a view, which is perfectly valid but it's not the real table that you might be after, and will probably only offer information on the select query that generated it.
2) One/all of the underlying tables, table columns or functions that the view uses does not exist
3) The user you are currently trying to use to DESCRIBE the table does not have this privilege.

[bvo]

I'm new with MySQl and do not know how to correct it... Would you please show me how? I am not create this table/view. Someone did and that person is somewhere I do not know.

Is there a way I can select the table name, user name to see who created it? I tried to select from dba_tables like Oracle and couldn't find the dba_tables or user_tables existed... Search on web for data dictionary chart for MySQL and again couldn't find it.

Thanks.

BV

[aschk]

At the cmd line you can execute "SHOW GRANTS" which will show you what permissions you have. (this may not work if you have insufficient permissions).
You can also do "SHOW TABLES" to show all the tables available to yourself.
To get information about the view you can use the information_schema table which is standard throughout SQL i believe. Again, if you don't have sufficient permissions you will be unable to use this database.

USE information_schema;
SELECT * FROM VIEWS WHERE TABLE_NAME = 'ATL1_USER_DETAILS_VW'\G

The above should give you a VIEW_DEFINITION part which will specify the full query that is being executed. If you have missing tables there's nothing you can do about it unless you have a backup of previous data that may contain those tables, in which case you can restore them.
You will also see the DEFINER part which should specify which user created the view and thus which user can also use it. If you don't have VIEW permissions you won't be able to use the view as the current user you are.

[bvo]

Thanks very much for your help