Playing with “Folders”: The Magic behind “Oslo” Repository
One of the highlight features in “Oslo” Repository is row labeling. Imagine that you can label instances in your database tables just like how you store files into different folders on your computer. That’s right!, including controlling security - read, write permissions, that we are talking about here. All the magic behind this functionality is captured under the concept of ‘Folder’.
In “Oslo” Repository, RepositoryUsers can see a list of folders that they have access to through the view [Repository.Item].[Folders] which is linked to the table [Repository.Item].[FoldersTable]. This table consists of four columns – [Id], [Name] ,[DisplayName] and [Folder]. The column [DisplayName] is designed to be used for application localization purpose. It is linked to [System.Globalization].[StringsTable] which consists of all localized strings in repository. The column [Folder] is basically there to enable the hierarchical folder structure.
Just like other databases, only users with db_owner and sysadmin roles can view all folders from the folder table. By default, RepositoryAdministrators have read and write permissions to all root folders (Note that they are not automatically assigned with a sysadmin role). Users with other roles can view all the folders that they have at least the read access to from the security view [Repository.Item].[Folders]. They also have the same permission to all sub folders of the root folders that they have access to.
“Oslo” Repository comes with a number of helper functions around Folders. Below are examples that demonstrate the usage of some helper functions and how Folders can be used to perform table horizontal partitioning. Note that these examples are provided based on the NET. Framework CLR instances. For more examples around Folders, see the SQL script (FolderExample.sql) attached.
Insert, update and delete a folder
insert into [Repository.Item].[Folders] ([Name], [Folder])
values (N'4.0', [Repository.Item].[PathsFolder](N'/Frameworks/.Net Framework'));
update [Repository.Item].[Folders]
set [Name] = N'4.0b'
where [Id] = [Repository.Item].[PathsFolder](N'/Frameworks/.Net Framework/4.0');
delete from [Repository.Item].[Folders]
where [Id] = [Repository.Item].[PathsFolder](N'/Frameworks/.Net Framework/4.0b');
Folder path
-- Given a folder id, return its full path.
select [Repository.Item].[FoldersPath](104); -- returns /Frameworks/.Net Framework/2.0
-- Given a folder path, return its folder id.
select [Repository.Item].[PathsFolder](N'/Frameworks/.Net Framework/2.0'); -- returns 104
Descendent folders
-- Given a folder id, list all its descendent folders
select [Repository.Item].[FoldersPath](S.[Id])
from [Repository.Item].[Subfolders](101) as S;
-- Given a folder id, list all its descendent folders including itself.
select [Repository.Item].[FoldersPath](S.[Id])
from [Repository.Item].[SubfoldersAndFolder](101) as S;
Folder contents
-- List all direct contents of a folder (/Frameworks/.Net Framework/2.0') in a view.
select *
from [Clr].[Assemblies] as A
where A.[Folder] = [Repository.Item].[PathsFolder](N'/Frameworks/.Net Framework/2.0');
-- List all contents of the folder with the id 101 and its subfolders in a set of views.
select A.*
from [Clr].[Assemblies] as A
inner join [Repository.Item].[SubfoldersAndFolder](101) as S on S.[Id] = A.[Folder];
Folder permissions
-- Grant a principal access to a folder.
execute [Repository.Item].[GrantPrincipalFolderAccess]
@principal = N'MyDomain\Principal',
@folderPath = N'/Frameworks/.Net Framework/2.0',
@mayRead = 1,
@mayUpdate = 0,
@mayGrantOrRevoke = 0;
-- Revoke a principal's access to a folder.
execute [Repository.Item].[RevokePrincipalFolderAccess]
@principal = N'MyDomain\Principal',
@folderPath = N'/Frameworks/.Net Framework/2.0',
@revokeReads = 1,
@revokeUpdates = 1;
To have a quick play with Folders, follow these links to download the Oslo SDK and then populate your repository with .NET Framework 2.0 & 3.0 CLR instances.
