Subject Re: [firebird-support] Any 'run as' in SQL/PSQL in FB3 ?
Author hamacker
Post date 2017-08-31T19:36:17Z
Now I remove 'AS ' and sintaxe error message is gone, thanks.

But another problem, my role 'perfil_vendas' have all access to all objects and 'perfil_vendas', but not for 'for select...' :(
when I try to execute my sample code, IBExpert says:

'no permition for SELECT access TABLE <TABLE_NAME> at block line....'

as expected. 

So, to my sample run file, all block code need to be a unique execute statement... and you know it´s a mess.

Better I think right and start over.

Thanks a lot.


Code sample:
EXECUTE BLOCK
returns (result_value Integer)
AS
declare variable lid_cv bigint=3;
declare variable lcoditem varchar(30)='(C120P2AC)';
declare variable lvl_base NUMERIC(18,2)=            100.00;
declare variable lid_cv_item bigint;
declare variable lid_cv_itens_sub1 bigint;
declare variable lid_cv_itens_sub2 bigint;
declare variable lcvi_coditem varchar(30);
declare variable lsub1_coditem varchar(30);
declare variable lsub2_coditem varchar(30);
declare variable lsql_update_cv_itens varchar(4096);
declare variable lsql_update_cv_itens_sub1 varchar(4096);
declare variable lsql_update_cv_itens_sub2 varchar(4096);
begin
  lsql_update_cv_itens='update cv_itens set vl_base=? where (id_cv_item=?);';
  lsql_update_cv_itens_sub1='update cv_itens_sub1 set vl_base=? where (id_cv_itens_sub1=?);';
  lsql_update_cv_itens_sub2='update cv_itens_sub2 set vl_base=? where (id_cv_itens_sub2=?);';
  result_value=0;
  for select id_cv_item, coditem from cv_itens -- no role for this select
  where id_cv=:lid_cv 
  into :lid_cv_item, :lcvi_coditem do
  begin
    if (:lcvi_coditem=:lcoditem) then 
    begin
      execute statement (lsql_update_cv_itens)
        (:lvl_base, :lid_cv_item)
        role 'perfil_orcamentos'; 
      result_value=(result_value+1);
    end
    for select id_cv_itens_sub1, coditem from cv_itens_sub1 -- no role for this select
    where id_cv_item=:lid_cv_item 
    into :lid_cv_itens_sub1, :lsub1_coditem  do
    begin
      if (:lsub1_coditem=:lcoditem) then 
      begin
        execute statement (lsql_update_cv_itens_sub1)
          (:lvl_base, :lid_cv_itens_sub1)
          role 'perfil_orcamentos'; 
        result_value=(result_value+1);
      end
      for select id_cv_itens_sub2, coditem from cv_itens_sub2  --- no role for this select
      where id_cv_itens_sub1=:lid_cv_itens_sub1
      into :lid_cv_itens_sub2, :lsub2_coditem do
      begin
        if (:lsub2_coditem=:lcoditem) then 
        begin
          execute statement (lsql_update_cv_itens_sub2)
            (:lvl_base, :lid_cv_itens_sub2)
            role 'perfil_vendas';
          result_value=(result_value+1);
        end
      end
    end
  end
  suspend;
end

2017-08-31 3:58 GMT-03:00 Svein Erling Tysvær setysvar@... [firebird-support] <firebird-support@yahoogroups.com>:
 

Did you remember to also remove AS? As far as I can see from the syntax description, AS should be used before USER, but not before ROLE, so try:

        execute statement (lsql_update_cv_itens_sub1)
          (:lvl_base, :lid_cv_itens_sub1)
          role 'perfil_vendas';

HTH,
Set


2017-08-30 23:08 GMT+02:00 hamacker sirhamacker@... [firebird-support] <firebird-support@yahoogroups. com>:


Something wrong in my test because 'sintax error' when I try role name without username/password.
Can you help me?


EXECUTE BLOCK
returns (result_value Integer)
AS
declare variable lid_cv bigint=3;
declare variable lcoditem varchar(30)='(C120P2AC)';
declare variable lvl_base NUMERIC(18,2)=            100.00;
declare variable lid_cv_item bigint;
declare variable lid_cv_itens_sub1 bigint;
declare variable lid_cv_itens_sub2 bigint;
declare variable lcvi_coditem varchar(30);
declare variable lsub1_coditem varchar(30);
declare variable lsub2_coditem varchar(30);
declare variable lsql_update_cv_itens varchar(4096);
declare variable lsql_update_cv_itens_sub1 varchar(4096);
declare variable lsql_update_cv_itens_sub2 varchar(4096);
begin
  lsql_update_cv_itens='update cv_itens set vl_base=? where (id_cv_item=?);';
  lsql_update_cv_itens_sub1='upd ate cv_itens_sub1 set vl_base=? where (id_cv_itens_sub1=?);';
  lsql_update_cv_itens_sub2='upd ate cv_itens_sub2 set vl_base=? where (id_cv_itens_sub2=?);';
  result_value=0;
  for select id_cv_item, coditem from cv_itens
  where id_cv=:lid_cv 
  into :lid_cv_item, :lcvi_coditem do
  begin
    if (:lcvi_coditem=:lcoditem) then 
    begin
      execute statement (lsql_update_cv_itens)
        (:lvl_base, :lid_cv_item)
        as user 'SYSDBA' password 'masterkey' role 'perfil_vendas'; -- sintaxe error when remove user and password
      result_value=(result_value+1);
    end
    for select id_cv_itens_sub1, coditem from cv_itens_sub1
    where id_cv_item=:lid_cv_item 
    into :lid_cv_itens_sub1, :lsub1_coditem  do
    begin
      if (:lsub1_coditem=:lcoditem) then 
      begin
        execute statement (lsql_update_cv_itens_sub1)
          (:lvl_base, :lid_cv_itens_sub1)
          as user 'SYSDBA' password 'masterkey' role 'perfil_vendas'; -- sintaxe error when remove user and password
        result_value=(result_value+1);
      end
      for select id_cv_itens_sub2, coditem from cv_itens_sub2
      where id_cv_itens_sub1=:lid_cv_itens _sub1
      into :lid_cv_itens_sub2, :lsub2_coditem do
      begin
        if (:lsub2_coditem=:lcoditem) then 
        begin
          execute statement (lsql_update_cv_itens_sub2)
            (:lvl_base, :lid_cv_itens_sub2)
            as user 'SYSDBA' password 'masterkey' role 'perfil_vendas'; -- sintaxe error when remove user and password
          result_value=(result_value+1);
        end
      end
    end
  end
  suspend;
end


2017-08-30 16:33 GMT-03:00 Dimitry Sibiryakov sd@... [firebird-support] <firebird-support@yahoogroups. com>:
30.08.2017 21:15, hamacker sirhamacker@... [firebird-support] wrote:
> Here in fb3, to put role name in execute, Its mandatory put username and password too.

   I was wrong a little, but still you should read the documentation:

> - if ON EXTERNAL DATA SOURCE clause is omitted then
>       a) statement will be executed against current (local) database
>       b) if AS USER clause is omitted or <user_name> equal to CURRENT_USER
>       and if ROLE clause is omitted or <role_name> equal to CURRENT_ROLE
>       then the statement is executed in current connection context
>       c) if <user_name> is not equal to CURRENT_USER or <role_name> not equal to CURRENT_ROLE
>       then the statement is executed in separate connection established inside the same
>       engine instance (i.e. created new internal connection without Y-Valve and remote layers).