Custom Security Roles

I am trying to implement forms authentication in reporting services and I
would like to use the report manager for rendering reports. What I don't
want to use is the built-in tools for item level and system level security.
Is there a way to get information as to which item is being accessed in the
Getpermissions and CheckAccess function so I can check my custom database for
authorization?Brent,
I don't think you need to know the item accessed because the CheckAccess
overload is scoped at an item level. In other words, if the user tries to
run a report A, the Report Server will pass the security descriptor for
report A only. The job of the security extension is to find out if the user
has rights to carry the action by examing the security descriptor. Any yes,
you can use application-defined role membership policies to simplify the
user maintanance.
I have a two-part article in the works for the SQL Magazine which discusses
Forms Authentication in detail. It should be out in January-February issues
I think.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Brent Slezak" <Brent Slezak@.discussions.microsoft.com> wrote in message
news:4614209E-953D-4A4B-B2BD-96EFE79FFDAC@.microsoft.com...
> I am trying to implement forms authentication in reporting services and I
> would like to use the report manager for rendering reports. What I don't
> want to use is the built-in tools for item level and system level
security.
> Is there a way to get information as to which item is being accessed in
the
> Getpermissions and CheckAccess function so I can check my custom database
for
> authorization?|||Teo,
First of all, I want to thank you for your prompt response to my question
and I will be looking for that article when it comes out.
Let me go into further detail as to the situation with the reporting
services and how I would like to use them. I currrently have a proprietary
security system setup for my enterprise-scale application that manages
thousands of users and groups and tens of thousands of roles. I don't want to
have to re-define that information in the ReportSErver database. Actually I
would like it if I had absolutly no security definition at all in the
ReportServer database. That being said. How would I get the unique
identifier of the object (i.e. report,folder,datasource) being accessed so
that I can cross-reference MyDatabase1 for security access.
In a nutshell I want to be able to use the report manager for the report
rendering and not for managing role-based security. I think building our own
custom rendering UI is the only logical alternative.
Please let me know what you would consider the best option with this scenario.
Thank you in advance for your input.
Brent
"Teo Lachev [MVP]" wrote:
> Brent,
> I don't think you need to know the item accessed because the CheckAccess
> overload is scoped at an item level. In other words, if the user tries to
> run a report A, the Report Server will pass the security descriptor for
> report A only. The job of the security extension is to find out if the user
> has rights to carry the action by examing the security descriptor. Any yes,
> you can use application-defined role membership policies to simplify the
> user maintanance.
> I have a two-part article in the works for the SQL Magazine which discusses
> Forms Authentication in detail. It should be out in January-February issues
> I think.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Brent Slezak" <Brent Slezak@.discussions.microsoft.com> wrote in message
> news:4614209E-953D-4A4B-B2BD-96EFE79FFDAC@.microsoft.com...
> > I am trying to implement forms authentication in reporting services and I
> > would like to use the report manager for rendering reports. What I don't
> > want to use is the built-in tools for item level and system level
> security.
> >
> > Is there a way to get information as to which item is being accessed in
> the
> > Getpermissions and CheckAccess function so I can check my custom database
> for
> > authorization?
>
>|||Brent,
I understand your scenario now. Of course, assuming that the reports will
be rendered on the server side of the application you don't have an issue
since you have an application front end. This is similar to the security
scenario I describe in Chapter 13 of my book.
However, the real problem is with URL addressability and custom security
extension. One approach that may be appropriate in your case is to get the
report path from the URL request. This could be similar to the approach I
describe in the following thread
http://groups.google.com/groups?q=Disable+Hyperlink+in+EXCEL&hl=en&lr=&selm=%23VCcwuh1EHA.1264%40TK2MSFTNGP12.phx.gbl&rnum=1
Once you get the report path, you can get the report identifier (the primary
key in table Catalgo) which is what you may want to use in your custom
security infrastructure.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Brent Slezak" <BrentSlezak@.discussions.microsoft.com> wrote in message
news:3741B21F-0963-457F-8EC6-A1F6403CB514@.microsoft.com...
> Teo,
> First of all, I want to thank you for your prompt response to my question
> and I will be looking for that article when it comes out.
> Let me go into further detail as to the situation with the reporting
> services and how I would like to use them. I currrently have a
proprietary
> security system setup for my enterprise-scale application that manages
> thousands of users and groups and tens of thousands of roles. I don't want
to
> have to re-define that information in the ReportSErver database. Actually
I
> would like it if I had absolutly no security definition at all in the
> ReportServer database. That being said. How would I get the unique
> identifier of the object (i.e. report,folder,datasource) being accessed so
> that I can cross-reference MyDatabase1 for security access.
> In a nutshell I want to be able to use the report manager for the report
> rendering and not for managing role-based security. I think building our
own
> custom rendering UI is the only logical alternative.
> Please let me know what you would consider the best option with this
scenario.
> Thank you in advance for your input.
> Brent
> "Teo Lachev [MVP]" wrote:
> > Brent,
> >
> > I don't think you need to know the item accessed because the CheckAccess
> > overload is scoped at an item level. In other words, if the user tries
to
> > run a report A, the Report Server will pass the security descriptor for
> > report A only. The job of the security extension is to find out if the
user
> > has rights to carry the action by examing the security descriptor. Any
yes,
> > you can use application-defined role membership policies to simplify the
> > user maintanance.
> >
> > I have a two-part article in the works for the SQL Magazine which
discusses
> > Forms Authentication in detail. It should be out in January-February
issues
> > I think.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Brent Slezak" <Brent Slezak@.discussions.microsoft.com> wrote in message
> > news:4614209E-953D-4A4B-B2BD-96EFE79FFDAC@.microsoft.com...
> > > I am trying to implement forms authentication in reporting services
and I
> > > would like to use the report manager for rendering reports. What I
don't
> > > want to use is the built-in tools for item level and system level
> > security.
> > >
> > > Is there a way to get information as to which item is being accessed
in
> > the
> > > Getpermissions and CheckAccess function so I can check my custom
database
> > for
> > > authorization?
> >
> >
> >

