| Subject | Re: [firebird-support] Query existing users with SQL |
|---|---|
| Author | Helen Borrie |
| Post date | 2010-12-29T19:05:22Z |
At 07:08 AM 30/12/2010, Nikolaus Kern wrote:
SQL privileges are defined in an individual database. The privileges inside a database do not apply to other databases.
Inside databases, a user is "defined" only in the sense that privileges can be granted or revoked for it. You can actually grant/revoke privileges for users that don't exist in the security database.
A ROLE is an SQL privilege that is database-specific. You define a role as a package of privileges. Once the ROLE exists, you can grant that role to a user. That is the recommended way to do privileges. IMHO, it is the ONLY sensible way to do them.
You can also grant/revoke privileges to users individually. If you do it this way, there is no "connection" with any privileges that user has with any ROLE that has been granted to it. That includes the PUBLIC role that is built in.
It is very, very easy to make your database ACL into a total mess.
So - the best advice is to *design* your security scheme properly and not use ad hoc grants and revokes that break the design....I mean *literally* make a diagram and use roles (but not PUBLIC!!) to keep it simple and orderly. Use (and maintain) a script for your privileges DDL and make it a firm rule that nobody is to use interactive DDL to maintain the security scheme.
In reply to your earlier question, NEVER try to maintain RDB$USER_PRIVILEGES as though it were a user table. NEVER. The data in that table are maintained in blobs by the engine. As with any DDL, there is a whole lot of stuff that happens under the hood when the DDL requests are submitted.
./heLen
>Hi all,A USER is defined in the security database, not in user databases. The new features in v.2.5 just give Administrator access to the users defined in the security database.
>
>After continuing checking I found out that revoking assigned roles from
>user cleans the table RDB$USER_PRIVILEGES.
>
>That makes sense on the one hand, on the other I am not sure why the privileges are not cleared when a user is dropped. (Same situation when the dropping is done with gsec).
>
>Input is wellcome - I want to make sure that I correctly understand the relationship user - role - RDB$USER_PRIVILEGES.
SQL privileges are defined in an individual database. The privileges inside a database do not apply to other databases.
Inside databases, a user is "defined" only in the sense that privileges can be granted or revoked for it. You can actually grant/revoke privileges for users that don't exist in the security database.
A ROLE is an SQL privilege that is database-specific. You define a role as a package of privileges. Once the ROLE exists, you can grant that role to a user. That is the recommended way to do privileges. IMHO, it is the ONLY sensible way to do them.
You can also grant/revoke privileges to users individually. If you do it this way, there is no "connection" with any privileges that user has with any ROLE that has been granted to it. That includes the PUBLIC role that is built in.
It is very, very easy to make your database ACL into a total mess.
So - the best advice is to *design* your security scheme properly and not use ad hoc grants and revokes that break the design....I mean *literally* make a diagram and use roles (but not PUBLIC!!) to keep it simple and orderly. Use (and maintain) a script for your privileges DDL and make it a firm rule that nobody is to use interactive DDL to maintain the security scheme.
In reply to your earlier question, NEVER try to maintain RDB$USER_PRIVILEGES as though it were a user table. NEVER. The data in that table are maintained in blobs by the engine. As with any DDL, there is a whole lot of stuff that happens under the hood when the DDL requests are submitted.
./heLen