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-automations opened this issue Jul 15, 2009 · 26 comments
Closed

Grants on MON$ tables [CORE2557] #2967

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

Comments

@firebird-automations
Copy link
Collaborator

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-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

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

security: Developers [ 10012 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Beta 1 [ 10332 ]

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Beta 2 [ 10586 ]

Fix Version: 3.0 Beta 1 [ 10332 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Beta 2 [ 10586 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE5343 [ CORE5343 ]

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

Can this be backported into FB3?

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Close to impossible

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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

@firebird-automations
Copy link
Collaborator Author

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
Development

No branches or pull requests

2 participants