Custom Role Provider Everthing working except roles??

Hello Everyone,

I am trying to use a custom role provider, the main purpose so that I don't have to use a database file. My web.config file is posted below. My problem is that I think I have followed all the steps to create a custom role provider using both articles on MSDN and some written by Scott Gu. After Modifying my web.config file I went to the asp.net configuration to test the connection. Users are created in the remote database but roles are being picked up and created in the App_Data folder. Can anyone give me a hint at what I am doing wrong? thanks in advance.

<?

xmlversion="1.0"?>

<!--

Note: As an alternative to hand editing this file you can use the

web admin tool to configure settings for your application. Use

the Website->Asp.Net Configuration option in Visual Studio.

A full list of settings and comments can be found in

machine.config.comments usually located in

\Windows\Microsoft.Net\Framework\v2.x\Config

-->

<

configurationxmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

<

appSettings/>

<

connectionStrings>

<

addname="MyConnectionString"connectionString="Data Source=10.10.10.10;Database=MyDataBase;Persist Security Info=True;User ID=user;Password=password"providerName="System.Data.SqlClient"/>

</

connectionStrings>

<

system.web>

<!--

Set compilation debug="true" to insert debugging

symbols into the compiled page. Because this

affects performance, set this value to true only

during development.

-->

<

roleManagerenabled="true"/>

<

membershipdefaultProvider="CustomSQLRoleManager">

<

providers>

<

clear/>

<

addname="CustomSQLRoleManager"type="System.Web.Security.SqlMembershipProvider"connectionStringName="MyConnectionString"applicationName="MyAppllicationName"minRequiredPasswordLength="6"minRequiredNonalphanumericCharacters="0"requiresQuestionAndAnswer="false"requiresUniqueEmail="true"/>

</

providers>

</

membership>

<

compilationdebug="true">

<

assemblies>

<

addassembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>

<

addassembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/></assemblies></compilation>

<!--

The <authentication> section enables configuration

of the security authentication mode used by

ASP.NET to identify an incoming user.

-->

<

authenticationmode="Forms"/>

<!--

The <customErrors> section enables configuration

of what to do if/when an unhandled error occurs

