You might encounter SQL0727N errors if you work in an environment that is heavily reliant on access control that is based on user groups.
Symptoms
An SQL0727N error message is returned when querying a view.
SQL0727N An error occurred during implicit system action type "3".
Information returned for the error includes SQLCODE "-142", SQLSTATE " "
and message tokens "". SQLSTATE=56098
Causes
This issue can occur when access control and authorization based on groups is widely used. This error occurs when you query views that are created on virtual tables if certain other conditions also hold true. Specifically, the view creator’s access to the underlying table is based on group membership. When these views are accessed, the user’s access to the view and the view creator’s access to the underlying table that the view is based on is checked. Since that view creator’s access to the underlying table relies on an external identity provider, the view is marked invalid while it is awaiting revalidation.
For more information, see Restrictions on the use of group privileges when executing DDL statements or binding packages8.
Resolving the problem
- If the error is as a result of a view that is marked invalid, a user with the Admin role can
resolve the condition that caused the view to be marked invalid. When you work with access based on
groups, generally an Admin must grant SELECT privileges to the user rather than or in addition to
the group.
You can also grant public access on your objects for all roles or all Data Virtualization users and then restrict access by using data protection rules that are defined on groups. For more information, see Governing virtual data with data protection rules in Data Virtualization.
- When the corrective action is taken, running
SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS()on that object revalidates the object. Subsequent access to the object then succeeds. This procedure requires a Data Virtualization user with an Admin role to run the necessary steps. Execution of the procedure must follow this sequence.
CALL SYSHADOOP.BIG_SQL_SERVICE_MODE('ON');
CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object-type, object_schema, object_name);
CALL SYSHADOOP.BIG_SQL_SERVICE_MODE('OFF');
For more information, see the following resources.