CODEWITHSUNDEEP
c#sql-servervisual-studio
Tomislav Bijelic
Tomislav Bijelic

Reputation: 23

How to set restrictions in code based on database privileges

I am making a auction aplication (WPF) in Visual Studio that needs to be connected with a database created in SQL Server 2014. This aplication is part of an assignment so I have to follow some rules.

The one I need help with is about setting restrictions based on a role. I the database, I have a table with a column isAdmin (value 1 if the user is admin or 0 if not). In Visual Studio I need to create an UI where users can log in before they access the main auction window. In the database I have also the list of users (each have a user name and password that they need to use to log in and the value of the isAdmin).

When they log in as admin, they have access to button controls for adding or removing items from the auction; when logged as a user they can only bid; guest can only view the auction.

I need help how to write the code so that the users can log in and based on their isAdmin value and have according access to the buttons in the main auction window.

Upvotes: 0

Views: 356

Answers (1)

CodyMR
CodyMR

Reputation: 415

There is a couple ways you could do this. I am going to do it using one query so it might seem a bit weird...

We are going to have a function that will manage everything...

public bool login(string user, string password, ref string isAdmin) { }

This will be the function I will use. Inside the function we are going to now access the database and check to make sure the person has entered correct credentials. I am going to assume you have already set up your SQL connection (which I will be calling myConnection...if you need help with that just comment saying so).

public bool login(string user, string password, ref string isAdmin){        

    //This is used so we know if we entered the while loop...
    bool success = false;

    //This is what I am going to use to execute commands
    myCommand = new SqlCommand("SELECT isAdmin FROM users WHERE user = @user AND pass = @pass", myConnection);
    myCommand.Parameters.Addwithvalue("@user", user);
    myCommand.Parameters.Addwithvalue("@pass", password); 

    try
    {
        //Open connection
        myConnection.Open();

        //Execute the reader
        myReader = myCommand.ExecuteReader();

        //Read the data now
        while(myReader.Read())
        {
            //isAdmin could very well be an int and myReader could convert to int instead of string
            isAdmin = myReader[0].ToString();   

            success = true;
        }

        //Close connection
        myConnection.Close();
    }
    catch
    {
        //Error checking
        myConnection.Close();
        return false;
    }

    if(success)
    {
        //Login worked - credentials were right
        return true;
    }
    //Login failed - credentials were wrong
    return false;
}

Now you have all the information you need. If returned value is true then you know the user is logged in and you have the isAdmin value from the ref string in the function. Pretty easy from here. Disable or enable buttons accordingly.

EDIT 1

This is to answer your comment -

This is of great help! However, I need some more help with permissions. Say now that "User X" is logged (for example without admin rights) and views the auction main window. I tough to insert the Panel Control and set the visibility to false in this case. How can you determine the current user rights? I also tough to add a column in users table with ID key so it simplifies the referencing. Do you need to write a function to determine the current user rights or can you use a simple IF statement in order to set the Panel visibility? – Tomislav Bijelic 13 hours ago

Yes you could use an IF statement. The function gets you the 1 or 0 (admin or not admin) on the line isAdmin = myReader[0].ToString();. Since isAdmin is ref it will be automatically saved into whatever you used in the function call. If you want to access it in another form you can just pass the variable through when you declare a new "form" to open it. An example of what I mean would be this...

///////////////AUCTION MAIN WINDOW START///////////////////////
//Global variable
string strIsAdmin;

public frmAuctionMainWindow(string isAdmin)
    {
        InitializeComponent();

        strIsAdmin = isAdmin;
    }
//strIsAdmin tells you whther or not they are admin so on form load just make them visible or not visible accordingly...

private frmAuctionMainWindow_Load(object sender, EventArgs e)
{
    if(strIsAdmin == "1")
    {
        //User is admin
    }     
    else if(strIsAdmin == "0")
    {
        //User is not admin...(could use else statement if only logged users are visiting this form
    }
}


///////////////AUCTION MAIN WINDOW END///////////////////////


///////////////LOGIN FORM START//////////////////////////////

    //Button could only be visible to people who have access (logged in users)
private void btnOpenMain(object sender, EventArgs e)
{
    frmAuctionMainWindow main = new frmAuctionMainWindow(isAdmin);
    main.Show(); //Or main.ShowDialog(); depending on what you want

}

///////////////LOGIN FORM END////////////////////////////////

If you want everyone to be able to access Main Auction Window then you will want to send in another variable telling the form whether or not the user is logged in. An easy fix for the guest is have a separate form that is view only and one form that is for logged in users only.

Edit 2

Also like what another user said you should do your DB access in the backend. You could make a class that does everything with the DB...read/write etc...

Upvotes: 1

Related Questions