during the execution of a request. Specifically,

it enables developers to configure html error pages

to be displayed in place of a error stack trace.

<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">

<error statusCode="403" redirect="NoAccess.htm" />

<error statusCode="404" redirect="FileNotFound.htm" />

</customErrors>

-->

</

system.web>

</

configuration>

Ok. I figured out what I was missing and bolded the lines that I added to my web.config file

<?

xmlversion="1.0"?>

<!--

Note: As an alternative to hand editing this file you can use the

web admin tool to configure settings for your application. Use

the Website->Asp.Net Configuration option in Visual Studio.

A full list of settings and comments can be found in

machine.config.comments usually located in

\Windows\Microsoft.Net\Framework\v2.x\Config

-->

<

configurationxmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

<

appSettings/>

<

connectionStrings>

<

removename="LocalSqlServer"/>

<

addname="MyConnectionString"connectionString="Data Source=10.10.10.10;Database=MyDatabase;Persist Security Info=True;User ID=User;Password=password"providerName="System.Data.SqlClient"/>

</

connectionStrings>

<

system.web>

<!--

Set compilation debug="true" to insert debugging

symbols into the compiled page. Because this

affects performance, set this value to true only

during development.

-->

<

roleManagerenabled="true"defaultProvider="CustomSQLRoleManager">

<

providers>

<

clear />

<

addconnectionStringName="MyConnectionString"applicationName="MyApp"name="CustomSQLRoleManager"type="System.Web.Security.SQLRoleProvider" />

</

providers>

</

roleManager>

<

membershipdefaultProvider="SQLRoleManager">

<

providers>

<

clear/>

<

addname="SQLRoleManager"type="System.Web.Security.SqlMembershipProvider"connectionStringName="MyConnectionString"applicationName="MyApp"minRequiredPasswordLength="6"minRequiredNonalphanumericCharacters="0"requiresQuestionAndAnswer="false"requiresUniqueEmail="true"/>

</

providers>

</

membership>

<

compilationdebug="true">

<

assemblies>

<

addassembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>

<

addassembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/></assemblies></compilation>

<!--

The <authentication> section enables configuration

of the security authentication mode used by

ASP.NET to identify an incoming user.

-->

<

authenticationmode="Forms"/>

<!--

The <customErrors> section enables configuration

of what to do if/when an unhandled error occurs

during the execution of a request. Specifically,

it enables developers to configure html error pages

to be displayed in place of a error stack trace.

<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">

<error statusCode="403" redirect="NoAccess.htm" />

<error statusCode="404" redirect="FileNotFound.htm" />

</customErrors>

-->

</

system.web>

</

configuration>|||

Ok. I figured out what I was missing and bolded the lines that I added to my web.config file

<?

xmlversion="1.0"?>

<!--

Note: As an alternative to hand editing this file you can use the

web admin tool to configure settings for your application. Use

the Website->Asp.Net Configuration option in Visual Studio.

A full list of settings and comments can be found in

machine.config.comments usually located in

\Windows\Microsoft.Net\Framework\v2.x\Config

-->

<

configurationxmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

<

appSettings/>

<

connectionStrings>

<

removename="LocalSqlServer"/>

<

addname="MyConnectionString"connectionString="Data Source=10.10.10.10;Database=MyDatabase;Persist Security Info=True;User ID=User;Password=password"providerName="System.Data.SqlClient"/>

</

connectionStrings>

<

system.web>

<!--

Set compilation debug="true" to insert debugging

symbols into the compiled page. Because this

affects performance, set this value to true only

during development.

-->

<

roleManagerenabled="true"defaultProvider="CustomSQLRoleManager">

<

providers>

<

clear />

<

addconnectionStringName="MyConnectionString"applicationName="MyApp"name="CustomSQLRoleManager"type="System.Web.Security.SQLRoleProvider" />

</

providers>

</

roleManager>

<

membershipdefaultProvider="SQLRoleManager">

<

providers>

<

clear/>

<

