Viewing active (and missing) permissions in SSRS and Power BI Server

To see who has folder/report level permissions in SSRS and Power BI Server, you have to join a couple of tables together – I started with a query I found at StackOverflow and built onto it – here’s my query (it pulls a bit more detail and shows not just permissions granted, but those not granted):

select  case E.Path
		when '' then '/'
			else E.Path
		end as ItemPathAndName,
		concat(LEFT(E.Path, case
			when len(E.Path)-len(E.Name) = 0 then 0
				else len(E.Path)-len(E.Name)-1 end),'/'
		) as ItemPath,
		E.Name as ItemName,
		case E.Type
			when 1 then 'Folder'
			when 13 then 'PBI Report'
			else concat('Unknown (',Type,')')
		end as ItemType,
		case e.PolicyRoot
			when 0 then 'Inherited'
			when 1 then 'Customized'
			else 'Unknown'
		end as PermissionSource,
		C.UserName,
		D.RoleName,
		--D.Description, -- Uncomment for role description
		convert(bit, case when
				a.id is null then 0
				else 1
		end) as ValidPermission
from dbo.Catalog E
cross join dbo.Users C
cross join dbo.Roles D
   left
   join dbo.PolicyUserRole A
     on E.PolicyID = A.PolicyID 
	and A.RoleID = D.RoleID
	and A.UserID = C.UserID
order by Path, C.UserName

Rather than just selecting the permissions, this query actually shows all possible permissions, and shows where users have or don’t have them. In my case, I wanted to see a list of users on the server that lacked access to specific reports and the “show me the permissions” query wouldn’t do that – additionally, this query can show you all the places that your permissions are manually set, rather than inherited.

To interpret them, I copied the results to Excel, created a pivot table, and then filtered using PermissionSource=Customized (to see all the custom permissions) or ValidPermission=0 (to see the places where people didn’t have permission to view an item).

The crossjoins can make the resultset a bit large, but it worked for me. If you want to filter things down a bit, you can add a WHERE clause near the end to folder those columns for the specific situation you’re looking for.

Decrypting RSA with Java

In a recent Java project (a *small* departure from my normal VB.NET development), I was attempting to use RSA to decrypt a value stored in one of our databases, but was running into some trouble. When I used Java’s native RSA Cipher (available in Java 1.5+), I could decrypt the value without any issues, but when I switched to Bouncycastle, I would get gibberish. Since I was doing the decryption from inside an Oracle database, the only version of Java available was 1.4.2, which doesn’t have a default RSA provider, leaving Bouncycastle as the only option.

The decryption didn’t fail or throw an exception – it always succeeded – but the resulting decrypted byte array was completely different between the two providers. In Java’s native RSA, it was 32 bytes (as it should be), but in Bouncycastle, it was 128 bytes (the same length as the input, interestingly).

In the end, it turned out that Java’s default RSA implementation is “RSA/None/PKCS1Padding”, whereas BC’s is “RSA/None/NoPadding”. Changing BC’s version of the Cipher.getInstance line in my code to explicitly specify the new padding resolved my issue:

    RSADecrypter = Cipher.getInstance("RSA/None/PKCS1Padding", "BC");

Here’s the original code (Line 10 is the one to switch out):

Cipher RSADecrypter;

// Here's the flag for choosing which provider to use
Boolean UseBouncyCastle = Boolean.TRUE;

// Choose between Java and BouncyCastle
if (UseBouncyCastle == Boolean.TRUE)
{
    Security.addProvider(new BouncyCastleProvider());
    RSADecrypter = Cipher.getInstance("RSA", "BC");
} else
{
    RSADecrypter = Cipher.getInstance("RSA");
}

// Initialize the Cipher using our the first key in the keystore
// This step works fine for both providers
RSADecrypter.init(Cipher.DECRYPT_MODE, keystore.getKey("1", PrivateKeyPassword.toCharArray()));

// Decrypt first 128 bytes of the array - here's the problem
// Java RSA gives 32 byte result, BouncyCastle gives 128 bytes of randomness
aegEncryptionKey = RSADecrypter.doFinal(binaryDataEncrypted,0,128);

More related reading:

How to check the java version
Bouncycastle’s default crypto
Java’s default crypto