November 20, 2004 at 9:11 pm
I have four tables created that are linked by EmpID, DeptID, SecLvlID and ReceiptID. The four tables are called Employees, UserLogins, SecLevels and Receipts. I want to only allow users to view their data based upon the data fields mentioned above. The fields are as followings:
Employees:
EmplID
Last_Name
First_Name
Business_Unit
Employee_Status
DeptID...
UserLogins:
UserID
EmpNo (same as EmplID)
Password
SecLvlID
LastName
FirstName
DeptID...
SecLevel:
SecLvlID
SecLvl
Receipts:
ReceiptID
UserID
DeptID....
Data Example #1:
ReceiptID: 0001
EmplID: B5844
Last_Name: Ahumada
First_Name: Riu
DeptID: 20156
SecLvlID: 1
Data Example #2:
ReceiptID: 0002
EmplID: C4812
Last_Name: Acevedo
First_Name: Salvador
DeptID: 169365
SecLvlID: 3
Also, how can I populate the UserLogin table from the Employees table if a user name exist without having to have the information manually entered by an administrator. What I'm trying to do is if a name already exist in the Employee table, update the UserLogin table with that user's Last_Name and First_Name.
Any help would be appreciated.
November 22, 2004 at 8:51 am
Are all users employees? I see a lot of overlap in those tables in the displayed columns. You could combine the tables into one table or, if all users are employees but not all employees are users, you could just have a users table with only the security related data...
Employee
EmplID, FirstName, LastName, BusinessUnit, Status, DeptID...
Users
UserID, EmplID (FK), Password, SecLvlID...
Then you can join the two tables like...
select columns from Employee e [inner/left] join Users u on e.EmplID = u.EmplID where tbl.col = @param
By doing this you eliminate the data entry and all that is required to create a user is to create a Password, Security Level and any other security related items. You would use the inner join to return a list of authorized users or the left join to get all employees.
That will take care of the "update" problem.
As far as the data access goes, that is just setting up the appropriate parameters.
--Paul Hunter
November 22, 2004 at 9:29 am
You need to refine your first question. Are you asking about implementing security, or are you asking for a query to return data for a particular person? The query is rather simple but security is rather complex.
November 22, 2004 at 9:45 am
You need a SP that takes user's login as a parameter and returns correct result based on his credentials
November 22, 2004 at 10:55 am
In creating the stored procedure that takes a user's login as a parameter and returns the correct credential, how is this accomplished. My user login requires the user to enter their employee number and password. The first thing it must do is verify the user exist in the employee table. If the user exist, I want the name to be updated within the userslogin table. The reason this is done is that the Employee table is a real-time data based upon our PeopleSoft application that stores all active employees within our organization. If the user moves to a different division, it will automatically update their record, showing their new deptID, thereby keeping my userlogins up-to-date when users leave the department, transfer to other divisions, etc.
So, my questions again would be. I log on with my credential, i.e., emplID and password. I want it to search the Employee table to find the emplID and if found, update my userslogin with the FirstName, LastName and DeptID.
My second attempt would be to combine the ReceiptID (e.g., 0001) with the DeptID of 165325 that indicates which division based upon the emplID made the entry, when the record is pulled later.
I hope this explains it a little better. (Refer to the tables entered earlier.)
November 22, 2004 at 6:40 pm
It looks like emplID and password are the inputs to the stored procedure. You want the procedure to verify the credentials. If valid, you want the procedure to update userlogins table with data from various other tables. I can't figure out what you want for your "second attempt".
CREATE PROCEDURE usp_login
@emplID varchar(10),
@password varchar(50),
@valid char(1) output
AS
BEGIN
-- are login credentials valid?
IF not exists(
SELECT *
FROM UserLogins
WHERE empNo = @emplID
and password = @password
)
BEGIN
SET @valid = 'n'
RETURN
END
SET @valid = 'y'
-- update UserLogins table.
UPDATE
UserLogins
SET
LastName = Employees.Last_Name,
FirstName = Employees.First_Name,
DeptID = Employees.DeptID
FROM
Employees
WHERE
Employees.emplID = @emplID
and UserLogins.emplID = @emplID
and UserLogins.emplID = Employees.emplID
END
November 23, 2004 at 10:58 am
Note: Rookie
The stored procedure you provided does cover what I need. Is there away to include the SecLvlID in this same procedure?
My second attempt was to create a search screen that displays only the receipts entered by a user based on their emplID and deptID. I don't want another user to view my data if they are not assigned to my department based upon their deptID, so if user #1 is assigned to deptID: 161356 and user #2 deptID is: 251065, the search form should only show receipt based upon my deptID: 161356 when I logon as user #1.
November 23, 2004 at 4:59 pm
You need more details.
These appear to be basic sql questions. I recommend reading a book or taking a course.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply