SQL Alert: permissions

Showing posts with label permissions. Show all posts

Sunday, March 25, 2012

Accessing data connection works locally, but not when deployed

Hi all, I hope this is a simple issue that I'm overlooking something or have contradicting permissions set somewhere. I have a fairly basic site with a subfolder containing an aspx page. The user logs in to gain access to the subfolder. That part works fine.
On another page(also within a subfolder in the subfolder), they can post form data to a database (SQL Server 2000) which exists on a different server than my web server. This page opens fine, but when submitting the data (only one value to one field for my testing purposes), it bombs and returns the error "Login failed for user"...
In my connection string I've specified the username and password which I assigned to the db in Enterprise Manager (not sa, btw). I've also tried letting IIS control the UID/PW, and even specified no UID/PW in either the connection string or the db. Anonymous access enabled in IIS for this virtual directory also returns the error. (?)
I should mention that in Visual Studio, the connection string was automatically set to my local development machine's workstation ID. I removed that and have tried replacing it with the server name, blank, etc.(in notepad) - to no avail. dll's are in their proper places, etc. The only thing not working is the database connection.
Using: IIS v6, SQL Server 2000, and Visual Studio.NET 2003 (1.1 framework)
Anyway - any suggestions? There are so many places to enable/disable permissions, it's getting confusing. I hope it's something I'm overlooking and not a problem with our SQL installation...
Thanks!
--DonnieI'm no longer getting the error when I press Submit, although now I need to figure out why the data isn't writing to the table. I think the problem now is in my query parameters. Here's what I did:
Recreated the DB and tables; recreated the SqlConnection in Visual Studio.
In IIS, I set the permissions to anonymous access using the IUSR_(mySERVER) (where server=the name of my web server)
In Enterprise Manager, I made the aspuser account the dbo on the database.(yep, I know this is not the best way...)
In my ASP.NET pages, I am using forms authentication and denying access to anonymous users (does this contradict the IIS setting? Should anonymous access be unchecked in IIS?)
I'm aware there are more than likely some security issues with this setup and will be working through the various posts and links here to fix them. This is a great site and it looks like I have a lot to learn, and will hopefully learn a lot here!
--Donnie|||Can you post the connection string (xxxx out the user id and password) and the code that does the data access?
Thanks,
Tyler

Friday, February 24, 2012

Access permissions on stored procedures.

Hi All
Is there a way to find out what access permissions have been granted to all
stored procedures in my database? Thank you in advance.sp_helprotect returns all permissions in the current database. To see only
stored procedures, you could put the results into a temporary table and
delete all but EXECUTE grants, which will leave stored procedures and
scalar-valued functions. Try this:
CREATE TABLE #p (Owner sysname,
Object sysname,
Grantee sysname,
grantor sysname,
ProtectType varchar(10),
Action varchar(20),
[Column] sysname);
INSERT INTO #p EXEC sp_helprotect @.permissionarea = o;
DELETE #p WHERE Action <> 'Execute';
SELECT * FROM #p;
DROP TABLE #p;
HTH
Vern
"MittyKom" wrote:

> Hi All
> Is there a way to find out what access permissions have been granted to al
l
> stored procedures in my database? Thank you in advance.
>

Access Permissions on server scoped objects for login

We are having problems with the response times from UPS WorldShip after switching from SQL Server 2000 to 2005.

I think that the problem can be fixed from the database end by setting the permissions correctly for the user/role/schema that is being used by WorldShip to connect to the server but, I'm not sure how to do it.

The Setup

Client
UPS WorldShip 8.0 running on XP Pro SP2
Connecting via Sql Native Client via SQL Server Login
Connection is over a T1 via VPN

Server -
SQL Server Standard Edition on Windows Server 2003
2x3ghz Xeon processors w/ 4gb ram

The user that is being used to connect runs under it's own schema and role and only needs access to two tables in a specific database on the server.

What UPS WorldShip seems to be doing is on a continual basis retrieving information about the layout of the database via calls such as the following

exec [sys].sp_tables NULL,NULL,NULL,N'''VIEW''',@.fUsePattern=1

exec [webservices].[sys].sp_columns_90 N'CHECK_CONSTRAINTS',N'INFORMATION_SCHEMA',N'dbase name',NULL,@.fUsePattern=1

exec [webservices].[sys].sp_columns_90 N'COLUMN_DOMAIN_USAGE',N'INFORMATION_SCHEMA',N'dbase name',NULL,@.fUsePattern=1

This seems to happen whenever WorldShip contacts the database to find out information in order to be able to create a mapping to the database as well as exporting information to it. Because of the VPN connection these calls take anywhere from 20 seconds to 3 minutes.

I am fairly confident that the problem lies with these calls to the database which I was able to capture using the SQL Server Profiler. We have experimented with the following setups.

1. Connecting to SQL 2000 over VPN with SQL Native Client - No noticeable lag
2. Connecting to SQL 2000 over VPN with SQL Server 2000 driver - No Noticable lag
3. Connecting to SQL 2005 locally with SQL Native Client - No Noticable lag
4. Connectiong to SQL 2005 over VPN with SQL Native Client - Lots of lag

Our network admin has been testing the network connections over the VPN and it is very responsive with none of the long wait times found when using UPS WorldShip.

Now for a possible solution other than getting UPS to fix their software. I think that by limiting the tables and views that the login is able to see will cut down significantly on the lag times that are being experienced. The problem is that there were 264 items that were being returned by sp_tables. I was able to cut that down to 154. I am unable to disable access to any of the rest of the items because they are server scoped.

Take for example the INFORMATION_SCHEMA.CHECK_CONSTRAINTS view. When I try to deny access to it in any way I get the following error:

Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master (Microsoft SQL Server, Error: 4629)

Am I able to deny access to these types of object and if so how? Also, what objects should be accessable such as sys.database_mirroring, sys.database_recovery_status, etc?

Yes, you should be able to deny access to server scoped objects. Create a user in master that is mapped to same login as that of your database user and deny the select permissions to that user. I hope the below example will make it more clear.

Let the database name be dbname, login used to connect to the server be lgn1 and the user corresponding to login lgn1 in dbname be usr1. If i understand your scenario correctly, lgn1 connects to the database dbname and runs the query

exec [sys].sp_tables NULL,NULL,NULL,N'''VIEW''',@.fUsePattern=1

You have denied select permissions on various views to usr1 and managed to reduce the number of objects returned to 154. Now to get it down further , try the following steps.

Connect to the master database and create a user for login lgn1.

use master

create user usr_master for login lgn1

Next, deny the select permissions on the various object to this user usr_master

DENY SELECT ON INFORMATION_SCHEMA.CHECK_CONSTRAINTS to usr_master.

Repeat for every server scoped object you want to disable access.

Let me know if this works for you

|||Weird. I thought I had tried that. Maybe I didn't link the user to the login. Anyway, it seems to have worked.

You don't by chance have any idea as to what should stay accessible to a client.
|||

Check the TABLE_OWNER column returned by sp_tables. If the TABLE_OWNER is INFORMATION_SCHEMA or SYS, then those views return metadata about the server. If your client absolutely does not require access to any metadata of the server then you can disable access to all objects whose TABLE_OWNER is INFORMATION_SCHEMA or SYS.

Access Permissions on server scoped objects for login

We are having problems with the response times from UPS WorldShip after switching from SQL Server 2000 to 2005.

The Setup

Client
UPS WorldShip 8.0 running on XP Pro SP2
Connecting via Sql Native Client via SQL Server Login
Connection is over a T1 via VPN

Server -
SQL Server Standard Edition on Windows Server 2003
2x3ghz Xeon processors w/ 4gb ram

The user that is being used to connect runs under it's own schema and role and only needs access to two tables in a specific database on the server.

What UPS WorldShip seems to be doing is on a continual basis retrieving information about the layout of the database via calls such as the following

exec [sys].sp_tables NULL,NULL,NULL,N'''VIEW''',@.fUsePattern=1

exec [webservices].[sys].sp_columns_90 N'CHECK_CONSTRAINTS',N'INFORMATION_SCHEMA',N'webservices',NULL,@.fUsePattern=1

exec [webservices].[sys].sp_columns_90 N'COLUMN_DOMAIN_USAGE',N'INFORMATION_SCHEMA',N'webservices',NULL,@.fUsePattern=1

I am fairly confident that the problem lies with these calls to the database which I was able to capture using the SQL Server Profiler. We have experimented with the following setups.

Our network admin has been testing the network connections over the VPN and it is very responsive with none of the long wait times found when using UPS WorldShip.

Take for example the INFORMATION_SCHEMA.CHECK_CONSTRAINTS view. When I try to deny access to it in any way I get the following error:

Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master (Microsoft SQL Server, Error: 4629)

Am I able to deny access to these types of object and if so how? Also, what objects should be accessable such as sys.database_mirroring, sys.database_recovery_status, etc?

Create a user for that login in the master database. Let's say that the login is 'alice', then you have to do something like this:

use master

create user alice

deny select on INFORMATION_SCHEMA.CHECK_CONSTRAINTS to alice

This needs to happen in the master database because CHECK_CONSTRAINTS is a special catalog. You can query it in other databases, but in fact it resides in the resource database and permissions on it have to be granted within the master database. You can see the permissions granted on it by querying the database_permissions catalog in the master database:

select * from sys.database_permissions where major_id = object_id('INFORMATION_SCHEMA.CHECK_CONSTRAINTS')

The deny made in master will work in any other database.

What objects should be accessible depends on what the UPS WorldShip application needs to access. You could try to determine the required subset of objects for which access should be granted to the application by trial and error.

Thanks
Laurentiu

|||Mostly correct.

you would have to add

create user alice for login alice

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407644&SiteID=1&mode=1|||Thanks by the way.|||

You don't need the "for login alice" part in my example because the user that is created has the same name as the login, so SQL will know to map them correctly. Asvin's example uses the clause because the user he creates is named differently than the login to which it is mapped.

Thanks
Laurentiu

|||Hi guys,

I have the same problem with WorldShip 8.0 but I'm using SQL Server 2000 and it just started happening last week.

I was wondering... I thought I had blocked access to all normal tables in the DB the WorldShip is querying, but somehow it is still able to run sp_tables and sp_columns and it takes forever to do so. I am also running it over a VPN.

The "create user xyz in master" syntax doesn't seem to work in SQL Server 2000.

I have created a role for the UPS computer user (ups) and restricted it to SELECT only from the one VIEW in needs to and INSERT into only the one table it needs to however it can still run the sp_tables and sp_columns commands.

Could someone please help me block its access to these two stored procedures?

Thanks!

Robert|||

CREATE USER is new DDL added in SQL Server 2005. The SQL Server 2000 counterpart is sp_adduser.

Thanks
Laurentiu

|||Thanks for that...

I was wondering. I am trying to block access to the sp_tables and sp_columns stored procedures but it doesn't seem to let me. The "public" role has access to run these but I set the permission for the UPS user to deny but it doesn't seem to work. Is there some way for me to do this?

Thanks.|||

How did you deny the permissions? The permissions should be denied in the master database to the UPS user.

use master

DENY EXECUTE ON sp_tables to UPS

DENY EXECUTE on sp_columns to UPS

|||Thanks,

I tried that and it's still able to run them somehow. ARGH!! Oh well, back to the drawing board.|||

I assume there is a login called UPS. And this login is mapped to a user called ups in each user database. What you are trying to do is to ensure that when the login UPS connects to any database, it cannot execute the procedures sp_tables and sp_columns. I hope I understood your scenario correctly.

If the above is correct, then here's what you should do. Create a user called UPS mapped to the login UPS in the master database. You can do this by

use master

CREATE USER UPS FOR LOGIN UPS

Now deny the execute permissions on this user UPS in the master database.

use master

DENY EXECUTE ON sp_tables to UPS

DENY EXECUTE on sp_columns to UPS

It shouldn't matter what permissions are granted to public since DENY to a specific user should override the grant on the public role.

Also, can you run the below queries in the master database and let me know the results. These queries return the permissions on the procedures.

select user_name(grantee_principal_id), * from sys.database_permissions where major_id = object_id('sys.sp_tables')

select user_name(grantee_principal_id), * from sys.database_permissions where major_id = object_id('sys.sp_columns')

|||Asvin,

Thanks a lot for your reply!

I've done exactly what you say. I've verified that the user has EXECUTE denied and it does. However it is still able to run sp_tables.

Unfortunately, since I'm running SQL Server 2000, your test queries don't work. Is there a 2000 version equivalent to the sys.database_permissions?|||

Ah! Didn't realize this was sql server 2000. The query in sql server 2000 should be

select user_name(grantee), object_name(id),* from master.dbo.syspermissions where id=object_id('dbo.sp_tables')
select user_name(grantee), object_name(id),* from master.dbo.syspermissions where id=object_id('dbo.sp_columns')

|||Thanks again Asvin!

Here are the results of those two queries. I found that the best way to show you was with a screenshot, so here is the URL:

http://rob.densi.com/sqlquery.jpg

I'm not quite sure what I'm looking at, but maybe you can tell me if things are set up properly.

Thanks!

Robert|||

You are looking at the permissions that have been granted or denied on the stored procedures sp_tables and sp_columns. To drill down further into what the permissions are can you run these queries

select object_name(id),user_name(uid),* from sys.sysprotects where id=object_id('dbo.sp_tables')

select object_name(id),user_name(uid),* from sys.sysprotects where id=object_id('dbo.sp_columns')

To interpret the results of this query use the documentation at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-p_0837.asp

If you see that the protecttype column is set to 206 for the user UPS and UPS is still able to execute these procedures, some thing is really wrong. Can you also try connecting directly to the master database as UPS and run these stored procedures instead of connecting to a user defined db.