[dba-SQLServer]Stored Procedure permissions question

Arthur Fuller artful at rogers.com
Wed Dec 10 18:37:47 CST 2003


I haven't tried that, Susan, but are you quite sure about this? It
doesn't sound right. My guess is that you have several choices, the most
useful two being:

A) write a table udf in the common db that returns the relevant info
from the users table;

B) create a view within the common db that does the same thing.

Deny table permissions to all mere mortals on the common db. Grant mere
mortals access only to the udf or the view.

Should a mortal be allowed to update his password etc., then write a
sproc dedicated to that purpose and grant exec to it as well. Don't use
an updatable view or dynamic SQL for this or you'll have to spend time
defending against SQL injections. If it's a sproc most injections are
trapped automatically.

Arthur

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan
Geller
Sent: Wednesday, December 10, 2003 11:57 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Stored Procedure permissions question


We do what you are trying to do and we have found that in order to get a
sproc in DB 1 to access a table in DB 2 we have to grant select access
to the table in DB 2.  I'm curious what  you are doing in DB 3 that is
making it not require this.

Another thing we have found with permissions is that even if we are in
only one database, if a sproc runs a query directly, then we don't need
to grant rights to the underlying tables.  However, if a sproc builds a
sQL string and then exec(MySQLSTring) at the end, we do need to grant
select access to the underlying tables.  We only do this when absolutely
necessary b/c it makes for less efficient sprocs.

--Susan

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Joe
Rojas
Sent: Wednesday, December 10, 2003 12:25 PM
To: 'dba-sqlserver at databaseadvisors.com'
Subject: RE: [dba-SQLServer]Stored Procedure permissions question


Thanks for the reply.

This is kind of what I thought with regards to sprocs.
I don't know if you read by previous post but I am having a problem
where I have one database (DB1) that has a sproc that performs a SELECT
statement on a table that is located in another database (DB2). Both
databases are on the same server.

The user has database access permitted on both databases (DB1 and DB2).
The user has execute permissions on the sproc in DB1 (no rows are
returned from this sproc, just an OUTPUT value). The user has no
permissions declared (all checkboxes are cleared) on the table in DB2.
An error of "SELECT permission denied on object 'tblLogins', database
'TNCOGlobal', owner 'dbo'" is received.

But if I use the same sproc (meaning I created another sproc with the
same
code) and setup as above but in another database (DB3), everything works
perfectly.

I am trying to hunt down what would cause this.

Any ideas?

JR


-----Original Message-----
From: Arthur Fuller [mailto:artful at rogers.com]
Sent: Wednesday, December 10, 2003 2:43 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Stored Procedure permissions question

I could be wrong, but I think that a sproc doesn't have a special user,
but rather that in theory a sproc can do anything. The issue is outside
and above the sproc, so to speak -- at the level of user rights and/or
role permissions. Then your app logs in as a user, perhaps with a role,
and that dictates what happens. A user with no rights won't be able to
execute the sproc.

Arthur

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Joe
Rojas
Sent: Wednesday, December 10, 2003 8:06 AM
To: 'dba-sqlserver at databaseadvisors.com'
Subject: [dba-SQLServer]Stored Procedure permissions question


Hello All,

(SqlServer 7.0)

I was wondering. If a user is granted execute permission to a stored
procedure (SP) and that SP accesses a table in a different database,
what user or login is used to access this table if the user executing
the SP has no explicit permissions to the table?

Another way of asking my question would be: We grant exec permissions to
SPs so that no user has direct access to a table. But is essence the
user is still getting data from a table, just through an SP. Does the SP
have it's own 'special' user to access the table directly?

Thanks!

JR





This electronic transmission is strictly confidential to TNCO, Inc. and
intended solely for the addressee. It may contain information which is
covered by legal, professional, or other privileges. If you are not the
intended addressee, or someone authorized by the intended addressee to
receive transmissions on behalf of the addressee, you must not retain,
disclose in any form, copy, or take any action in reliance on this
transmission. If you have received this transmission in error, please
notify the sender as soon as possible and destroy this message. While
TNCO, Inc. uses virus protection, the recipient should check this email
and any attachments for the presence of viruses. TNCO, Inc. accepts no
liability for any damage caused by any virus transmitted by this email.
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



This electronic transmission is strictly confidential to TNCO, Inc. and
intended solely for the addressee. It may contain information which is
covered by legal, professional, or other privileges. If you are not the
intended addressee, or someone authorized by the intended addressee to
receive transmissions on behalf of the addressee, you must not retain,
disclose in any form, copy, or take any action in reliance on this
transmission. If you have received this transmission in error, please
notify the sender as soon as possible and destroy this message. While
TNCO, Inc. uses virus protection, the recipient should check this email
and any attachments for the presence of viruses. TNCO, Inc. accepts no
liability for any damage caused by any virus transmitted by this email.
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com





More information about the dba-SQLServer mailing list