In SQL Anywhere Network Server Version 18.104.22.1685, Sybase Central won't display the views for a user id that otherwise has permissions to use the views:
GRANT CONNECT TO ADHOC IDENTIFIED BY "SQL"; -- no other permissions other than SELECT granted later GRANT MEMBERSHIP IN GROUP DBA TO ADHOC; GRANT GROUP TO ADHOC; CREATE VIEW alert AS SELECT * FROM rroad_alert; GRANT SELECT ON alert TO ADHOC;
Here's what Sybase Central says...
The set of views could not be read from the database. Permission denied: you do not have permission to execute the procedure "sa_materialized_view_can_be_immediate" [Sybase][ODBC Driver][SQL Anywhere]Permission denied: you do not have permission to execute the procedure "sa_materialized_view_can_be_immediate" SQLCODE: -121 SQLSTATE: 42000 SQL Statement: SELECT T.table_id, T.creator, T.object_id, T.table_name, U.user_name, T.table_type, J."status", D.dbspace_id, D.dbspace_name, V.mv_use_in_optimization, V.mv_refresh_type, IF T.table_type = 2 THEN COALESCE( IF V.mv_refresh_type = 1 THEN '0' ENDIF, IF J."status" = 4 THEN '1' ENDIF, IF V.mv_last_refreshed_at IS NOT NULL THEN '2' ENDIF, IF U.user_name = CURRENT USER OR dbo.sp_has_role( 'ALTER ANY MATERIALIZED VIEW' ) = 1 OR dbo.sp_has_role( 'ALTER ANY OBJECT' ) = 1 THEN (SELECT FIRST SQLStateVal FROM dbo.sa_materialized_view_can_be_immediate( T.table_name, U.user_name) ORDER BY 1) ELSE '-2' ENDIF ) ENDIF, V.mv_last_refreshed_at, V.mv_known_stale_at, T."encrypted", T.pct_free, T.count, R.remarks FROM SYS.SYSTAB T JOIN SYS.SYSUSER U ON U.user_id = T.creator JOIN SYS.SYSOBJECT J ON J.object_id = T.object_id JOIN SYS.SYSVIEW V ON V.view_object_id = T.object_id LEFT OUTER JOIN SYS.SYSDBSPACE D ON D.dbspace_id = T.dbspace_id AND T.table_type = 2 LEFT OUTER JOIN SYS.SYSREMARK R ON R.object_id = T.object_id WHERE U.user_id NOT IN (SELECT creator FROM dbx_filter_list) ORDER BY T.table_name, U.user_name
Here is the workaround...
GRANT EXECUTE ON sa_materialized_view_can_be_immediate TO ADHOC;
asked 23 Jun '14, 13:35