ROLE permission not working

27 views
Skip to first unread message

Midsoft UK

unread,
Feb 10, 2021, 8:47:12 AMFeb 10
to firebird-support
Hi
Can anyone tell me why the following doesn't work ?
I've obviously missed something fundamental.

Using iSql - 

db - SERVTRAK, table =- JOB, user - SERVTRAK_PC, role - DBUSER
I've granted permissions to the table JOB and connected top the DB using the ROLE USERDB which has Select permission.  However I can't run a select query on the table..

(logged in using sysdba to start)
SQL> SHOW GRANTS JOB ;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON JOB TO ROLE DBUSER

then connect using the correct user-
CONNECT "localhost:c:\data\development\servtrak\data\SERVTRAK.FDB" user 'SERVTRAK_PC' password '********' ROLE 'DBUSER';

SQL> SELECT COUNT(*) FROM JOB ;
Statement failed, SQLSTATE = 28000
no permission for SELECT access to TABLE JOB

Thanks
Andy

Dimitry Sibiryakov

unread,
Feb 10, 2021, 8:53:14 AMFeb 10
to firebird...@googlegroups.com
10.02.2021 14:42, Midsoft UK wrote:
> I've granted permissions to the table JOB and connected top the DB using the ROLE USERDB
> which has Select permission.

You forgot to grant the role to the user and missed two important output lines from
isql where after connect it shows current user AND role.

--
WBR, SD.

Midsoft UK

unread,
Feb 10, 2021, 8:58:28 AMFeb 10
to firebird-support
So you have to permanently grant the role to the user, not just when you connect ?

What are the two lines I missed ?

Thanks

Dimitry Sibiryakov

unread,
Feb 10, 2021, 9:00:40 AMFeb 10
to firebird...@googlegroups.com
10.02.2021 14:56, Midsoft UK wrote:
> What are the two lines I missed ?

Pardon, one line:

> C:\Programs\Firebird\Firebird4>isql TEST -u SYSDBA -r RDB$ADMIN
> Database: TEST, User: SYSDBA, Role: RDB$ADMIN


--
WBR, SD.

Midsoft UK

unread,
Feb 10, 2021, 9:49:55 AMFeb 10
to firebird-support
if you have to assign a user to a role, whats the purpose of connecting to the db using a role ?

Midsoft UK

unread,
Feb 10, 2021, 9:49:55 AMFeb 10
to firebird-support
The full connect if it helps.

C:\Program Files (x86)\Firebird\Firebird_3_0>isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> CONNECT "localhost:c:\data\development\servtrak\data\SERVTRAK.FDB" user 'SERVTRAK_PC' password '**********' ROLE 'DBUSER';
Database: "localhost:c:\data\development\servtrak\data\SERVTRAK.FDB", User: SERVTRAK_PC
SQL>

DougC

unread,
Feb 10, 2021, 9:55:56 AMFeb 10
to firebird-support
A user can have permission to use several roles. For each connect they pick one of the available roles to use for that connection. That way they can limit their effect on the database to the limits of the role they are currently using. For example, I may have permission to use the DBBACKUP role to perform backups but I only connect using that role when I'm actually doing a backup. Otherwise, I connect using DBUSER which has fewer permissions associated with it.



---- On Wed, 10 Feb 2021 09:03:30 -0500 Midsoft UK <midsof...@gmail.com> wrote ----

Mark Rotteveel

unread,
Feb 10, 2021, 10:00:05 AMFeb 10
to firebird...@googlegroups.com
On 10-02-2021 15:03, Midsoft UK wrote:
> if you have to assign a user to a role, whats the purpose of connecting
> to the db using a role ?

Specifying the role on connect will allow the user to assume the
privileges associated with that role. The idea of roles is that a user
fulfills a functional role, and while they fulfill that role, they need
a specific set of privileges. Those privileges are assigned to a ROLE
object. If a user fulfills a different role, then they need another set
of privileges, etc. If a user connects without a role, they only have
the privileges assigned directly to their user, or to the user PUBLIC.

For example, an administrator has defined roles like "DATAENTRY" (which
allows you to enter data, e.g. INSERT privileges only) and "DATACHECKER"
(which allows you to see and update data, e.g. SELECT and UPDATE
privilege only).

A single user could have both roles. If they have no other privileges
(directly to the user or on PUBLIC), when they are logged in with role
DATAENTRY, they can only insert data, and when they are logged in with
role DATACHECKER, they can view the data and update it if necessary.

Firebird 4 will introduce a new feature on roles that will automatically
apply their privileges to a user, without the user needing to explicitly
specify that role on connect.

If a user wouldn't need to be granted a role, every user could be an
administrator, or at least gain a broad range of privileges that they
aren't actually entitled to, which would be a gigantic security hole.

Mark
--
Mark Rotteveel

Mark Rotteveel

unread,
Feb 10, 2021, 10:03:55 AMFeb 10
to firebird...@googlegroups.com
On 10-02-2021 15:01, Midsoft UK wrote:
> The full connect if it helps.
>
> C:\Program Files (x86)\Firebird\Firebird_3_0>isql
> Use CONNECT or CREATE DATABASE to specify a database
> SQL> CONNECT "localhost:c:\data\development\servtrak\data\SERVTRAK.FDB"
> user 'SERVTRAK_PC' password '**********' ROLE 'DBUSER';
> Database: "localhost:c:\data\development\servtrak\data\SERVTRAK.FDB",
> User: SERVTRAK_PC
> SQL>

Given the role is not listed, the user doesn't have access to the role.
You need to grant the role to the user:

GRANT DBUSER TO USER SERVTRAK_PC;

or if you want all users to be able to assume the role:

GRANT DBUSER TO USER PUBLIC;

Mark
--
Mark Rotteveel

Midsoft UK

unread,
Feb 10, 2021, 11:32:58 AMFeb 10
to firebird-support
All starting to make sense. Thanks everyone.

Midsoft UK

unread,
Feb 11, 2021, 8:30:09 AMFeb 11
to firebird-support
I'd assumed it was already like this - 

Firebird 4 will introduce a new feature on roles that will automatically
apply their privileges to a user, without the user needing to explicitly
specify that role on connect. 

Only putting this up there for future help for new users. 

Reply all
Reply to author
Forward
0 new messages