Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Grants on MON$ tables [CORE2557] #2967

Closed
firebird-issue-importer opened this issue Jul 15, 2009 · 26 comments
Closed

Grants on MON$ tables [CORE2557] #2967

firebird-issue-importer opened this issue Jul 15, 2009 · 26 comments

Comments

@firebird-issue-importer

Submitted by: eXandr (i.reg)

Relate to CORE5343

Votes: 3

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}

====== Test Details ======

See tests/functional/syspriv/monitor_any_attachment.fbt

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 16, 2009

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

summary: Improvement grants on MON tables => Grants on MON$ tables

security: Developers [ 10012 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 16, 2009

Modified by: @dyemanov

status: Open [ 1 ] => Open [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 16, 2009

Commented by: @dyemanov

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 16, 2009

Commented by: eXandr (i.reg)

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 10, 2011

Commented by: Ain Valtin (ain)

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).

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 23, 2012

Modified by: @dyemanov

Fix Version: 3.0 Beta 1 [ 10332 ]

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 24, 2014

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ] => Alexander Peshkov [ alexpeshkoff ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 17, 2014

Modified by: @dyemanov

Fix Version: 3.0 Beta 2 [ 10586 ]

Fix Version: 3.0 Beta 1 [ 10332 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 18, 2014

Modified by: @dyemanov

Fix Version: 3.0 Beta 2 [ 10586 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 9, 2016

Modified by: @dyemanov

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 1, 2016

Commented by: @dyemanov

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 1, 2016

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 1, 2016

Modified by: @dyemanov

Link: This issue relate to CORE5343 [ CORE5343 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 1, 2016

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Covered by another test(s)

Test Details: See tests/functional/syspriv/monitor_any_attachment.fbt

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 1, 2016

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 15, 2018

Commented by: @livius2

Can this be backported into FB3?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 15, 2018

Commented by: @AlexPeshkoff

Close to impossible

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 13, 2018

Commented by: michalk1 (michalk1)

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") ?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 13, 2018

Commented by: @AlexPeshkoff

CREATE ROLE MONITOR SET SYSTEM PRIVILEGES TO MONITOR_ANY_ATTACHMENT;
GRANT MONITOR TO PROCEDURE XXXXX;

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 13, 2018

Commented by: michalk1 (michalk1)

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 13, 2018

Commented by: @AlexPeshkoff

Sorry - I was sure we can grant role to procedure but I was wrong.
Probably we can have such statement even in fb4...

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 14, 2018

Commented by: michalk1 (michalk1)

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.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 14, 2018

Commented by: @AlexPeshkoff

Michalk1, also you may create procedure with SQL SECURITY DEFINER and procedure will have all rights of user which created it.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 14, 2018

Commented by: michalk1 (michalk1)

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^

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 14, 2018

Commented by: @AlexPeshkoff

That's definitely bug. Please create separate ticket for it.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 14, 2018

Commented by: michalk1 (michalk1)

Done, created CORE5892.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants