<hadjicy@...> wrote in message news:9sh3sb+916i@......

>
> I have a database with several tables.I do not in general
> want to restrict the access to anyone but for some roles which
> I have already defined I want to restrict the vewing of certain
> field when in editing screen.Is there an easy way to do it.
> For example if the Role is EMPLOYEE when vewing the
> employee record in and editing screen i do not want him to see
> the salary field.Maybe the roles for which i want this to happen
> are 3 or 4
> Thanks
> Costas

SQL works the other way. By default, nobody has access to tables, procedures
and views; only the creator of the object, SYSDBA and with limited rights,
the owner of the database (the creator of the db).
The original IB model granted everyone rights by default and denied rights
as specified. In a golden era when people were trustable, this was fine.
Now, the SQL security concept is more realist. By default, all users lack
rights over tables. You can create a role that has ALL rights over every
table and give your users that role (and connect with that role). Unlike
UPDATE and REFERENCES, there's no way to grant SELECT on specific fields.

In your case, you can create two SQL VIEWs based on the original table. One
of them doesn't have the salary field. You don't give anyone rights on the
table. You grant SELECT rights to all roles on your limited VIEW and grant
SELECT rights only to specific roles on the VIEW that includes the payment
or salary field. Another alternative is to create a selectable stored
procedure and do inside it:

create procedure p0(...)
returns (name varchar(50), payment numeric(15,2))
as begin
for SELECT e_name, e_payment from employee
into :name, :payment do
begin
if (current_role <> 'FULL_ROLE')
then payment = 0;
SUSPEND;
end
end

This way, all users see the same number of fields (less problem for the
client-side application) but unauthorized people only get zero in the
salary. Again, you don't give anyone SELECT rights over your table, but this
time you can grant any role EXECUTE rights on the procedure, since the
procedure decides internally. You could achieve the same effect with only
one SQL VIEW by putting the salary in a sub-select, but the code looks ugly:

create view v0(name, payment) as
select e.e_name,
(select e2.e_payment from employee e2
where e2.emp_code = e.emp_code
AND current_role = 'FULL_ROLE')
from employee e;

This way, you get NULL payment when the role is different than FULL_ROLE.
Another way to write the same that causes different internal execution is:

create view v0(name, payment) as
select e.e_name, e2.e_payment
from employee e left join employee e2
ON e2.emp_code = e.emp_code
AND current_role = 'FULL_ROLE';

Hope this gives you ideas.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing