So what is the relationship of clients to sessions?
Most of our client applications use one session. But, a few use multiple sessions, largely to support threaded access to the database. In our current setup, a session connection may only be used by one thread. So, if you want another thread to access the database you must have it open its own separate session.
When looking at the content of a calculated result in a global temporary table, however, we only ever use one session. And, we have no choice in that as the results are per session.
Well you where saying that having a client create a table would result in it having the clients permissions instead of the servers. Doing the table creation through a function with SECURITY INVOKER would allow you to 'shape' the permissions.
Sorry, I was not completely clear in my first message. By "ownership" I meant ownership of the table data structure in a programming sense, not ownership of the table content. That is, I do not want the client to know that the global temporary table structure have certain fields of certain data types. The table and field definitions belong with the code that manipulates the table, and all that code runs on our server. On the client we just have presentation code that displays the content of the table, nothing that needs to really understand the tables structure.
In our current use, security is handled by restricting who can call the manipulation functions that populate the global temporary tables. It is those functions that look at secure data and produced derived results in the global temporary table. The global temporary table itself need have no special access restrictions. If you cannot call the population services the table never contains any data. The actual table structure itself (field definitions, keys) matters not at all and has no security issues associated with it.
