| Subject | Re: [firebird-support] Any 'run as' in SQL/PSQL in FB3 ? |
|---|---|
| Author | hamacker |
| Post date | 2017-08-30T21:08:42Z |
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='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
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).