Intro

On this how-to page we're going to build a template to generate SELECT and UPDATE permissions directly into the Database.sqlpermissions file of the database project. I've used a similar solution to manage the execute permissions of stored procedures because editing the Database.sqlpermissions file by hand is error prone and quite a lot of work.

Template set-up

Start by creating a new T4 template in the templates folder of your project and call it DatabasePermissions.tt (or something similar). Set the "Skip autogeneration" property of the template to false.

When you've created the template add the following references and namespaces to your template:

<#@ assembly name="Microsoft.Data.Schema" #>
<#@ assembly name="Microsoft.Data.Schema.Sql" #>
<#@ assembly name="Microsoft.Data.Schema.Sql.SchemaModel" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.IO" #>
<#@ assembly name="System.Xml" #>
<#@ assembly name="System.Xml.Linq" #>
<#@ assembly name="TFDP.Common" #>
<#@ import namespace="Microsoft.Data.Schema" #>
<#@ import namespace="Microsoft.Data.Schema.SchemaModel" #>
<#@ import namespace="Microsoft.Data.Schema.Sql.SchemaModel" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Xml" #>
<#@ import namespace="System.Xml.Linq" #>
<#@ import namespace="TFDP.Common.T4" #>

You've probably noticed that we're going to use XLinq and the VSTDB API in this template.

Next add a new code block to the template:

<#+
private XDocument doc;
private const string ns = "urn:Microsoft.VisualStudio.Data.Schema.Permissions";
+#>

This block will also contain the methods we're going to create further on.

Output directive

Because we're editing a file that already exists in the project we don't need an output directive in the template.

Locating and loading the sqlpermissions file

The Database.sqlpermissions file is located under the Properties folder of the database project. Because we don't have a direct reference to it we need to magic our way around this by using the path of the template file and a relative path from folder where the template is located. So to locate and load the sqlpermissions file add the following code:

string sqlPermissionsPath = Path.Combine(Path.GetDirectoryName(this.Host.TemplateFile), "..", "Properties", "Database.sqlpermissions");
this.doc = XDocument.Load(sqlPermissionsPath);

The path to the template file can be obtained by using the Host property (run-time) which provides the TemplateFile property that contains the absolute path to the template file.

Enumerating the tables in the model

So now that we've got a reference to the sqlpermissions file we can walk through the schema model and enumerate all the tables that exist in the project. By using the GetMany<T> extension method which is provided by the TFDP extension you can quickly find all instances of a specific model element type. To obtain all the tables in the model simply call the GetMany<T> method like this:

var model = ((TemplateHost)this.Host).Model;
var tables = model.GetMany<ISqlTable>(String.Empty);

The tables variable will contain all the tables that exist in the schema model. Note that the String.Empty argument is used to filter the objects returned by GetMany. If you want to find all tables that contain the word "User" you can call the method like this:

var tablesWithUser = model.GetMany<ISqlTable>("User");

Locating the permission statements

The next bit of fun is to find out if there is already a permission statement defined for a given object we need to query the sqlpermissions file. We'll create a new method "FindPermission" in the template that uses the features of XLinq to find out if the object already exists in the file with the given permission:

private XElement FindPermissionStatement(IModelElement element, string permission)
{
	var match = this.doc.Descendants(XName.Get("Object", ns))
		.Where(e => e.Attribute("Name").Value == element.GetName() &&
		            e.Attribute("Schema").Value == element.GetSchemaName() &&
e.Parent.Element(XName.Get("Permission", ns)).Value == permission) .SingleOrDefault(); return match == null ? null : match.Parent; }

The return statement checks if we've found a match and returns a reference to the parent which is the actual PermissionStatement element we're interested in.

Creating the permission statements

After finding out if the object already exists in the sqlpermissions file we can now tell if we need to create a new permission statement. So let's create another method called "Grant" to tie this all together:

private void Grant(IModelElement element, string permission)
{
	XElement elem = FindPermissionStatement(element, permission);

        // Does the permission not yet exist?
	if(elem == null)
	{
		// Yes, let's create a new permission:
		XElement ps = new XElement(
			XName.Get("PermissionStatement", ns),
			new XAttribute("Action", "GRANT"),
			new XElement(XName.Get("Permission", ns), permission.ToUpper()),
			new XElement(XName.Get("Grantee", ns), "User1"),
			new XElement(
				XName.Get("Object", ns),
				new XAttribute("Name", element.GetName()),
				new XAttribute("Schema", element.GetSchemaName()),
				new XAttribute("Type", "OBJECT")
			)
		);

		// Add the permission to the root node
		// of the sqlpermissions file
		this.doc.Elements().First().Add(ps);
	}
}

The method itself is not very complicated but I skate over the structure of the PermissionStatement rather quick though. For more information on how the PermissionStatement elements are defined just open the Database.sqlpermissions file in Visual Studio and have a look at existing objects.

Making it work

All right, we've now got all the blocks together to find all the tables in the model and generate permission statements but we're missing the glue that sticks everything together. What we need to do is add a for-each loop that enumerates all the tables we've loaded from the model and call the Grant method for each table:

var model = ((TemplateHost)this.Host).Model;

foreach(var table in model.GetMany<ISqlTable>(String.Empty))
{
	Grant(table, "SELECT");
	Grant(table, "UPDATE");
}

Rather straightforward: just call the Grant method with the table as the first argument and the permission (SELECT, UPDATE, INSERT, DELETE, whatever) as the second argument.

Last but not least we need to save the XDocument instance so that the new permissions are actually stored in the Database.sqlpermissions file:

this.doc.Save(sqlPermissionsPath);

Now to test it save the template (or right-click the template to open the context menu and click "Regenerate objects") and check the Errors window and the Database.sqlpermissions file.

If you've used the "TFDPSample getting started" project you should end up with something like this:

<PermissionStatement Action="GRANT">
    <Permission>SELECT</Permission>
    <Grantee>User1</Grantee>
    <Object Name="SomeTable" Schema="dbo" Type="OBJECT" />
  </PermissionStatement>
  <PermissionStatement Action="GRANT">
    <Permission>SELECT</Permission>
    <Grantee>User1</Grantee>
    <Object Name="SomeTableTest" Schema="dbo" Type="OBJECT" />
  </PermissionStatement>
  <PermissionStatement Action="GRANT">
    <Permission>SELECT</Permission>
    <Grantee>User1</Grantee>
    <Object Name="AnotherTable" Schema="dbo" Type="OBJECT" />
  </PermissionStatement>
  <PermissionStatement Action="GRANT">
    <Permission>SELECT</Permission>
    <Grantee>User1</Grantee>
    <Object Name="AnotherTableTest" Schema="dbo" Type="OBJECT" />
  </PermissionStatement>

Wrap-up

What we've seen here is that you can create a T4 template that modifies an existing file and at the same time use the VSTDB API to query the schema model of the database project. Of course you can extend this template to generate permissions for more than just tables by using the appropriate types in the call to GetMany<T> (for example ISqlProcedure for stored procedures).

A complete version of the permissions template can be found here and can be used with the "TFDPSample getting started" project.

Last edited Sep 6, 2011 at 10:54 AM by sandermvanvliet, version 4

Comments

No comments yet.