On [Sun, 23.08.2009 09:56], Helen Borrie wrote:

>
> A role is a package of privileges on objects in the database. What it is NOT is a group of users. The way roles work is
>
> 1. You create the role
> 2. You grant the required privileges to that role
> 3. You grant the role to individual users.
>
> Then, at login, by supplying both the user name and the role in the DPB, you get (first) authentication of the user at the server level and (next) application of the privileges of the role to that user, if that role has been granted to that user. With embedded, the only difference is that the server-level authentication of the user does not occur.

Ooops :)
Thanks for the clarifaction.

> Another potential mistake that's implied by your problem description is that supplying a role automatically gives any user name the privileges packaged in that role. It doesn't. Unless you explicitly grant the role to the user, the user will have no privileges.
>
> Rethink your security design and *particularly* do not write applications that log ordinary users in as SYSDBA! Remember that the engine doesn't care if a database has privileges (including ROLE packages) assigned to users that don't exist in the security database.

Maybe the following example describes the problem better:

Think about a Webserver, a programming langauge like PHP, Perl,
... and a Firebird server.

The DBA has created one user which has all permissions for database
/var/db/customer123.fdb.

The web application running on the webserver can ONLY connect with
this username to the database.

The application needs four tables:
(omitted constraints for simplicity..)


CREATE TABLE department (
id_key INTEGER,
depname VARCHAR(50)
);

1 | Department One
2 | Department Two


CREATE TABLE employee (
id_key INTEGER,
name VARCHAR(50),
department_id INTEGER
);

50 | Joe Miller | 1
56 | David Jackson | 2


CREATE TABLE users (
id_key INTEGER,
loginname VARCHAR(30),
passwd VARCHAR(100)
)

5 | Harry | xxx
6 | Janine | xxx


CREATE TABLE useraccess (
id_key INTEGER,
user_id INTEGER,
permname VARCHAR(50),
department_id INTEGER
);

100 | 5 | EMPLOYEE | 1
101 | 5 | EMPLOYEE | 2
102 | 6 | EMPLOYEE | 1



In this example, user "Harry" can see all employees from both
departments, user Janine only has access to department two.



$UserID = getMyCurrentUser();

SELECT employee.*
FROM useraccess INNER JOIN employee
ON useraccess.department_id = employee.department_id
WHERE useraccess.user_id = $UserID
AND useraccess.parmname = 'EMPLOYEE'


This query returns all employees the user has access to.


Dealing with ~100 of tables like employee which are all referencing to
the department table isn't really easy. Every query must take care
about the current user. And in addition, I often have several other
"global filters" - not only the department filter.

Instead of storing these filters at runtime in the application and
generating a complex query,
I think it's easier to store all global filters into a database table
(userAccess from example above)
and create a bunch of views (vEmployee, vSales, vXyz..).

Example:

CREATE VIEW vEmployee AS
SELECT employee.*
FROM useraccess INNER JOIN employee
ON useraccess.department_id = employee.department_id
WHERE useraccess.user_id = $UserID /* PROBLEM */
AND useraccess.parmname = 'EMPLOYEE'

With these views, I just can run a
SELECT * FROM vEmployee;
regardless of any filters - the database already knows what the user
has globally configured (for example, only see department one data).

But you can see the problem: the VIEW needs to know the current
user_id which isn't available.

So I thought about a mapping table:

CREATE TABLE rolesToUsers (
id_key INTEGER,
rolename VARCHAR(50),
user_id INTEGER
);


If a user successfully login to the application, the application
reconnects to the database, but sets now the role to "Harry" (for user
Harry).

In the VIEWs, I can now join the rolesToUsers table with
JOIN rolesToUsers ON useraccess.user_id = rolesToUsers.user_id AND
rolesToUsers.rolename = CURRENT_ROLE


What I really need is a solution to tell the database "Hey, currently
user Harry is connected to the database and use his user_id in all of
my VIEWs" - but without using real server-managed database users.


Defining a variable (with connection scope) CURRENT_APP_USER is maybe
possible via RDB$SET_CONTEXT(), but I'm looking for a solution which
can be used on Firebird (server & embedded) and MSSQL & Oracle.




Daniel

--
web: http://daniel.vogelbacher.name
irc: cytrinox @ (freenode|ircnet|quakenet)