Alexandre Benson Smith wrote:
Can someone explain me what's the diference between these two tables ?
I did the following test:
create table foo(
a integer,
b varchar(10));
grant select on foo to public;
grant insert on foo to Alexandre;
grant update (a) on foo to Pedro;
grant delete on foo to Maria;
commit;
First let's take a look on RDB$USER_PRIVILEGES:
select * from rdb$USER_PRIVILEGES where RDB$RELATION_NAME = 'FOO'; RDB$USER RDB$GRANTOR RDB$PRIVILEGE RDB$GRANT_OPTION RDB$RELATION_NAME RDB$FIELD_NAME RDB$USER_TYPE RDB$OBJECT_TYPE SYSDBA SYSDBA S 1 FOO [null] 8 0 SYSDBA SYSDBA I 1 FOO [null] 8 0 SYSDBA SYSDBA U 1 FOO [null] 8 0 SYSDBA SYSDBA D 1 FOO [null] 8 0 SYSDBA SYSDBA R 1 FOO [null] 8 0 PUBLIC SYSDBA S 0 FOO [null] 8 0 ALEXANDRE SYSDBA I 0 FOO [null] 8 0 PEDRO SYSDBA U 0 FOO A 8 0 MARIA SYSDBA D 0 FOO [null] 8 0
well... From the above I can see that:
- SYSDBA has (S)elect, (I)nsert, (U)pdate, (D)elete and (R)eference privileges, all with GRANT OPTION
- PUBLIC has (S)elect only no GRANT OPTION
- ALEXANDRE has (S)elect (because of public) and (I)nsert without GRANT OPTION
- PEDRO has (S)elect (because of public) and (U)pdate on column A without GRANT OPTION
- MARIA has (S)elect (because of public) and (D)elete without GRANT OPTION
All the privileges granted above is represented completely in this table....
Now let's see what is on RDB$SECURITY_CLASS, but first we need to now what secutiry class is applied to each object:
select RDB$RELATION_NAME, RDB$SECURITY_CLASS, RDB$DEFAULT_CLASS from RDB$RELATIONS where RDB$RELATION_NAME = 'FOO'; RDB$RELATION_NAME RDB$SECURITY_CLASS RDB$DEFAULT_CLASS FOO SQL$8 SQL$DEFAULT5
and:
select RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$SECURITY_CLASS from RDB$RELATION_FIELDS where RDB$RELATION_NAME = 'FOO'; RDB$RELATION_NAME RDB$FIELD_NAME RDB$SECURITY_CLASS FOO A SQL$GRANT9 FOO B [null]
So we need to check SQL$8, SQL$DEFAULT5 and SQL$GRANT9:
select RDB$SECURITY_CLASS, cast(RDB$ACL as varchar(2000)) from
RDB$SECURITY_CLASSES;
(formatted for a better reading)
RDB$SECURITY_CLASS RDB$ACL
SQL$8 ACL version 1
person: SYSDBA, privileges: (PCDWR)
person: ALEXANDRE, privileges: (IR)
person: MARIA, privileges: (ER)
person: PEDRO, privileges: (UR)
all users: (*.*), privileges: (R)
SQL$DEFAULT5 ACL version 1
person: SYSDBA, privileges: (PCDWR)
person: ALEXANDRE, privileges: (IR)
person: MARIA, privileges: (ER)
all users: (*.*), privileges: (R)
SQL$GRANT9 ACL version 1
person: SYSDBA, privileges: (PCDWR)
person: ALEXANDRE, privileges: (IR)
person: MARIA, privileges: (ER)
person: PEDRO, privileges: (UR)
all users: (*.*), privileges: (R)
I don't know why SQL$DEFAULT5 misses "person: PEDRO, privileges: (UR)" that is on SQL$8 that refers to the table too, perhaps this indicates that UPDATE has a special record on RDB$RELATION_FIELDS.RDB$SECURITY_CLASS
Let me try to interpret that data:
- all users (Public) can (R)read
- ALEXANDRE can (I)nsert and (R)ead
- MARIA can (E)rase and (R)read
- PEDRO can (U)pdate (column A, that is the field that has SQL$GRANT9) and (R)ead
- SYSDBA has P, C, D, W and R privileges, wich I don't know the meaning, but of course it is administrative/owner rights
Besides SQL$8 lists "person: PEDRO, privileges: (UR)" a further check must be in place to see if he can alter each field...
Trying to put the information above on the same terms, I get:
From RDB$USER_PRIVILEGES:
- SYSDBA has (S)elect, (I)nsert, (U)pdate, (D)elete and (R)eference privileges, all with GRANT OPTION
- PUBLIC has (S)elect only no GRANT OPTION
- ALEXANDRE has (S)elect (because of public) and (I)nsert without GRANT OPTION
- PEDRO has (S)elect (because of public) and (U)pdate on column A without GRANT OPTION
- MARIA has (S)elect (because of public) and (D)elete without GRANT OPTION
From RDB$SECURITY_CLASSES:
- SYSDBA ???
- PUBLIC has (S)elect only
- ALEXANDRE has (S)elect and (I)nsert
- PEDRO has (S)elect (U)pdate on column A
- MARIA has (S)elect(D)elete
-- No information about GRANT OPTION, I created another table and give the privileges with GRANT OPTION and see no diference on the data stored in RDB$SECURITY_CLASS.RDB$ACL
Can someone give some info about the role of each table ? As far as I can see RDB$USER_PRIVILEGES has all the information needed and RDB$SECURITY_CLASS dos not have all the information (misses GRANT OPTION) but have some info for SYSDBA that I don't know the meaning...
Dmitry Yemanov answers:
RDB$USER_PRIVILEGES is kinda public interface for RDB$SECURITY_CLASSES, the latter is mostly a lower level representation of the former. RDB$SECURITY_CLASSES defines ACLs actually used by the engine to validate permissions. It includes not only SQL permissions but also some special ones like "control", "protect" and "delete" that are assigned to the object owner. RDB$USER_PRIVILEGES is used only when we need to grant something or show/export the grants, as it lists only grantable permissions and includes the grant option.