Issue Details (XML | Word | Printable)

Key: CORE-2557
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Alexander Peshkov
Reporter: eXandr
Votes: 3
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Grants on MON$ tables

Created: 15/Jul/09 09:55 AM   Updated: 14/Aug/18 01:44 PM
Component/s: Security
Affects Version/s: None
Fix Version/s: 4.0 Alpha 1

Issue Links:
Relate
 

QA Status: Covered by another test(s)
Test Details: See tests/functional/syspriv/monitor_any_attachment.fbt


 Description  « Hide
Sometimes it is necessary to permit the users to see ALL rows in the MON$ATTACHMETS/MON$STATEMENTS tables (for kiling connections/statements), but not give them RDB$ADMIN role.

For that, need something like this:

GRANT VIEWALL ON {MON$TABLE} TO {USER|ROLE}
REVOKE VIEWALL ON {MON$TABLE} FROM {USER|ROLE}

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 16/Jul/09 12:43 AM
I don't think it makes much sense to grant permissions for individual monitoring tables. Instead, there was a suggestion to have a new system role (e.g. RDB$MONITOR) which allows access to the monitoring features and can be granted to other users/roles.

eXandr added a comment - 16/Jul/09 03:06 AM
You know, I have also been thinking about the RDB$MONITOR role, however I found some serious drawbacks of this approach, e.g. allowing user to see(and kill) any connection through the RDB$MONITOR role we cannot prevent him from seeing all SQL statements, while we would want him to be able to see only his own SQL statements.

Ain Valtin added a comment - 10/Jan/11 09:20 AM
Another drawback of the special role is that you can't connect using mulitple roles (ie CONNECT ... ROLE MyRole, RDB$MONITOR ... ), so you either have to have an extra connection open all the time or do additional work each time you need to query monitoring table(s).

Dmitry Yemanov added a comment - 01/Sep/16 08:31 AM
Currently implemented using:

CREATE ROLE MONITOR SET SYSTEM PRIVILEGES TO MONITOR_ANY_ATTACHMENT;
GRANT MONITOR TO ROLE MYROLE;

Later we may add a predefined system role RDB$MONITOR, if required.

Karol Bieniaszewski added a comment - 15/May/18 09:35 AM
Can this be backported into FB3?

Alexander Peshkov added a comment - 15/May/18 10:00 AM
Close to impossible

michalk1 added a comment - 13/Aug/18 12:36 PM
This feature seams helpfull, but the proposed solution gives the granted user full access to the MON$ tables, which is often way too much.
Isn't there a way how to limit the monitoring access only to a specific procedure (something like "GRANT MONITOR_ANY_ATTACHMENT TO PROCEDURE XXXXX") ?

Alexander Peshkov added a comment - 13/Aug/18 12:42 PM
CREATE ROLE MONITOR SET SYSTEM PRIVILEGES TO MONITOR_ANY_ATTACHMENT;
GRANT MONITOR TO PROCEDURE XXXXX;

michalk1 added a comment - 13/Aug/18 02:00 PM
Alex, thanks, that would be perfect. But is it only planned feature or already implemented ? I tried it with current snapshot build (FB 4.0.0.1163) and get "Token unknown - procedure" error when running the second statement.

Alexander Peshkov added a comment - 13/Aug/18 02:16 PM
Sorry - I was sure we can grant role to procedure but I was wrong.
Probably we can have such statement even in fb4...

michalk1 added a comment - 14/Aug/18 06:10 AM - edited
Never mind, but it would be nice to have this feature. Stored procedures would allow to precisely finetune which fields and rows of monitoring tables should be visible to non-priviledged users, which is impossible to achieve solely by grants.

Alexander Peshkov added a comment - 14/Aug/18 08:45 AM
Michalk1, also you may create procedure with SQL SECURITY DEFINER and procedure will have all rights of user which created it.

michalk1 added a comment - 14/Aug/18 11:55 AM
Good point, I didn't know about this new feature that would also solve my task. Unfortunately, it doesn't seem to work either ;). The following procedure created by SYSDBA returns all connections when run by SYSDBA. But when run by an ordinary user, it sees only that user's connections.

CREATE PROCEDURE TEST
RETURNS (CONCNT INTEGER)
SQL SECURITY DEFINER
AS
BEGIN
  select count (*) from mon$attachments into :CONCNT;
  suspend;
END^

GRANT EXECUTE ON PROCEDURE TEST TO PUBLIC^

Alexander Peshkov added a comment - 14/Aug/18 12:57 PM
That's definitely bug. Please create separate ticket for it.

michalk1 added a comment - 14/Aug/18 01:44 PM
Done, created CORE-5892.