Re: Permissions!
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 09/10/05
- Next message: Jeffrey Yao: "Question about xp_sendmail security context"
- Previous message: Kalen Delaney: "Re: Granting GRANT permissions"
- In reply to: Arpan: "Re: Permissions!"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Sat, 10 Sep 2005 11:02:06 -0500
> but one thing is for sure - users will be accessing the table only from
> my application. What would you suggest?
Since users can access the database only from your application, I'd suggest
a single login. You can grant object permissions to this login and control
access to individual objects/columns from your app because you know the
user's identity and can execute different queries accordingly.
> The bottomline is irrespective of the way I allow users to access data,
> either I have to create 100 views for 100 users (if I use only one
> login name & password) or create 100 login names in SQL Server & have
> 100 If....Else statements in my ASP application (with the login name &
> password for every user changing in the ConnectionString) & that is
> exactly what I would like to avoid. I am back to square one! I need a
> solution to overcome such a tedious & mnotonous approach.
I understand your desire to simplify code. You don't need 100 views or 100
SQL logins or conditional code in your application. All you need to do is
execute different queries depending on the individual user. You can
accomplish this the schema I posted earlier. All you need to do is add the
users and queries to the Users and UserViews configuration tables for use by
your app. The VBScript example below shows how you could do this. You can
beef this up to suit your needs.
--proc to retrieve query for current user
CREATE PROC GetUserSqlStatement
@UserId varchar(30),
@TableName sysname
AS
SET NOCOUNT ON
SELECT SqlStatement
FROM UserViews
WHERE UserId = @UserId AND
TableName = @TableName
GO
'assuming strUserId contains the user id of current user
ExecuteQueryForUser strUserId, "Table1"
Sub ExecuteQueryForUser(UserId, TableName)
Set connection = CreateObject("ADODB.Connection")
'you could also use a standard SQL application login here
connection.Open _
"Provider=SQLOLEDB" & _
";Data Source=(local)" & _
";Initial Catalog=MyDatabase" & _
";Integrated Security=SSPI"
'get the query for this user
sqlUserQuery = GetUserSqlStatement(UserId, TableName, connection)
If sqlUserQuery = "" Then
'user has not been configured yet
Response.Write "<p>You have not yet been configured. Contact
Arpan</p>"
Else
ExecuteQuery sqlUserQuery, connection
End If
connection.Close
Set connection = Nothing
End Sub
'get the appropriate SQL query for this user
Function GetUserSqlStatement(UserId, TableName, Connection) ' As String
Dim sqlStatement
Set cmdGetUserSqlStatement = CreateObject("ADODB.Command")
Set cmdGetUserSqlStatement.ActiveConnection = Connection
cmdGetUserSqlStatement.CommandType = adCmdStoredProcedure
cmdGetUserSqlStatement.CommandText = "GetUserSqlStatement"
Set paramUserId = _
cmdGetUserSqlStatement.CreateParameter("@UserId")
paramUserId.Type = adVarChar
paramUserId.Direction = adParamInput
paramUserId.Size = 30
paramUserId.Value = UserId
cmdGetUserSqlStatement.Parameters.Append paramUserId
Set paramTableName = _
cmdGetUserSqlStatement.CreateParameter("@TableName")
paramTableName.Type = adVarWChar
paramTableName.Direction = adParamInput
paramTableName.Size = 128
paramTableName.Value = TableName
cmdGetUserSqlStatement.Parameters.Append paramTableName
Set rsSqlStatement = cmdGetUserSqlStatement.Execute
If rsSqlStatement.EOF Then
sqlStatement = ""
Else
sqlStatement = rsSqlStatement("SqlStatement")
End If
rsSqlStatement.Close
Set rsSqlStatement = Nothing
Set cmdGetUserSqlStatement = Nothing
GetUserSqlStatement = sqlStatement
End Function
'Execute query and display results in table
Sub ExecuteQuery(SqlStatement, Connection)
Set cmdUserQuery = CreateObject("ADODB.Command")
Set cmdUserQuery.ActiveConnection = Connection
cmdUserQuery.CommandType = adCmdText
cmdUserQuery.CommandText = SqlStatement
Set rsUserQuery = cmdUserQuery.Execute
Response.Write "<table><tr>"
For Each column In rsUserQuery.Fields
Response.Write "<th>" & column.Name & "</th>"
Next
Response.Write "</tr>"
Do While rsUserQuery.EOF = False
Response.Write "<tr>"
For Each column In rsUserQuery.Fields
Response.Write "<td>" & column.Value & "</td>"
Next
Response.Write "</tr>"
rsUserQuery.MoveNext
Loop
rsUserQuery.Close
Response.Write "</table>"
Set rsUserQuery = Nothing
Set cmdUserQuery = Nothing
End Sub
-- Hope this helps. Dan Guzman SQL Server MVP "Arpan" <arpan_de@hotmail.com> wrote in message news:1126273838.396106.289280@g47g2000cwa.googlegroups.com... > Well, actually my application is at its primitive stage & I haven't > decided whether to use an application login for database access i.e. > have only 1 login name & password in the ConnectionString of the ASP > application or connect users using different login names & passwords > but one thing is for sure - users will be accessing the table only from > my application. What would you suggest? > > The bottomline is irrespective of the way I allow users to access data, > either I have to create 100 views for 100 users (if I use only one > login name & password) or create 100 login names in SQL Server & have > 100 If....Else statements in my ASP application (with the login name & > password for every user changing in the ConnectionString) & that is > exactly what I would like to avoid. I am back to square one! I need a > solution to overcome such a tedious & mnotonous approach. > > Arpan >
- Next message: Jeffrey Yao: "Question about xp_sendmail security context"
- Previous message: Kalen Delaney: "Re: Granting GRANT permissions"
- In reply to: Arpan: "Re: Permissions!"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
