Re: proposal: session server side variables

2016-12-26 15:53 GMT+01:00 Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>:

>
> Hello Pavel,
>
> AFAICS they are shared between backends, [...] They constitute a
>>> consistent design.
>>>
>>
>> no
>> http://stackoverflow.com/questions/2383061/scope-of-oracle-
>> package-level-variables
>>
>
> If stackoverflow says so, too bad for me:-) Now I do not understand the
> point of the example I read on Oracle documentation: why having an employee
> count accessed by some functions if it is reset on each new session?
>

please, can send link?

so some better documentation
https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/packages.htm#LNPLS99926

I am sure, so package variables are not shared between sessions/backends -
bacause Oracle uses different mechanism for interprocess communication -
wrote it in Orafce

"When a session references a package item, Oracle Database instantiates the
package for that session. Every session that references a package has its
own instantiation of that package."

>
> So I do retract "it constitute a consistent design". It looks more like a
> PL/SQL confined hack.
>
> Note that Oracle also seems to have session variables with set with DEFINE
> and referenced with &variable.
>
> [...] That could look like:
>>>
>>> SET ROLE Admin;
>>> DECLARE @secure_variable INTEGER RESTRICT; -- only accessible to Admin
>>> SET @secure_variable = 3;
>>>
>>> SET ROLE BasicUser;
>>> SELECT @secure_variable; -- say NULL or error does not exist...
>>>
>>> what will be if BasicUser does DECLARE @secure_variable
>>
>
> Then there would be a distinct global @secure_variable unrelated to the
> previous one, that would be hidden from Admin who would see its own private
> @secure_variable. Maybe "restrict" is not the right word, though, let us
> use "private".
>
> SET ROLE User1;
> -- use @var: does not exist in scope error
> DECLARE @var INTEGER PRIVATE;
> SET @var = 1;
> -- use @var: get 1
>
> SET ROLE User2;
> -- use @var: does not exist in scope error
> DECLARE @var INTEGER PUBLIC;
> SET @var = 2;
> -- use @var; get 2
>
> SET ROLE User1;
> -- use @var: get 1 (private version)
>
> SET ROLE User3;
> -- use @var: get 2 (public version created by User2).
>
>
> There are not any granularity of rights - you cannot to grant access ...
>>
>
> Indeed, at least directly. With the above version you can just control
> whether everybody or only the owner has access.
>
> However with some minimal more effort the owner of a private session
> variable could provide a grantable function for accessing this variable:
> the benefit would be that the function is permanent, i.e. would not need to
> be granted each time the variable is used, it could be done once and for
> all.
>
> CREATE FUNCTION setSecret(INT) SECURITY DEFINER ... AS $$
> DECLARE IF NOT EXISTS @secret TEXT PRIVATE;
> SET @secret = $1;
> $$ LANGUAGE SQL;
>

> CREATE FUNCTION useSecret(TEXT) SECURITY DEFINER TEXT AS $$
> -- would fail if @secret has not been set yet...
> SELECT sha256sum(@secret || ':' || $1);
> $$ LANGUAGE SQL;
>
> CREATE FUNCTION getSecret() RETURNS TEXT SECURITY DEFINER AS $$
> DECLARE IF NOT EXISTS @secret TEXT PRIVATE;
> SELECT @secret;
> $$ LANGUAGE SQL;
>
> -- then
> REVOKE/GRANT ... ON FUNCTION set/use/getSecret(...);
>
> I am sorry, I don't see benefit in your proposal.
>>
>
> The benefit I see is to have MS/MY-SQL/Oracle like light-weight
> (inexpensive, untransactional) session variables and still a minimal access
> control which might be enough for significant use cases.
>
> If more is really needed, consider the function hack, or maybe some
> one-row table with all the power of grant. Ok, the table solution is more
> heavy weight, but then this is also for a special requirement, and it would
> work as well for persistence.
>
> Probably there will be only one agreement, so there are not agreement
>> between us :(
>>
>
> It seems so. I do believe that I am trying to propose a solution which
> take into account your use case as I understand it (you did not confirm nor
> infirm) which is to store securely but not safely some kind of temporary
> data between different function calls with SECURITY DEFINER within the same
> session.
>
> I'm trying to avoid "special-case" medium-weight (i.e. pg_class-based)
> session variables with permissions, which could preclude MY/MS-SQL/Oracle
> like light-weight session variables which are I think interesting in their
> own right.
>

I am sorry, it is not secure. Theoretically it can work if you have
granted order of function calls, but if not?

regards

Pavel

>
> --
> Fabien.
>