Update on this behavior: I am able to reproduce the problem outside the
test harness, which is a great relief. The following sequence of steps
results in the DataDictionary having a SYS SchemaDescriptor with a null
authorization ID:
1) Create the database with authentication and authorization turned on.
At this point, the SYS SchemaDescriptor has a valid authorization ID
2) Shutdown the database
3) Reboot the database with authentication and authorization still
turned on. At this point, the SYS SchemaDescriptor has a null
authorization ID although the corresponding on-disk tuple in SYSSCHEMAS
is correct.
Does this jog anyone's memory?
Thanks,
-Rick
Rick Hillegas wrote:
Thanks, Dag. Yes, that's what I see if I select from the SYSSCHEMAS
catalog too. The problem isn't the on-disk representation of the SYS
schema. The problem is the in memory SchemaDescriptor which lives in
the system table descriptors in the DataDictionary. When I run outside
the test framework, the SYS SchemaDescriptor has a correct
authorizationID. But when I run a test case inside one of our JUnit
tests, the SYS SchemaDescriptor has a null authorizationID.
Thanks,
-Rick
Dag H. Wanvik wrote:
Rick Hillegas <[email protected]> writes:
I am seeing the following odd behavior:
o When I run ij standalone, turning on authorization and
authentication, and look inside the schema descriptor for SYSTABLES, I
see that the authorization id is what I expect: It is the name of the
user who created the database. This is also what is stored in the
tuple for SYS in SYSSCHEMAS
o But when I run an experiment inside a test case in the
GrantRevokeDDLTest junit test, although the SYS tuple in SYSSCHEMAS is
correct, there is a null authorization id in the schema descriptor for
SYSTABLES.
Not sure what you are seeing, Rick. I inserted a new test case as the
first and last test inside GrantRevokeDDLTest, see enclosure, and it
looks ok to me, that is, I do not see a null authorization id (it is is
that sems a bug to me):
I added this as a first and last test:
public void testDag () throws SQLException {
Statement s = createStatement();
ResultSet rs = s.executeQuery("select * from sys.sysschemas");
while (rs.next()) {
System.out.println("schemaid: " + rs.getString(1) +
" schemaName: " + rs.getString(2) +
" authid: " + rs.getString(3));
}
rs = s.executeQuery("select * from sys.systables");
while (rs.next()) {
System.out.println("tableid: " + rs.getString(1) +
" tableName: " + rs.getString(2) +
" schemaid: " + rs.getString(4));
}
}
and saw this output:
.schemaid: c013800d-00f8-5b53-28a9-00000019ed88 schemaName: SYSIBM
authid: TEST_DBO
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900 schemaName: SYS
authid: TEST_DBO
schemaid: c013800d-00fb-2641-07ec-000000134f30 schemaName: SYSCAT
authid: TEST_DBO
schemaid: c013800d-00fb-2642-07ec-000000134f30 schemaName: SYSFUN
authid: TEST_DBO
schemaid: c013800d-00fb-2643-07ec-000000134f30 schemaName: SYSPROC
authid: TEST_DBO
schemaid: c013800d-00fb-2644-07ec-000000134f30 schemaName: SYSSTAT
authid: TEST_DBO
schemaid: c013800d-00fb-2647-07ec-000000134f30 schemaName: NULLID
authid: TEST_DBO
schemaid: c013800d-00fb-2648-07ec-000000134f30 schemaName: SQLJ
authid: TEST_DBO
schemaid: c013800d-00fb-2646-07ec-000000134f30 schemaName: SYSCS_DIAG
authid: TEST_DBO
schemaid: c013800d-00fb-2649-07ec-000000134f30 schemaName: SYSCS_UTIL
authid: TEST_DBO
schemaid: 80000000-00d2-b38f-4cda-000a0a412c00 schemaName: APP
authid: APP
tableid: 80000010-00d0-fd77-3ed8-000a0a0b1900 tableName:
SYSCONGLOMERATES schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 80000018-00d0-fd77-3ed8-000a0a0b1900 tableName: SYSTABLES
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 8000001e-00d0-fd77-3ed8-000a0a0b1900 tableName: SYSCOLUMNS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 80000022-00d0-fd77-3ed8-000a0a0b1900 tableName: SYSSCHEMAS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 8000002f-00d0-fd77-3ed8-000a0a0b1900 tableName:
SYSCONSTRAINTS schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 80000039-00d0-fd77-3ed8-000a0a0b1900 tableName: SYSKEYS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 8000003e-00d0-fd77-3ed8-000a0a0b1900 tableName: SYSDEPENDS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: c013800d-00d7-ddbd-08ce-000a0a411400 tableName: SYSALIASES
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 8000004d-00d0-fd77-3ed8-000a0a0b1900 tableName: SYSVIEWS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 80000056-00d0-fd77-3ed8-000a0a0b1900 tableName: SYSCHECKS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 8000005b-00d0-fd77-3ed8-000a0a0b1900 tableName:
SYSFOREIGNKEYS schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 80000000-00d1-15f7-ab70-000a0a0b1500 tableName:
SYSSTATEMENTS schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 80000000-00d3-e222-873f-000a0a0b1900 tableName: SYSFILES
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: c013800d-00d7-c025-4809-000a0a411200 tableName: SYSTRIGGERS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: f81e0010-00e3-6612-5a96-009e3a3b5e00 tableName:
SYSSTATISTICS schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: c013800d-00f8-5b70-bea3-00000019ed88 tableName: SYSDUMMY1
schemaid: c013800d-00f8-5b53-28a9-00000019ed88
tableid: b8450018-0103-0e39-b8e7-00000010f010 tableName:
SYSTABLEPERMS schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 286cc01e-0103-0e39-b8e7-00000010f010 tableName: SYSCOLPERMS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 2057c01b-0103-0e39-b8e7-00000010f010 tableName:
SYSROUTINEPERMS schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: e03f4017-0115-382c-08df-ffffe275b270 tableName: SYSROLES
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 9810800c-0121-c5e2-e794-00000043e718 tableName: SYSSEQUENCES
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 9810800c-0121-c5e1-a2f5-00000043e718 tableName: SYSPERMS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
.....schemaid: c013800d-00f8-5b53-28a9-00000019ed88 schemaName:
SYSIBM authid: TEST_DBO
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900 schemaName: SYS
authid: TEST_DBO
schemaid: c013800d-00fb-2641-07ec-000000134f30 schemaName: SYSCAT
authid: TEST_DBO
schemaid: c013800d-00fb-2642-07ec-000000134f30 schemaName: SYSFUN
authid: TEST_DBO
schemaid: c013800d-00fb-2643-07ec-000000134f30 schemaName: SYSPROC
authid: TEST_DBO
schemaid: c013800d-00fb-2644-07ec-000000134f30 schemaName: SYSSTAT
authid: TEST_DBO
schemaid: c013800d-00fb-2647-07ec-000000134f30 schemaName: NULLID
authid: TEST_DBO
schemaid: c013800d-00fb-2648-07ec-000000134f30 schemaName: SQLJ
authid: TEST_DBO
schemaid: c013800d-00fb-2646-07ec-000000134f30 schemaName: SYSCS_DIAG
authid: TEST_DBO
schemaid: c013800d-00fb-2649-07ec-000000134f30 schemaName: SYSCS_UTIL
authid: TEST_DBO
schemaid: 80000000-00d2-b38f-4cda-000a0a412c00 schemaName: APP
authid: APP
schemaid: 8a16905c-0125-fdf6-12c5-ffffaa8cb81a schemaName: MAMTA1
authid: MAMTA1
schemaid: 9cf91078-0125-fdf6-12c5-ffffaa8cb81a schemaName: MAMTA2
authid: MAMTA2
schemaid: 36e4d0a3-0125-fdf6-12c5-ffffaa8cb81a schemaName: SESSION
authid: TEST_DBO
tableid: 80000010-00d0-fd77-3ed8-000a0a0b1900 tableName:
SYSCONGLOMERATES schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 80000018-00d0-fd77-3ed8-000a0a0b1900 tableName: SYSTABLES
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 8000001e-00d0-fd77-3ed8-000a0a0b1900 tableName: SYSCOLUMNS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 80000022-00d0-fd77-3ed8-000a0a0b1900 tableName: SYSSCHEMAS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 8000002f-00d0-fd77-3ed8-000a0a0b1900 tableName:
SYSCONSTRAINTS schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 80000039-00d0-fd77-3ed8-000a0a0b1900 tableName: SYSKEYS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 8000003e-00d0-fd77-3ed8-000a0a0b1900 tableName: SYSDEPENDS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: c013800d-00d7-ddbd-08ce-000a0a411400 tableName: SYSALIASES
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 8000004d-00d0-fd77-3ed8-000a0a0b1900 tableName: SYSVIEWS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 80000056-00d0-fd77-3ed8-000a0a0b1900 tableName: SYSCHECKS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 8000005b-00d0-fd77-3ed8-000a0a0b1900 tableName:
SYSFOREIGNKEYS schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 80000000-00d1-15f7-ab70-000a0a0b1500 tableName:
SYSSTATEMENTS schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 80000000-00d3-e222-873f-000a0a0b1900 tableName: SYSFILES
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: c013800d-00d7-c025-4809-000a0a411200 tableName: SYSTRIGGERS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: f81e0010-00e3-6612-5a96-009e3a3b5e00 tableName:
SYSSTATISTICS schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: c013800d-00f8-5b70-bea3-00000019ed88 tableName: SYSDUMMY1
schemaid: c013800d-00f8-5b53-28a9-00000019ed88
tableid: b8450018-0103-0e39-b8e7-00000010f010 tableName:
SYSTABLEPERMS schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 286cc01e-0103-0e39-b8e7-00000010f010 tableName: SYSCOLPERMS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 2057c01b-0103-0e39-b8e7-00000010f010 tableName:
SYSROUTINEPERMS schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: e03f4017-0115-382c-08df-ffffe275b270 tableName: SYSROLES
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 9810800c-0121-c5e2-e794-00000043e718 tableName: SYSSEQUENCES
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 9810800c-0121-c5e1-a2f5-00000043e718 tableName: SYSPERMS
schemaid: 8000000d-00d0-fd77-3ed8-000a0a0b1900
tableid: 8b5fd0a6-0125-fdf6-12c5-ffffaa8cb81a tableName: T2 schemaid:
36e4d0a3-0125-fdf6-12c5-ffffaa8cb81a
Time: 42.13
OK (6 tests)
