Hi !
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_CLASSRDB$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...
see you !
