The 2 September alpha code of PyrrhoDB v7 is now available
So far it can manage creation and CRUD operations on simple tables, but has a full set of data types and system tables. There is an updated introduction to the source code in the doc folder.
Work continues, comments welcome.
Monday, 2 September 2019
Tuesday, 20 August 2019
Pyrrho V7 progress
A draft version of the “Introduction to the Source Code” document for v7.0 is available here .
So far PyrrhoSvr.exe is under 800KB, and will probably stay below 1MB. It is quite different from version 6.3.
By contrast the user manual Pyrrho.pdf has almost no changes, apart from dropping the idea of multi-database connections.
Database files from version 6.3 (OSP and Pro) should work with PyrrhoSvr v7.0. The reverse will not be the case as I hope that Pyrrho v7.0 will allow the development of database files before users are defined and will enable databases to be renamed.
Almost all of the internal operation has changed:
- Identifiers (and the Ident class) are used only for Parsing: uids are used everywhere else
- BList has been added to BTree and both are easier to use
- There is a Basis class for making work with immutable/shareable classes more efficient and intuitive
- IO operations (Level1) have been remodelled following the StrongDBMS architecture
- The Physical level 2 is not only for serialisation to the transaction log file (PhysBase has gone)
- The logical database layer (“Level 3”) now includes Transaction and Database and their components, and everything at this level is immutable and shareable
- All queries and SQL code fragments are parsed exactly once (and for each role on grant), are then immutable/shareable, and don’t need further analysis.
I will upload an alpha version soon. I plan to make Tpcc a first priority, then all of the examples in the Pyrrho manual, ending with the RESTView example probably sometime next year. The Embedded edition can wait for now.
Monday, 24 June 2019
Version 7 is coming (soon)
Starting in autumn 2018 I have been developing StrongDBMS (strongdbms.com, Twitter #StrongDBMS) which incorporates many of PyrrhoDB's ideas, but handles concurrency better than other DBMS I have managed to test. The reasons and source code are written up in GitHub, see shareabledata.org .These tests also showed up some weaknesses in Pyrrho.
Both Strong and Pyrrho use a minimal set of locks to manage concurrency and ensure ACID properties: one lock is for the DBMS itself, and one for each database file. Unfortunately, in versions up to 6.3, Pyrrho transactions can involve more than one database, and the database is stored in a sequence of files (with file names including sequence numbers 001 etc if required). Both of these "improvements" in Pyrrho make verification of ACID properties more difficult.
I will produce a cleaned up version 7 of Pyrrho that will be backwards compatible with Pyrrho 6.3, apart from lacking multi-database connections and supporting only one file per database.
Thursday, 13 December 2018
Mandatory Access Control
Pyrrho now provides a free-to-use simulation of Bell-LaPadula security, similar to the US Department of Defense Orange Book. The basic idea is well-explained in Wikipedia and popular database textbooks and there corrent implementations of this security model in Oracle 18.
A feature of the system is that a table can contain rows with different security classifications, and users will be able to see a subset of these detemined by their security clearance. Users can create or modify data that matches their clearance.
The basic idea is that there are security levels D,C,B and A, and security is managed by the database owner (the security administrator SA). The SA can give users a security clearance, and can give a classification based on these levels to objects such as tables and rows in the database.
By default all access to tables is then subject to security clearance, but the SA can limit enforcement in a table to any combination of read, insert, update and delete. In addition the SA can specify two sets of identifiers (Pyrrho calls these groups and references), so that security clearance and classification can be enhanced using subsets of these identifiers.
To whet your appetite, here is a simple test script [as revised 5 January 2019]:
A. Logged in as database owner
1. Starting with empty database "mac"
create table A(B int,C char)
create table D(E char primary key) security level D groups Army Navy references Defence scope read
create table F(G char primary key,H char security level C)
revoke "mac" from public
2. Create some users with and without clearance. (On Windows prefix the user names with Domain\)
grant "mac" to "Student"
grant "mac" to "Fred"
grant security level B groups Army references Defence Cyber to "Student"
3. Add some rows with and without classification
insert into A values(2,'Two')
insert into A values(3,'Three') security level C
insert into D values('Test')
insert into F values('MI6','sis.gov.uk')
4. Check we can see two rows in A, one row in D and two columns in F
table A
table D
table F
B. Logged in as Fred
5. Check we can only see one row in A, one column in F, and nothing in D
table A
table D
table F
6. Check we can add a row in A, D and F
insert into A values(4,'Four')
insert into D values('Fred wrote this')
insert into F values('UWS')
C. Logged in as Student
7. Check we can see three rows in A, two rows in D and two columns in F
table A
table D
table F
8. Check we can' only make changes in table D (enforcement in D is only for read)
update A set c = 'No' where b=2
update A set c = 'No' where b=3
update A set c = 'No' where b=4
update D set E='Fred?' where E<>'Test'
update F set H='www.sis.gov.uk' where G='MI6'
update F set H='www.uws.ac.uk' where G='UWS'
9. Check we can add and update rows in all three tables
insert into A values(5,'Fiv')
update A set c='Five' where b=5
insert into D values('Another')
insert into F values('BBC','bbc.co.uk')
update F set H='www.bbc.co.uk' where G='BBC'
10. Check we can see our rows and changes
table A
table D
table F
D. Logged in as Fred
11. Check Fred can't see the new rows
table A
table D
table F
E. Logged in as database owner
12. Check all tables including the security information
select B,C,security from A
select E,security from D
select G,H,security from F
select * from A where security=level c
update A set security=level C where security=level B
update F set security=level D where G='BBC'
table "Sys$Classification"
F. Logged in as Student
13. Check we can still see our row in A
select * from a where b=5
14. Check we can no longer update our rows in A or F
delete from A where b=5
update F set H='bbc.com' where G='BBC'
G. Logged in as Fred
15. Check we can see the row about the BBC
H. Logged in as database owner
16. Check that auditing has been happening
table "Sys$Audit"
Full details of Pyrrho's implementation are in the manual at section 3.4.2 and the syntax pages on pyrrhodb.com have been updated to include the syntax extensions. The SA is able to manage all of this with the help of a set of system tables such as Sys$Classification. A commercial vendor such as Oracle provides many tools to assist in this process.
The implementation in Pyrrho is new and no doubt will evolve over the next weeks. Comments please to malcolm.crowe@uws.ac.uk . Also follow me @MalcolmCrowe #PyrrhoDBMS .
(Update 5 Jan 2019: the manual has been updated to remove an incorrect assertion about changes to security clearance. Such changes can have no effect on ongoing transactions.)
A feature of the system is that a table can contain rows with different security classifications, and users will be able to see a subset of these detemined by their security clearance. Users can create or modify data that matches their clearance.
The basic idea is that there are security levels D,C,B and A, and security is managed by the database owner (the security administrator SA). The SA can give users a security clearance, and can give a classification based on these levels to objects such as tables and rows in the database.
By default all access to tables is then subject to security clearance, but the SA can limit enforcement in a table to any combination of read, insert, update and delete. In addition the SA can specify two sets of identifiers (Pyrrho calls these groups and references), so that security clearance and classification can be enhanced using subsets of these identifiers.
To whet your appetite, here is a simple test script [as revised 5 January 2019]:
A. Logged in as database owner
1. Starting with empty database "mac"
create table A(B int,C char)
create table D(E char primary key) security level D groups Army Navy references Defence scope read
create table F(G char primary key,H char security level C)
revoke "mac" from public
2. Create some users with and without clearance. (On Windows prefix the user names with Domain\)
grant "mac" to "Student"
grant "mac" to "Fred"
grant security level B groups Army references Defence Cyber to "Student"
3. Add some rows with and without classification
insert into A values(2,'Two')
insert into A values(3,'Three') security level C
insert into D values('Test')
insert into F values('MI6','sis.gov.uk')
4. Check we can see two rows in A, one row in D and two columns in F
table A
table D
table F
B. Logged in as Fred
5. Check we can only see one row in A, one column in F, and nothing in D
table A
table D
table F
6. Check we can add a row in A, D and F
insert into A values(4,'Four')
insert into D values('Fred wrote this')
insert into F values('UWS')
C. Logged in as Student
7. Check we can see three rows in A, two rows in D and two columns in F
table A
table D
table F
8. Check we can' only make changes in table D (enforcement in D is only for read)
update A set c = 'No' where b=2
update A set c = 'No' where b=3
update A set c = 'No' where b=4
update D set E='Fred?' where E<>'Test'
update F set H='www.sis.gov.uk' where G='MI6'
update F set H='www.uws.ac.uk' where G='UWS'
9. Check we can add and update rows in all three tables
insert into A values(5,'Fiv')
update A set c='Five' where b=5
insert into D values('Another')
insert into F values('BBC','bbc.co.uk')
update F set H='www.bbc.co.uk' where G='BBC'
10. Check we can see our rows and changes
table A
table D
table F
D. Logged in as Fred
11. Check Fred can't see the new rows
table A
table D
table F
E. Logged in as database owner
12. Check all tables including the security information
select B,C,security from A
select E,security from D
select G,H,security from F
select * from A where security=level c
update A set security=level C where security=level B
update F set security=level D where G='BBC'
table "Sys$Classification"
F. Logged in as Student
13. Check we can still see our row in A
select * from a where b=5
14. Check we can no longer update our rows in A or F
delete from A where b=5
update F set H='bbc.com' where G='BBC'
G. Logged in as Fred
15. Check we can see the row about the BBC
H. Logged in as database owner
16. Check that auditing has been happening
table "Sys$Audit"
Full details of Pyrrho's implementation are in the manual at section 3.4.2 and the syntax pages on pyrrhodb.com have been updated to include the syntax extensions. The SA is able to manage all of this with the help of a set of system tables such as Sys$Classification. A commercial vendor such as Oracle provides many tools to assist in this process.
The implementation in Pyrrho is new and no doubt will evolve over the next weeks. Comments please to malcolm.crowe@uws.ac.uk . Also follow me @MalcolmCrowe #PyrrhoDBMS .
(Update 5 Jan 2019: the manual has been updated to remove an incorrect assertion about changes to security clearance. Such changes can have no effect on ongoing transactions.)
Friday, 23 November 2018
Recording access to sensitive data
Today there is considerable
interest in access auditing, and a requirement in some jurisdictions for companies
to record use of sensitive data.
Pyrrho already indelibly records changes to all data together with the user/role and time of changes, and watches reading of data during transactions to construct simple constraints on a transaction being committed.
As a matter of fact, few experts agree with this feature, since it means the inclusion of a read operation in a transaction will prevent the transaction being committed a concurrent transaction commits a modification of any of the data that has been read. Pyrrho enforces this approach however, because the user whose transaction is prevented from committing is the same one who (presumably deliberately) included the read operation in the transaction.
The present discussion also considers read operations but in a different way. Here we want to distinguish sensitive data (say at the data type level) and immediately record access to it (by anyone other than the database owner), whether or not the current operation is ever committed.
These features [updated: 27 November] are available in Pyrrho version 6.3. The audit record is merged into the transaction log and a system table gives access to all the details.
From the manual:
Sec 1.5:
From the manual:
Sec 1.5:
Version
6.3 adds support for “sensitive” data, for which any access is auditable. Columns,
domains and types can be declared SENSITIVE[1].
Sensitive values are not assignment-compatible with anything that is not
sensitive, and there is a sensitive property inherited by any object that
contains a sensitive data type. This means for example that the sum of
sensitive data is still sensitive. The transaction log will contain a record of
every access to sensitive values (apart from by the database owner), even if the
transaction is rolled back. These details are visible in the Sys$Audit system
table (see section 8.3.1).
Sec 7.4:
Type = (StandardType
| DefinedType | Domain_id | Type_id | REF’(‘TableReference’)’) [UriType]
[SENSITIVE] .
Sec 8.3.1:
8.3.1 Sys$Audit
|
Field
|
DataType
|
Description
|
|
Pos
|
Char
|
The location of this access
record in the transaction log
|
|
User
|
Char
|
The defining position of the
accessing user
|
|
Table
|
Char
|
The defining position of the
sensitive table or view object
|
|
Timestamp
|
Int
|
The time of the access in
ticks
|
Audit
records are only for committed sensitive data. Entries come from physical Audit
records, and are added immediately on access (do not wait for transaction
commit).
8.3.2 Sys$AuditKey
|
Field
|
DataType
|
Description
|
|
Pos
|
Char
|
The location of the access
record in the transaction log
|
|
Seq
|
Int
|
The ordinal position of the
key (0 based)
|
|
Col
|
Char
|
The defining position of the
key column
|
|
Key
|
Char
|
A string representation of
the key value at this position
|
Key
information for audit records comes from the filters used to access a sensitive
object. For example, if a record is inserted in a table, there is no applicable
filter, the audit record will apply to the whole table, and there will be no
key information here.
Comments by email are welcome.
[1] SENSITIVE is a
reserved word in SQL that normally applies to cursor sensitivity. The usage in
Pyrrho described here is quite different, and the keyword comes at the end of a
type clause (see section 7.4).
Wednesday, 21 November 2018
Rethinking Shareable Data Structures
I have been recently developing a set of data structures following on from Okasaki's Purely Functional Data Structures. There is a lot to be gained by using immutable data structures, that is, where all the fields are public readonly in C# or public final in Java. Strings in C# and Java already have this property and it turns out to be remarkably easy to develop all the usual data structure types with this property. Something of the kind had already started to happen in Pyrrho: many of Pyrrho's data structures are immutable, and Pyrrho uses Bookmarks instead of Iterators. Specifically, the benefits (as with strings) are
So the time seemed ripe for a serious approach to #ShareableDataStructures and the fruits of these labours are emerging at github.com . Eventually the classes will be rich enough to implement a DBMS, and the plan is to implement everything in C# and Java, and then Python later. Efforts in the DBMS direction are currently called #StrongDBMS . A lot will depend on the performance of the TPCC benchmark.
It is natural to ask what this might mean for Pyrrho. It does seem like a natural evolution (Pyrrho 7.0 maybe), but some of the Pyrrho code would be a real nuisance to transform. Time will tell.
- a snapshot is obtained by a simple assignment, so rollback is a breeze
- structures can be modified while a traversal continues with the previous state#
- they are thread-safe and safe to pass as a parameter in C# or Java, so that
- these data structures never need to be locked
So the time seemed ripe for a serious approach to #ShareableDataStructures and the fruits of these labours are emerging at github.com . Eventually the classes will be rich enough to implement a DBMS, and the plan is to implement everything in C# and Java, and then Python later. Efforts in the DBMS direction are currently called #StrongDBMS . A lot will depend on the performance of the TPCC benchmark.
It is natural to ask what this might mean for Pyrrho. It does seem like a natural evolution (Pyrrho 7.0 maybe), but some of the Pyrrho code would be a real nuisance to transform. Time will tell.
Tuesday, 2 October 2018
Window Functions
A basic set of window functions are supported in PyrrhoDBMS. The full set of OLAP functions as defined in ISO 9075 used to be supported and can be again if there is demand: email me if you think this would be useful or if you find any issues (my direct email at UWS is in the pyrrhodb.com download).
Subscribe to:
Posts (Atom)
