secure a procedure

[gogetsome]

Hello, I have a button when clicked executes a stored procedure and populates a window and grid. I want to be able to check the user against a table and only allow the procedure to run if the user is in the table. Any Ideas how I can accomplish this?
Here is the code I'm working with:

Private Sub PushButtonM162_AfterUserChanged()
On Error GoTo ErrorHandler

CancelLogic = True

Dim userinfo As New RetrieveGlobals.retrieveuserinfo

Dim adCNN As ADODB.Connection
Dim adrst As ADODB.Recordset
Dim strcmd As String
Dim strCnn As String

Dim response As Variant

sqluid = userinfo.retrieve_user()
sqlpwd = userinfo.sql_password()
SQLdb = userinfo.intercompany_id()
sqlsvr = userinfo.sql_datasourcename()

With CLIN_Filter
.User = sqluid
.password = sqlpwd
.SERVER = sqlsvr
.DataBase = SQLdb

.Filter_Type = "CLIN"
.Height = 7000
.dHeight = 3750
.Width = 8500
.dWidth = 7800
' .blindLU = True
.addSorts = 2
.reportCount = 4

'.param = SalesTransactionEntryDetail.ItemNumber.Value
.sp_Name = "csp_ws_lookup_item_clin_Only70"

response = .load_view

CLIN_Filter_RowReturned response

End With

Exit Sub

ErrorHandler:

Exit Sub

 

[Borvik]

I've got an idea, that I've been playing with and seems to work.

qlpwd = userinfo.sql_password()
SQLdb = userinfo.intercompany_id()
sqlsvr = userinfo.sql_datasourcename()

I don't know if there is a way to get what you're looking for with this - it would be great if you could I'd use it too. You should be able to get the UserID at least, so you can do user lookups in the database.

You will need the Toolbar window added to VBA, as well as the User ID field - which for me shows up as eEnterpriseUserID (I'm using GP 7.5 by the way).

Then in your code do something like this:

Private Sub Window_BeforeOpen(OpenVisible As Boolean)
    Dim UserID As String
    UserID = Toolbar.eEnterpriseUserID
    'Open your connection and do queries
End Sub

Hopefully that will help get you on the right track.

 

[hs1]

There are two ways of achieving waht you want:
1. Each GP user has a SQL user account and you could deny permissions to that user- you could use enterprise manager or use tsql to Revoke access
The disadvantage is that you will have to trap for that error in VBA

2. Maintain your custom table. I dont suppose you'll update this table too often, and you could view it in Gp if yo have SLBuilder.

Next, Modify existing proc, pass in the userid you retrieved form retrieveglobals
in the proc you have a line
if exists (select name from mytable where name = @name)
begin
<processing code>
end

HS