private SecureDatabase BuildSampleDB() { SecureDatabase db = new SecureDatabase(); // Add a sample table if (db.TableExists(sampleTableName)) { db.DropTable(sampleTableName); } Table t = db.AddTable(sampleTableName, 100000); t.AddColumn(new ColumnDetails("ID", "int", null, null, true)); t.AddColumn(new ColumnDetails("Title", "string", "")); t.AddColumn(new ColumnDetails("Priority", "byte", 0)); t.AddColumn(new ColumnDetails("SecretOwner", "string", null)); t.AddColumn(new ColumnDetails("SecretPriority", "byte", null)); // Add sample data (for joins) DataBlock b = new DataBlock(new string[] { "ID", "Title", "Priority", "SecretOwner", "SecretPriority" }, 5, new Array[] { new int[] { 1, 2, 3, 4, 5 }, new string[] { "One", "Two", "Three", "Four", "Five" }, new byte[] { 1, 1, 3, 3, 0 }, new string[] { "Bob", "Alice", "Alice", "Bob", "Bob" }, new byte[] { 3, 3, 2, 2, 0 } }); t.AddOrUpdate(b); return(db); }
public DatabaseFactory() { if (s_database == null) { s_database = new SecureDatabase(); } }
public void CreateTable(IList <ColumnDetails> columns, SecurityPermissions permissions) { this.Table = new Table(this.Configuration.ArribaTable, this.Configuration.ItemCountLimit); // Try to load the table if it already exists if (BinarySerializable.EnumerateUnder(Path.Combine("Tables", this.Configuration.ArribaTable)).Count() > 0) { Trace.WriteLine(string.Format("Loading Arriba Table '{0}'...", this.Configuration.ArribaTable)); this.Table.Load(this.Configuration.ArribaTable); } // Columns are added dynamically by Append // Set the table security SecureDatabase sdb = new SecureDatabase(); sdb.SetSecurity(this.Configuration.ArribaTable, permissions); sdb.SaveSecurity(this.Configuration.ArribaTable); // Debug Only: Verify consistency just after load if (this.DiagnosticsEnabled) { Trace.WriteLine("Verifying Arriba Table consistency [on load]..."); ExecutionDetails d = new ExecutionDetails(); this.Table.VerifyConsistency(this.DiagnosticsLevel, d); if (!d.Succeeded) { Debugger.Break(); Trace.TraceError(String.Format("Consistency Errors Detected: {0}", String.Join("\r\n", d.Errors))); } } }
private void SecureSampleDB(SecureDatabase db) { SecurityPermissions security = db.Security(sampleTableName); security.RestrictedColumns.Add(SecurityIdentity.Create(IdentityScope.Group, "G1"), new List <string>(new string[] { "SecretOwner" })); security.RestrictedColumns.Add(SecurityIdentity.Create(IdentityScope.Group, "G2"), new List <string>(new string[] { "SecretPriority" })); security.RowRestrictedUsers.Add(SecurityIdentity.Create(IdentityScope.Group, "G3"), "SecretPriority > 1"); security.RowRestrictedUsers.Add(SecurityIdentity.Create(IdentityScope.Group, "G4"), "SecretPriority > 2"); }
public void SecureDatabase_JoinQuerySecurity() { SecureDatabase db = BuildSampleDB(); SecureSampleDB(db); JoinQuery <SelectResult> q = new JoinQuery <SelectResult>(db, new SelectQuery() { TableName = sampleTableName, Columns = new string[] { "*" } }, new SelectQuery[] { new SelectQuery() { TableName = sampleTableName, Where = QueryParser.Parse("One") }, new SelectQuery() { TableName = sampleTableName, Where = QueryParser.Parse("SecretOwner=Bob") } }); SelectResult result; // Run a JOIN with full access - verify success (inner query expanded). q.Where = QueryParser.Parse("SecretPriority = 1 AND SecretOwner = #Q2[SecretOwner]"); result = db.Query(q, (si) => si.Name == "g1" || si.Name == "g2"); Assert.IsTrue(result.Details.Succeeded); Assert.AreEqual("[SecretPriority] = 1 AND [SecretOwner] = IN(Bob, Bob, Bob)", result.Query.Where.ToString()); // Run a JOIN with a disallowed clause on the top query - verify error q.Where = QueryParser.Parse("SecretPriority = 1 AND SecretOwner = #Q1[SecretOwner]"); result = db.Query(q, (si) => si.Name == "g2"); Assert.IsFalse(result.Details.Succeeded); Assert.AreEqual("", result.Query.Where.ToString()); Assert.AreEqual(String.Format(ExecutionDetails.DisallowedColumnQuery, "SecretOwner"), result.Details.Errors); // Run a JOIN where the join column in the outer query is disallowed - verify error q.Where = QueryParser.Parse("ID > 1 AND SecretOwner = #Q1[ID]"); result = db.Query(q, (si) => false); Assert.IsFalse(result.Details.Succeeded); Assert.AreEqual("", result.Query.Where.ToString()); Assert.AreEqual(String.Format(ExecutionDetails.DisallowedColumnQuery, "SecretOwner"), result.Details.Errors); // Run a JOIN where an inner query uses a disallowed clause - verify error q.Where = QueryParser.Parse("ID > 1 AND ID = #Q2[ID]"); result = db.Query(q, (si) => false); Assert.IsFalse(result.Details.Succeeded); Assert.AreEqual("", result.Query.Where.ToString()); Assert.AreEqual(String.Format(ExecutionDetails.DisallowedColumnQuery, "SecretOwner"), result.Details.Errors); // Run a JOIN where no secured columns are accessed - verify success (inner query expanded) q.Where = QueryParser.Parse("ID > 1 AND ID = #Q1[ID]"); result = db.Query(q, (si) => false); Assert.IsTrue(result.Details.Succeeded); Assert.AreEqual("[ID] > 1 AND [ID] = IN(1)", result.Query.Where.ToString()); }
private static void SetSettings(Table table, CombinedSettings settings) { // Set and write security SecureDatabase db = new SecureDatabase(); db.SetSecurity(table.Name, settings.Security); db.SaveSecurity(table.Name); // Create/Modify columns foreach (ColumnDetails cd in settings.Schema) { table.AddColumn(cd); } }
private static void SetSettings(string tableName, string settingsJsonPath) { Console.WriteLine("Applying settings from '{0}' to '{1}'...", settingsJsonPath, tableName); // Read settings file CombinedSettings settings = LoadSettings(settingsJsonPath); // Create table, if required SecureDatabase db = new SecureDatabase(); if (!db.TableExists(tableName)) { db.AddTable(tableName, settings.ItemCountLimit); } // Apply the settings SetSettings(db[tableName], settings); }
private static void GetSettings(string tableName, string settingsJsonPath) { Console.WriteLine("Reading settings from '{0}' and writing to '{1}'...", tableName, settingsJsonPath); CombinedSettings settings = new CombinedSettings(); SecureDatabase db = new SecureDatabase(); settings.Security = db.Security(tableName); Table t = db[tableName]; settings.ItemCountLimit = t.PartitionCount * ushort.MaxValue; settings.Schema = new List <ColumnDetails>(t.ColumnDetails); string settingsJson = JsonConvert.SerializeObject(settings, serializerSettings); File.WriteAllText(settingsJsonPath, settingsJson); }
private static void SetTableCreators(string creators) { Console.WriteLine("Setting table creators..."); SecurityPermissions createPermissions = new SecurityPermissions(); foreach (string creator in creators.Split(';')) { string[] parts = creator.Split(':'); IdentityScope scope = parts[0].Equals("u", StringComparison.OrdinalIgnoreCase) ? IdentityScope.User : IdentityScope.Group; createPermissions.Grant(new SecurityIdentity(scope, parts[1]), PermissionScope.Owner); Console.WriteLine($" - {scope} {parts[1]}"); } // Create table, if required SecureDatabase db = new SecureDatabase(); db.SetSecurity("", createPermissions); db.SaveSecurity(""); }
public void SecureDatabase_CustomQuerySecurity() { // NOTE: Where clause security can be implemented for all queries, but // column security is custom per query types. If columns are restricted // and the query is an unknown type, it's always blocked. SecureDatabase db = BuildSampleDB(); CustomQuery c = new Model.SecureDatabaseTests.CustomQuery(); c.Columns = new string[] { "ID" }; c.TableName = sampleTableName; c.Where = QueryParser.Parse("One"); SelectResult result; // Run the Query without security. Expect no restrictions and no security checks. result = db.Query(c, (si) => true); Assert.IsTrue(result.Details.Succeeded); // Restrict the secret columns to people in "G1", or users in "G2" can see everything but only for SecretPriority < 1 items. SecureSampleDB(db); // Run the Query when the user has permission to everything. Verify success. result = db.Query(c, (si) => si.Name == "g1" || si.Name == "g2"); Assert.IsTrue(result.Details.Succeeded); // Run the Query when the user has a row restriction. Verify success with row restrictor. result = db.Query(c, (si) => si.Name == "g3"); Assert.IsTrue(result.Details.Succeeded); Assert.AreEqual("[SecretPriority] > 1 AND [*]:One", result.Query.Where.ToString()); // Run the Query when the user has a column restriction. Verify error. result = db.Query(c, (si) => false); Assert.IsFalse(result.Details.Succeeded); Assert.IsTrue(result.Details.Errors.Contains(String.Format(ExecutionDetails.DisallowedQuery, "CustomQuery"))); }
public void SecureDatabase_SelectQuerySecurity() { SecureDatabase db = BuildSampleDB(); SelectQuery q = new SelectQuery() { TableName = sampleTableName, Columns = new string[] { "*" }, Where = QueryParser.Parse("One") }; SelectResult result; // Verify default 'Query' method throws (must pass method which can report user group memberships) Verify.Exception <ArribaException>(() => db.Query(new SelectQuery(q))); // Run the Query without security. Expect no restrictions and no security checks. result = db.Query(new SelectQuery(q), (si) => { Assert.Fail("No security checks for unsecured table"); return(true); }); Assert.AreEqual("[*]:One", result.Query.Where.ToString()); Assert.IsFalse(HasRestrictedClauses(result.Query.Where)); Assert.AreEqual("ID, Priority, SecretOwner, SecretPriority, Title", String.Join(", ", ((SelectQuery)result.Query).Columns)); // Restrict the secret columns to people in "G1", or users in "G2" can see everything but only for SecretPriority < 1 items. SecureSampleDB(db); // Run the Query as a user in all column restriction groups // Verify the query is unrestricted - no WHERE clause and no filtered columns result = db.Query(new SelectQuery(q), (si) => si.Name == "g1" || si.Name == "g2"); Assert.AreEqual("[*]:One", result.Query.Where.ToString()); Assert.IsFalse(HasRestrictedClauses(result.Query.Where)); Assert.AreEqual("ID, Priority, SecretOwner, SecretPriority, Title", String.Join(", ", ((SelectQuery)result.Query).Columns)); // Run the query as a user in G1 only; verify G2 restricted columns excluded result = db.Query(new SelectQuery(q), (si) => si.Name == "g1"); Assert.AreEqual("[*]:One", result.Query.Where.ToString()); Assert.IsTrue(HasRestrictedClauses(result.Query.Where)); Assert.AreEqual("ID, Priority, SecretOwner, Title", String.Join(", ", ((SelectQuery)result.Query).Columns)); // Run the Query as a user in G3. // Verify WHERE clause restriction, but no column restrictions // Security design is EITHER row or column security. result = db.Query(new SelectQuery(q), (si) => si.Name == "g3"); Assert.AreEqual("[SecretPriority] > 1 AND [*]:One", result.Query.Where.ToString()); Assert.IsFalse(HasRestrictedClauses(result.Query.Where)); Assert.AreEqual("ID, Priority, SecretOwner, SecretPriority, Title", String.Join(", ", ((SelectQuery)result.Query).Columns)); // Run the Query as a user in G4. // Verify WHERE clause restriction, but no column restrictions // Security design is EITHER row or column security. result = db.Query(new SelectQuery(q), (si) => si.Name == "g4"); Assert.AreEqual("[SecretPriority] > 2 AND [*]:One", result.Query.Where.ToString()); Assert.IsFalse(HasRestrictedClauses(result.Query.Where)); Assert.AreEqual("ID, Priority, SecretOwner, SecretPriority, Title", String.Join(", ", ((SelectQuery)result.Query).Columns)); // Run the Query as a user in all groups // Verify WHERE clause restriction for *first* matching group, no column restrictions result = db.Query(new SelectQuery(q), (si) => true); Assert.AreEqual("[SecretPriority] > 1 AND [*]:One", result.Query.Where.ToString()); Assert.IsFalse(HasRestrictedClauses(result.Query.Where)); Assert.AreEqual("ID, Priority, SecretOwner, SecretPriority, Title", String.Join(", ", ((SelectQuery)result.Query).Columns)); // Run the Query as a user in no groups. // Verify all column restrictions, no where clause filter result = db.Query(new SelectQuery(q), (si) => false); Assert.AreEqual("[*]:One", result.Query.Where.ToString()); Assert.IsTrue(HasRestrictedClauses(result.Query.Where)); Assert.AreEqual("ID, Priority, Title", String.Join(", ", ((SelectQuery)result.Query).Columns)); // Add a query clause for a restricted column when not in the required group. Verify error. result = db.Query(new SelectQuery(q) { Where = QueryParser.Parse("One AND SecretOwner=Bob") }, (si) => false); Assert.AreEqual("", result.Query.Where.ToString()); Assert.IsFalse(result.Details.Succeeded); Assert.AreEqual(String.Format(ExecutionDetails.DisallowedColumnQuery, "SecretOwner"), result.Details.Errors); // Add a query clause for a restricted column when in the required group. Verify success. result = db.Query(new SelectQuery(q) { Where = QueryParser.Parse("One AND SecretOwner=Bob") }, (si) => si.Name == "g1"); Assert.AreEqual("[*]:One AND [SecretOwner] = Bob", result.Query.Where.ToString()); Assert.IsTrue(HasRestrictedClauses(result.Query.Where)); // Ask for restricted columns in result listing. Verify restricted columns allowed only for my group, warning for removed column. result = db.Query(new SelectQuery(q) { Columns = new string[] { "Title", "SecretOwner", "SecretPriority" } }, (si) => si.Name == "g1"); Assert.AreEqual("[*]:One", result.Query.Where.ToString()); Assert.IsTrue(HasRestrictedClauses(result.Query.Where)); Assert.AreEqual("Title, SecretOwner", String.Join(", ", ((SelectQuery)result.Query).Columns)); Assert.AreEqual("SecretPriority", String.Join(", ", result.Details.AccessDeniedColumns)); }
public void SecureDatabase_AggregationQuerySecurity() { SecureDatabase db = BuildSampleDB(); AggregationQuery q = new AggregationQuery("sum", new string[] { "ID" }, "One"); q.TableName = sampleTableName; AggregationResult result; // Run the Query without security. Expect no restrictions and no security checks. result = db.Query(new AggregationQuery(q), (si) => { Assert.Fail("No security checks for unsecured table"); return(true); }); Assert.AreEqual("[*]:One", result.Query.Where.ToString()); // Restrict the secret columns to people in "G1", or users in "G2" can see everything but only for SecretPriority < 1 items. SecureSampleDB(db); // Run the Query as a user in all column restriction groups // Verify the query is unrestricted - no WHERE clause and no filtered columns result = db.Query(new AggregationQuery(q), (si) => si.Name == "g1" || si.Name == "g2"); Assert.AreEqual("[*]:One", result.Query.Where.ToString()); Assert.IsFalse(HasRestrictedClauses(result.Query.Where)); // Run the Query as a user in G3. // Verify WHERE clause restriction. result = db.Query(new AggregationQuery(q), (si) => si.Name == "g3"); Assert.AreEqual("[SecretPriority] > 1 AND [*]:One", result.Query.Where.ToString()); Assert.IsFalse(HasRestrictedClauses(result.Query.Where)); // Run the Query as a user in all groups // Verify WHERE clause restriction for *first* matching group result = db.Query(new AggregationQuery(q), (si) => true); Assert.AreEqual("[SecretPriority] > 1 AND [*]:One", result.Query.Where.ToString()); Assert.IsFalse(HasRestrictedClauses(result.Query.Where)); // Run the Query as a user in no groups. // Verify all column restrictions, no where clause filter result = db.Query(new AggregationQuery(q), (si) => false); Assert.AreEqual("[*]:One", result.Query.Where.ToString()); Assert.IsTrue(HasRestrictedClauses(result.Query.Where)); // Add a query clause for a disallowed column when not in group. Verify error. result = db.Query(new AggregationQuery(q) { Where = QueryParser.Parse("One AND SecretOwner=Bob") }, (si) => false); Assert.AreEqual("", result.Query.Where.ToString()); Assert.IsFalse(result.Details.Succeeded); Assert.AreEqual(String.Format(ExecutionDetails.DisallowedColumnQuery, "SecretOwner"), result.Details.Errors); // Add a query clause for a disallowed column when in group. Verify success. result = db.Query(new AggregationQuery(q) { Where = QueryParser.Parse("One AND SecretOwner=Bob") }, (si) => si.Name == "g1"); Assert.AreEqual("[*]:One AND [SecretOwner] = Bob", result.Query.Where.ToString()); Assert.IsTrue(HasRestrictedClauses(result.Query.Where)); // Ask to aggregate on a restricted column. Verify error. result = db.Query(new AggregationQuery(q) { AggregationColumns = new string[] { "SecretPriority" } }, (si) => si.Name == "g1"); Assert.AreEqual("", result.Query.Where.ToString()); Assert.IsFalse(result.Details.Succeeded); Assert.AreEqual(String.Format(ExecutionDetails.DisallowedColumnQuery, "SecretPriority"), result.Details.Errors); // Ask to aggregate over a restricted column dimension. Verify error. AggregationQuery a = new AggregationQuery(q); a.Dimensions.Add(new AggregationDimension("Columns", new string[] { "ID > 5", "ID > 10", "SecretPriority = 1" })); result = db.Query(a, (si) => si.Name == "g1"); Assert.AreEqual("", result.Query.Where.ToString()); Assert.IsFalse(result.Details.Succeeded); Assert.AreEqual(String.Format(ExecutionDetails.DisallowedColumnQuery, "SecretPriority"), result.Details.Errors); }