ROLE permission not working

[Midsoft UK]

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]

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]

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]

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]

if you have to assign a user to a role, whats the purpose of connecting to the db using a role ?

[Midsoft UK]

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]

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]

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]

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]

All starting to make sense. Thanks everyone.

[Midsoft UK]

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.