addname="SQLRoleManager"type="System.Web.Security.SqlMembershipProvider"connectionStringName="MyConnectionString"applicationName="MyApp"minRequiredPasswordLength="6"minRequiredNonalphanumericCharacters="0"requiresQuestionAndAnswer="false"requiresUniqueEmail="true"/>

</

providers>

</

membership>

<

compilationdebug="true">

<

assemblies>

<

addassembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>

<

addassembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/></assemblies></compilation>

<!--

The <authentication> section enables configuration

of the security authentication mode used by

ASP.NET to identify an incoming user.

-->

<

authenticationmode="Forms"/>

<!--

The <customErrors> section enables configuration

of what to do if/when an unhandled error occurs

during the execution of a request. Specifically,

it enables developers to configure html error pages

to be displayed in place of a error stack trace.

<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">

<error statusCode="403" redirect="NoAccess.htm" />

<error statusCode="404" redirect="FileNotFound.htm" />

</customErrors>

-->

</

system.web>

</

configuration>

Custom Object Wide Roles

Can we create custom object wide roles? In the same manner that db_datareader in effect grants SELECT on all tables, can we create roles that affect all objects without having to explicitly grant the permission on every object?

Yes, you can create a role and grant to it SELECT on the database, that is actually how db_datareader works as well. You can grant permissions on containers and they will apply to the contained objects. Databases and schemas are such containers.

Thanks
Laurentiu

Custom Database Roles -- Insert

I am trying to set up a Custom Database Role that allows the user to
select, update, insert and delete data. I created the role by
combining the built-in roles of db_datareader and db_datawriter.
However when I logged with the custom role I was not able to insert a
new record.
When I then went in and assigned Insert rights to the table I was
trying to insert to, I was able to do it. But if I go that route I
end up having to set permissions on every table in the database. I
just want to grant these rights to ALL the tables.
Is there a straighforward way to do this and why didn't adding
db_datawriter to the role definition accomplish this?
Thanks.
StevenHello,
See if there is any Deny permissions set for the user for the specific table
you tried to insert.
Thanks
Hari
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1180227296.829693.158290@.i13g2000prf.googlegroups.com...
>I am trying to set up a Custom Database Role that allows the user to
> select, update, insert and delete data. I created the role by
> combining the built-in roles of db_datareader and db_datawriter.
> However when I logged with the custom role I was not able to insert a
> new record.
> When I then went in and assigned Insert rights to the table I was
> trying to insert to, I was able to do it. But if I go that route I
> end up having to set permissions on every table in the database. I
> just want to grant these rights to ALL the tables.
> Is there a straighforward way to do this and why didn't adding
> db_datawriter to the role definition accomplish this?
> Thanks.
> Steven
>|||ExcelMan (sfarkas@.sjfcg.com) writes:
> I am trying to set up a Custom Database Role that allows the user to
> select, update, insert and delete data. I created the role by
> combining the built-in roles of db_datareader and db_datawriter.
> However when I logged with the custom role I was not able to insert a
> new record.
> When I then went in and assigned Insert rights to the table I was
> trying to insert to, I was able to do it. But if I go that route I
> end up having to set permissions on every table in the database. I
> just want to grant these rights to ALL the tables.
> Is there a straighforward way to do this and why didn't adding
> db_datawriter to the role definition accomplish this?
As Hari suggested, a DENY permission may be the problem. Here is a
script for SQL 2000 that demonstrates that what you want to do really
works. By the way, if you are using SQL 2005, you are better off granting
access on schema or database level.
USE tempdb
go
CREATE DATABASE rolle
EXEC sp_addlogin rollerull, '12'
go
USE rolle
go
CREATE TABLE mulle (a int NOT NULL, x sysname DEFAULT USER)
go
EXEC sp_addrole rolle
EXEC sp_addrolemember 'db_datawriter', 'rolle'
EXEC sp_addrolemember 'db_datareader', 'rolle'
EXEC sp_grantdbaccess rollerull
EXEC sp_addrolemember rolle, rollerull
go
SETUSER 'rollerull'
go
INSERT mulle (a) VALUES (12)
go
SETUSER
go
SELECT * FROM mulle
go
use tempdb
go
DROP DATABASE rolle
EXEC sp_droplogin rollerull
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx