How do I size the object security tables?

(Copy from the MIMS Open Enterprise Forum)

Sizing / Re-sizing Security Tables for MIMS/Ellipse.

The MIMS/ELLIPSE application security is configured in numerous database tables, the most importing being the following six:
+MSF02A - Security Profile Application, the records in this table contain all client applications that a profile (as defined on the security profile MSF020) has been granted access to.
+MSF02B - Security Reference Code Entity, the records in this table contain all the reference code entities ( +TYPES , of client applications) that a profile (as defined on the security profile MSF020) has been granted access to
+MSF02C - Security Profile Class
+MSF02D - Security Profile Class Attribute
+MSF02E - Security Profile Class Method, the records in this table contain all class methods that a profile (as defined on the security profile MSF020) has been granted access to. The method of security implemented by the Project Team will determine the final size of these tables. Key factors are:
+ the number of users and/or profiles;
+ the type of profiles (i.e. Global or User);
+ the number of districts;
+ the number of entities (i.e. programs or screens); and
+ the degree to which access to screen attributes is limited or +customized

MSF02A
The appropriate size for the MSF02A table may be determined by multiplying the total number of applications (MSQ ,s) by the number of entities (Profiles, Global Profiles, and/or Users). For this particular database it was determined that there are 80 MSQ ,s and there will be approximately 30
users/Global Profiles. Thus, the expected row count for this table is:
80 X 30 = 2400 rows
A table description and some notes are below.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> desc msf02a
Name Null? Type
----------------------------------------- --------
ENTRY_TYPE NOT NULL VARCHAR2(1)
ENTITY NOT NULL VARCHAR2(10)
DSTRCT_CODE NOT NULL VARCHAR2(4)
APPLICATION_NAME NOT NULL VARCHAR2(20)
APPLICATION_TYPE NOT NULL VARCHAR2(1)
ACCESS_LEVEL NOT NULL VARCHAR2(1)

SQL> select count(*) from msf02a;

COUNT(*)
----------
1312

SQL> spool off

SQL> select distinct(entity) from msf02a;

ENTITY
----------
USER1
USER2
USER3
NINES
ADMIN
USER4
USER5
RANDY

8 rows selected.

