| Subject | Re: [firebird-support] CURRENT_ROLE returns NONE but the role exists |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-08-22T23:56:22Z |
At 12:07 AM 23/08/2009, you wrote:
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.
You have made two wrong assumptions.
-- One is that you seem to think SYSDBA is like ordinary users. It's not. SYSDBA has an implicit role, always. That role gives SYSDBA complete, destructive access to all objects, always. Accordingly, trying to force a different role onto SYSDBA is a no-op.
-- The other is that you seem to think a role is a way of replacing users. It's not. It is for packaging a group of privileges, by design and by standard.
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.
./heLen
>Hi all,That is not how roles work.
>
>I want to use roles to identify users (in views/procedures) maintained
>by a client application.
>These "users" are not reflected by database users, every login isThis is a misapprehension. From the POV of logging into the embedded server, it is not required for the user to be SYSDBA. It can be "any" user name, including users that don't exist in the security database. Reason: login to the embedded server does not authenticate users at all.
>performed with SYSDBA, because sometimes the database is transferred
>to a tablet PC and used with embedded FB access (no chance to
>install a server).
>I have created a new ROLE withA 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
>
>CREATE ROLE 'uid1';
>
>SELECT * FROM RDB$ROLES; returns "uid1".
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.
>The connection string is:Certainly they are supported - by the engine - regardless of the server model you are using. In all models, a connection can have one and only one role.
>CONNECT 'e:\testdb.fdb' USER 'SYSDBA' PASSWORD 'MASTERKEY' ROLE 'uid1';
>
>But the role is not returned by
>SELECT CURRENT_ROLE FROM RDB$DATABASE;
>
>Every attempt results in "NONE".
>
>I have tried this with Firebird 2.0.4 Win32 embedded, 2.0.5 embedded
>and 2.0.5 SuperServer (yes, for the server I've used the correct
>fbclient.dll).
>
>Are roles supported in embedded mode?
You have made two wrong assumptions.
-- One is that you seem to think SYSDBA is like ordinary users. It's not. SYSDBA has an implicit role, always. That role gives SYSDBA complete, destructive access to all objects, always. Accordingly, trying to force a different role onto SYSDBA is a no-op.
-- The other is that you seem to think a role is a way of replacing users. It's not. It is for packaging a group of privileges, by design and by standard.
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.
./heLen