Oracle Database Express Edition (XE)

Is this RDBMS bug? Because, the prompted expansion is just a dbms_utility.expand_sql_text call. And it is always ctrl-Z away if unsatisfied with the amended statement.

If SQL Developer is merely calling dbms_utility.expand_sql_text that explains the behaviour.

I don't think it's an RDBMS bug, dbms_utility.expand_sql_text seems to do what it says on the tin including the VPD policy, according to the docs (https://docs.oracle.com/database/121/ARPLS/d_util.htm#ARPLS73240 ):

"The resulting query text only contains references to underlying tables and is semantically equivalent with some caveats:
If there are invoker rights functions called from any of the views, they may be called as a different user in the resulting query text if the view owner is different from the user who will eventually compile/run the expanded SQL text.
The VPD policy expands differently if there is a function supplied to generate the dynamic WHERE clause. This function would return differently, for example, if the userid caused the expansion to be different."

I think it's more likely that SQL Developer is using it inappropriately, or else this feature is not quite as useful as it might appear. When I hovered over the select statement in SQL Developer and it offered to "expand the SQL statement" the developer might expect to get a suggestion that actually makes sense for development purposes

Ideally, the dbms_utility.expand_sql_text function would accept parameters to customise its behaviour (e.g. to suppress the expansion of VPD policies) and this would then become a useful feature of SQL Developer.

Thanks

Personally, I think it is wonderful that SQL Developer shows you exactly what the database is doing with the SQL statement you send to it.  I imagine that will help avoid confusion in many cases.  

Part of the feature is that you can click on the expansion and SQL Developer copies it into your code editor - which is almost always exactly what you don't want to do.

To each his own.  Thanks for bringing up this interesting topic.  I haven't used the feature (in existence for a long while: https://www.thatjeffsmith.com/archive/2014/12/sql-developer-and-a-12c-magic-trick/ ), except accidentally when mousing over statements in an editor.

My preference would be a more fully developed feature, probably explicitly available in a context menu, with an option to control whether the expansion continues through the VPD layer. But as Vadim notes, that would require changes on the RDBMS side of the house to enhance dbms_utility.expand_sql_text

Jeffrey Kemp wrote:
Part of the feature is that you can click on the expansion and SQL Developer copies it into your code editor - which is almost always exactly what you don't want to do.

It seems like the only time I would want to replace the SQL is if I'm want to expand only the SELECT * section into SELECT <list of columns>

Beyond that, I consider it a potential security risk to allow anyone to see the applied VPD/RAS rules.

Not only should the caller specifically tell EXPAND_SQL_TEXT that he/she wants to see those rule, the DBA/Security person should tell the database "yes, that person is allowed to see those rules".

But, those are my thoughts and opinions.

MK