Setting application users permissions on specific records.

[nokia33948]

Hi there,
I am writing a VB.NET 2005 application that connects to an MySQL DB
(it could be MS SQL, one day...). One of the window forms contains a
gridview with some rows coming from a table on the DB, basically,
phisical addresses of contacts. I would like to implement a system to
grant/deny read/write permissions on specific rows, so that if the
user, after the log in, has got the necessay privileges he actually
can see/modify some rows, if he hasn't the rows will not even be shown
to him.

I can't think of an easy way to implement that, considering also that
I would like to set persmissions also at group (of users) level.

Any idea would be appreciated.

Regards,
D.

 

[Chris Dunaway]

Hi there,
I am writing a VB.NET 2005 application that connects to an MySQL DB
(it could be MS SQL, one day...). One of the window forms contains a
gridview with some rows coming from a table on the DB, basically,
phisical addresses of contacts. I would like to implement a system to
grant/deny read/write permissions on specific rows, so that if the
user, after the log in, has got the necessay privileges he actually
can see/modify some rows, if he hasn't the rows will not even be shown
to him.

I can't think of an easy way to implement that, considering also that
I would like to set persmissions also at group (of users) level.

Any idea would be appreciated.

Regards,
D.

I don't know very much about MySql, but if I were using Sql Server, I
would create a View that selected just the rows needed and then set
permissions on the view as appropriate and then deny permissions on
the table itself. The queries would all query the view. I don't know
if MySql supports views or not, but that is the easiest way I know to
do it.

Chris

 

[nokia33948]

I don't know very much about MySql, but if I were using Sql Server, I
would create a View that selected just the rows needed and then set
permissions on the view as appropriate and then deny permissions on
the table itself.  The queries would all query the view.  I don't know
if MySql supports views or not, but that is the easiest way I know to
do it.

Chris-

Hi Chris,
thank you for your interested: mySQL supports views, almost the same
way as SQL Server; but how could I tell the system that user A can see
row 1 and 3, user B can see all the rows (1,2,3), user C cannot see
any?

Regards,
D.

 

[Lloyd Sheen]

I don't know very much about MySql, but if I were using Sql Server, I
would create a View that selected just the rows needed and then set
permissions on the view as appropriate and then deny permissions on
the table itself. The queries would all query the view. I don't know
if MySql supports views or not, but that is the easiest way I know to
do it.

Chris-

Hi Chris,
thank you for your interested: mySQL supports views, almost the same
way as SQL Server; but how could I tell the system that user A can see
row 1 and 3, user B can see all the rows (1,2,3), user C cannot see
any?

Regards,
D.


What is the data in the rows. SQL has no concept of row 1 etc. When you do
a query without an order by the order of the rows returned could be
different each time. You will want to put security on according to the
data. If there is no data that determines who should view what then you had
best add that to your tables.

LS

 

[Chris Dunaway]

Hi Chris,
thank you for your interested: mySQL supports views, almost the same
way as SQL Server; but how could I tell the system that user A can see
row 1 and 3, user B can see all the rows (1,2,3), user C cannot see
any?

Regards,
D.

Create one view that selects rows 1 and 3 and grant user A permission
to query from that view, create another view that selects all the rows
and grant user B permission to query from that view. Deny all users
permissions on the table and force them to query the views only. User
C will not be able to see any because she can't access either of the
views or the table.

Chris

 

[nokia33948]

What is the data in the rows.  SQL has no concept of row 1 etc.  When you do
a query without an order by the order of the rows returned could be
different each time.  You will want to put security on according to the
data.  If there is no data that determines who should view what then youhad
best add that to your tables.

LS

Hi Llyod thank you for your reply.

When I wrote row 1, row 2, etc. I was just making examples. I have got
one suggestion to add a field "Role" to each row to determine which
user can see a specific row and which one cannot. Then the stored
procedure retrieving the data will filter on the "Role" field. What do
you think?

Regards,
D.

 

[nokia33948]

Create one view that selects rows 1 and 3 and grant user A permission
to query from that view, create another view that selects all the rows
and grant user B permission to query from that view.  Deny all users
permissions on the table and force them to query the views only.  User
C will not be able to see any because she can't access either of the
views or the table.

Chris- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -

Hi Chris,
as I wrote to Lloyd, I have got been suggested to add a field "Role"
to each row to determine which user can see that row and which one
cannot. Then the stored procedure retrieving the data will filter on
the "Role" field. What do you think?

Regards,
D.