Commented by: @dyemanov

Isn't a selectable stored procedure good enough for that purpose?

Commented by: Sean Leyne (seanleyne)

This request makes little sense, can you please outline what you are expecting to accomplish with the feature.

Commented by: maziar (maziar)

Dmitry Yemanov:
selecting stored procedure (sp) for this job have some disadvantage
1.First for large project get long time to generate sp for each
2. no enough security : for example if user A grant execute sp then it can must grant to access table then hack user can access directly to table without sp role do own job !
...and some other limitation

Sean Leyne:
easy i force use trigger for update,delete,insert for user control ans some time for manual job
but we have not any trigger in database if user use SELECT then DB not trigger any event for it. I explain for Dmitry Yemanov: why sp dont true do it
and get long time for write sp
some example of use it :
1. log user access
2. che0ck complex security option (many time simple grant don't true this job for example for time 3.00 am to 9.ooPM user a must grant and over it have not grant !)
http://3.do some manual job for example if select access to this table some job occurs

Commented by: @dyemanov

You're wrong about security. If user A is granted to execute a procedure, then all table-level grants should be assigned to the procedure instead of the user. No security hole in this case, as users don't are not allowed to access tables directly.

Commented by: maziar (maziar)

Dmitry Yemanov :
NO. and i know about this then this is minor security problem
if user A garnt excute sp and sp must access to Table A then mean user A can directly access Table A ! and this can NOT GOOD because i write sp for control user and user without run sp can access table ... !
this is not directly answer to my imporovment need but i think grant control of sp better if deffernt for sp and user table access in other new improvment !.this mean grant better if can control access of sp and user direct separately

Commented by: @dyemanov

Again, you're wrong.

-- connect as SYSDBA
create table gr (col int);

create procedure pgr returns (col int) as
begin
for select col from gr into :col do suspend;
end;

grant execute on procedure pgr to user A;
grant select on table gr to procedure pgr;

-- connect as A
select * from pgr; -- success
select * from gr; -- error

Commented by: maziar (maziar)

YES

but use each sp for each table is hard to do but event seprate it and more understandable ....

Commented by: Philip Williams (unordained)

Possible use: auditing which users have accessed a record (important in the medical field.)

Stored procedures could do this, as part of returning rows -- also insert into the audit log. The problem with a stored procedure in this case is that it can't detect and optimize extra "where" clauses:

select * from patients where date_of_birth = '1/1/2003';

In this case, the stored procedure (to replace table "patients") would be required to return all patients. It would also mark them all as accessed by the user, even though that's clearly not the case. An "ON SELECT" trigger on the table or view would allow optimization of the WHERE clause, returning only the required rows, and marking only the truly returned rows as accessed. Would this work if joining between a table/view with a trigger, and others? Would it sometimes mark extra rows as accessed, when the query engine simply *had* to filter the rows out after the fact, possibly after retrieving, joining, and transforming them? I would expect edge cases.

Another option: always also select a "column" that is the result of a stored procedure (and as of FB 2.5, this could be done in a view.)

create procedure access_patient(patient_id integer) returns (x integer) as
begin
insert into accessed_patients(patient_id, user_id, access_ts) values (:patient_id, CURRENT_USER, CURRENT_TIMESTAMP);
-- null / zero-row procedure (depending on call method)
exit;
end

create view patients2 as
select patients.*, (select x from access_patient(http://patients.id)) as x from patients;

Problem: if a user is granted access to patients2, could he avoid logging his access by doing the following?

select id, ... ["x" column not included in select list] from patients2;

It seems like rdb$db_key is somewhat preserved and silently selected when joining between tables, even if not explicitly selected; if true, could a mechanism be created to look at the "final" select results, and run the on-select triggers for the appropriate tables, based on the actual, visible rdb$db_key values being returned? Any anonymized information (via aggregates, particularly) would not be included, as their db_key values wouldn't be present in the final select. (Again, there would probably be tricks you could use to get around the logging? Casting values a lot? Selecting everything via min() or max(), for one-row queries, where the result is not anonymous, but the db_key is no longer tracked?)

In all the scenarios I list, "before" and "after" don't matter. If you're interested in modifying the result of a select to selectively hide field values from some users (null for grunts, non-null for administrators), that can be done in a complex view, and would replace "before select", except for those who would rather not use a view. (Oracle's row/column security effectively acts as a complex view / select trigger that can modify or hide results based on credentials. It's a related technology, at least.)

Commented by: Philip Williams (unordained)

Related to my comment above:
CORE1929 (row / column filtering and auditing, red database feature)
CORE886 (stored procedures accessible from views)

Commented by: @livius2

Hi,

is this somehow planned? Stored procedures are not able to be optimized by optimizer in joins.

This is really usefull feature. In the first step it can be readonly
To allow acces to field values without changing it.
It is very important feature e.g. if you must audit access for some data.
E.g. access to personal or customer data should be monitored and registered who and when.

in the next steap i see that this trigger can modify values returned.
Server when do joins should fallow oryginal values (indexed reads)
and after that apply filter on values returned by trigger
of course, it may be the case that indexed read do not find record but trigger change it to values which normally be accepted by filter
i suppose first step is only possible second as above sentence show is really difficult