reassign privileges

[j2willi4]

We have a database that has several applications accessing data from it. We want to clean up the privileges that had been assigned to the public role when the developers created those apps by reassigning them to application roles all users of the apps will belong to.

we aren't taking any privileges away from the users of the application. but as a security measure, want to limit the public role.

typically speaking, would moving privileges around to different roles have any effect on the application itself?

If we carry through with this we will obviously conduct thorough testing... just looking for some insight.

thanks a bunch!

 

[SQLBill]

Depends on how the app functions. Some apps change the permissions as part of their function. For example: I log in to the app as SQLBill and enter my password. The app might then pass my query on to the database by using a different login. So, changing the permissions to the logins used by the users may not work. You first need to find out what login the app uses to connect to the database. Is it the users' or a different login?

-SQLBill

Posting advice: FAQ481-4875

 

[j2willi4]

well... my understanding of it is (and i could be wrong... i'm a novice) the users log into the application... the application allows the user to connect and query the db using a generic application login...

so the login to the database has 2 roles providing privileges. 1) the applicationUser role. 2) the generic public role.

The public role has a few grants to business data that raises the level of risk to the data's security & confidentiality, so we revoke those privileges from public and grant them to the applicationUser role.

did i break anything?

 

[SQLBill]

It's hard to tell since I don't have the vendor's code. Ask them how their app uses public. Tell them your concern with security and that you are trying to secure the data as best you can and you noticed that public is being used. If you work with them politely, they may be willing to change their method or at least tell you what functionality you might lose.

Remember, don't just tell them it's bad security. Say you are looking into providing better security and you noticed this.

-SQLbill

Posting advice: FAQ481-4875

 

[j2willi4]

thanks sqlbill!