2016-12-26 15:53 GMT+01:00 Fabien COELHO <coe...@cri.ensmp.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. >