79(MSQ's) * 20(Global Profiles) = 1580

Bump it up for 'cushion':

80 * 30 = 2400

MSF02B
The appropriate size for the MSF02B table may be determined by multiplying the total number of REFCODE_ENTITY*** (SQL STATEMENT: SELECT DISTINCT RCODE_NAME FROM MSF02M by the number of districts by the number of Profiles, Global Profiles, and/or Users. For this particular database it was determined that there are 16 REFCODE_ENTITY, 5 districts, and there will be approximately 30 users/Global Profiles. Thus, the expected row count for this table is:
16 X 5 X 30 = 2400 rows
A table description and some notes are below.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> desc msf02b
Name Null? Type
----------------------- --------
ENTRY_TYPE NOT NULL VARCHAR2(1)
ENTITY NOT NULL VARCHAR2(10)
DSTRCT_CODE NOT NULL VARCHAR2(4)
REFCODE_ENTITY NOT NULL VARCHAR2(3)***FK on MSFO2M
ACCESS_LEVEL NOT NULL VARCHAR2(1)

SQL> select count(*) from msf02b;

COUNT(*)
----------
256

SQL> spool off

16 (REFCODE_ENTITY) * 5 (DSTRCT_CODE) * 30 (Global Profiles) = 2400

MSF02C
The appropriate size for the MSF02C table may be determined by multiplying the total number of Profiles, Global Profiles, and/or Users by the number of districts by the number of CLASS_NAMES*** (SQL STATEMENT: SELECT DISTINCT CLASS_NAME FROM MSF02E. For this particular database it was determined that there will be approximately 30 users/Global Profiles, 5 districts, and 91 CLASS_NAMES. Thus, the expected row count for this table is:
30 X 5 X 91 = 13650 rows
A table description and some notes are below.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> desc msf02c
Name Null? Type
------------------------- --------
ENTRY_TYPE NOT NULL VARCHAR2(1)
ENTITY NOT NULL VARCHAR2(10)
DSTRCT_CODE NOT NULL VARCHAR2(4)
CLASS_NAME NOT NULL VARCHAR2(16) ***From MSF02E
XDATA NOT NULL VARCHAR2(1)

SQL> select count(*) form msf02c
COUNT(*)
----------
2128

SQL> spool off

30 (Global Profiles) * 5 (Districts) * 91 (Class_Names***) = 13650

MSF02D
The appropriate size for the MSF02D table may be determined by multiplying the total number of Profiles, Global Profiles, and/or Users by the number of districts by the number of CLASS_NAMES*** (SQL STATEMENT: SELECT DISTINCT CLASS_NAME FROM MSF02E. For this particular database it was determined that there will be approxamately 30 users/Global Profiles, 5 districts, and 91 CLASS_NAMES. Thus, the expected row count for this table is:
30 X 5 X 91 X 2 = 546,000 rows
A table description and some notes are below.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


SQL> desc msf02d
Name Null? Type
-------------------------- --------
ENTRY_TYPE NOT NULL VARCHAR2(1)
ENTITY NOT NULL VARCHAR2(10)
DSTRCT_CODE NOT NULL VARCHAR2(4)
CLASS_NAME NOT NULL VARCHAR2(16)
CLASS_ATTRIBUTE NOT NULL VARCHAR2(50)
ACCESS_LEVEL NOT NULL VARCHAR2(1)

SQL> select count(*) from msf02d
COUNT(*)
----------
0

SQL> spool off
(PROFILES/ENTITIES) * (DIST) 5 * (CLASSES) 91 * 2 (ACCESS LEVEL) = 546,000

MSF02E
The appropriate size for the MSF02E table may be determined by multiplying the total number of needed for one completely configured ADMIN account by the total number of users/Global Profiles. Thus, the expected row count for this table is:
3325 X 30 = 99,750 rows
A table description and some notes are below.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> desc msf02e
Name Null? Type
-----------------------------
ENTRY_TYPE NOT NULL VARCHAR2(1)
ENTITY NOT NULL VARCHAR2(10)
DSTRCT_CODE NOT NULL VARCHAR2(4)
CLASS_NAME NOT NULL VARCHAR2(16)
CLASS_METHOD NOT NULL VARCHAR2(50)
SECURITY_ACCESS NOT NULL VARCHAR2(1)

SQL> select count(*) from msf02e
COUNT(*)
----------
10640

SQL> spool off

3325 = row count for one configured admin profile

3325 * 30 (num global profiles) = 99,750


The PL/SQL scripts needed to create these tables and indexes with the proper storage parameters can be generated with the msgendd.pl PERL script. This script will accept table names as arguments on the command line. The example
below will generate the scripts for tables MSF02A, -B, and -C.
(/bin/ksh) mimstest> msgendd.pl -defdd MSF02A MSF02B MSF02C
In cases where the database has already been created, the changes in storage parameters may also require a change in tablespace(s) (TABS & INDX) size. To determine how much additional tablespace is needed, you may first generate the PL/SQL scripts without the new row count estimates.
The output below shows the dbase.sql script for MSF02A, -B, -C, -D, & -E with
default storage parameters.
(/bin/ksh) mimstest> more dbase.sql
CREATE TABLESPACE TABS DATAFILE 'tabs.dbf' SIZE 55910;
COMMIT;
CREATE TABLESPACE INDX
DATAFILE 'indx.dbf' SIZE 58573;
COMMIT;
Thus, the datafile size necessary for the tablespace for these tables ONLY would be 55,910 bytes (54.6 kilobytes).
The output below shows the dbase.sql script for MSF02A, -B, -C, -D, & -E with
appropriate storage parameters based on the new estimated row counts.
(/bin/ksh) mimstest> more dbase.sql
CREATE TABLESPACE TABS
DATAFILE 'tabs.dbf' SIZE 107899080;
COMMIT;
CREATE TABLESPACE INDX
DATAFILE 'indx.dbf' SIZE 111937944;
COMMIT;
Thus, the datafile size necessary for the tablespace for these tables ONLY would be 107899080 bytes (105,370 kilobytes or 102.9 megabytes). Since the
database was created with the default allocation of 54.6 KB for these tables, we need an additional 105,315 KB (102.8 MB) for these tables.
107899080 ) 55910 = 107843170
107843170/1024 = 105315.6 KB
105315.6/1024 = 102.8 MB
Similar changes will be necessary in the index tablespace(s).