Hi,
I'm writing an application that involves data that has a set of users
that are allowed to perform certain operations on it.
i.e. Only the row owner can modify a row, but there is a set of users
who can view it.
At the moment, I've started to implement this by calling a UDF at the
beginning of each stored procedure that validates that the user is
allowed to call the procedure on that particular row (trusting a higher
teir to verify the user), and throws an error if they are not.
I don't particularly like this solution, as I need a UDF for each
procedure, and will have to re-write the udf's if the access rules
change (which they might).
Can anyone suggest a method of implementing a more generic row
permissions system?
Cheers,
Ben
5  1383 
"Bomza" <be******@hotma il.com> wrote in message
news:11******** **************@ c13g2000cwb.goo glegroups.com.. . Hi,
I'm writing an application that involves data that has a set of users
that are allowed to perform certain operations on it.
i.e. Only the row owner can modify a row, but there is a set of users
who can view it.
At the moment, I've started to implement this by calling a UDF at the
beginning of each stored procedure that validates that the user is
allowed to call the procedure on that particular row (trusting a higher
teir to verify the user), and throws an error if they are not.
I don't particularly like this solution, as I need a UDF for each
procedure, and will have to re-write the udf's if the access rules
change (which they might).
Can anyone suggest a method of implementing a more generic row
permissions system?
Cheers,
Ben
Unfortunately there's no built-in or generic solution, so you need to
implement something yourself. Here's an alternative view-based approach,
which might give you some more ideas: http://vyaskn.tripod.com/row_level_s..._databases.htm
Simon
Views are of good use when situations like this.
In your table create a field called userID and assing the userID to
each row that has modifying permission. Create a view like this for
egsample.....
Create view <name>
AS
Select col1, col2, col3
from <table>
where userid = sUser_sName().
Then update the table by updating this view so that only those records
that are visible to that user can update these records on the
destination table.....!
Also create another view where userID<> sUser_sName() and grant only
view permission to the users... So they cant update the table thru this
view...!
This is just a guess. I have not tried it myself. So feel free to
comment on this..!!
Good luck..!
They're both interesting ideas, and are probably a fair bit faster than
the way I came up with. I like the idea of being able to throw an
error when a client does something illegal rather than just doing
nothing and as I'm using ADO.NET, my "authorisin g" UDF method provides
a neat way of doing it. At worst the UDF does an EXISTS on a SELECT
statement, so its not a totally horrible method.
It must be a fairly common problem, so when I'm finished I might turn
it into a generic framework where you can grant and evoke various
permissions to any users on any rows, but at the moment I've just got
to get this working :) I think its all been addressed in SQL 2005
anyway...
Bomza (be******@hotma il.com) writes: It must be a fairly common problem, so when I'm finished I might turn
it into a generic framework where you can grant and evoke various
permissions to any users on any rows, but at the moment I've just got
to get this working :) I think its all been addressed in SQL 2005
anyway...
Not really. There is no particular support in SQL 2005 for row-level
security either.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
"Bomza" <be******@hotma il.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .
<<>> It must be a fairly common problem, so when I'm finished I might turn
it into a generic framework where you can grant and evoke various
permissions to any users on any rows, but at the moment I've just got
to get this working :) I think its all been addressed in SQL 2005
anyway...
I usually address this in the front end design.
Something like.
All users see everything in one screen is read only.
There are links butons or whatever take them to another screen to do
updates.
The update screen is only available to supervisors or when you pick data is
maintained by your team.
This does of course rely on users not trying to break the system by
connecting up using odbc or something.
But.
I suspect others use the same sort of approach and that's why there aren't
loads of solutions available.
--
Regards,
Andy O'Neill This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Jan Waga |
last post by:
Hello,
Is there some way to control SCM timeout while starting or stopping a C#
windows service? I know I can work on a different thread and quickly return
from OnStart(), but I'd like my service to shutdown gracefully, and it takes
a long time to save all data while exiting.
Thanks for any help,
Jan
|
by: Ken Bass |
last post by:
Hi all,
I have a client that is obsessed (actually paranoid) with security.
One requirement he has is that any data deleted from a database be
physically removed. This means not only within the scope of the
database itself, but at the file level itself.
For example, if a row in a table has a value of "foobar", and that row
is deleted, then the string "foobar" must not be found within any of
the database files, or even on the disk. If...
|
by: pb648174 |
last post by:
I have a very long transaction that runs on the same database that
other users need to use for existing data. I don't care if they see
data from the transaction before it is done and am only using the
transaction because I need a way to roll it back if any errors happen
during the transaction. Unfortunately all tables affected in the long
running transaction are completely locked and nobody else can access
any of the affected tables while...
|
by: Martien van Wanrooij |
last post by:
I have been using in a lot of websites a script that creates a menu bar but
avoids that the button to the current page can be clicked.
I am rather satisfied with it (although suggestions for better ways of doing
things are welcome, the script follows below FYI)
No there is a a problem that for a new site (www.orkestbasic.nl/nieuw) the
menu with the links has to be placed in a separate frame because the right
part of the page must be...
|
by: Cigar |
last post by:
I am developing a program for a client. She runs a shop where her
clients bring in items for sale or short term buyback. Development of
the program has been going great but she's mentioned that there is a
'feature' coming up in the next couple of weeks that she'd like me to
implement that has me a bit worried.
My client has told me a story of how she hired someone from a competing
store and that person had brought a copy of the program...
| | |
by: Andrey Mosienko |
last post by:
We are using PostgreSQL about two years beginning from version 7.0.
I have one question about starting postmaster:
Is there way to detach it from the controlling terminal under FreeBSD?
My situation: I start postmaster manually:
su pgsql -c "/usr/local/pgsql/bin/pg_ctl
-D /usr/local/pgsql/data -l /usr/local/pgsql/log/pgsql.log start"
Log:
|
by: John Baker |
last post by:
Hi:
At the outset let me admit that I screwed up!
I have built a rather elaborate set of forms and sub forms starting with a client table,
and going down to PO and Line item. This works very well EXCEPT that when I started the
design I failed to consider that client records would be added to the client table in
random order. The table is indexed on client ID which is an auto number field, and as a
result the table is in random order,...
|
by: Stephan Tobies |
last post by:
Hi everyone,
I am looking for a good data structure that could be used to represent
families of trees with shared sub-trees and copy-on-write semantics.
On a very abstract level, I would like to have an API like this:
Let Node be a suitably defined data structure. Then the following
functions shall be supported:
|
by: GaryDean |
last post by:
We have been noticing that questions on vs.2005/2.0 don't appear to get much
in answers so I'm reposting some questions posted by some of the programmers
here in our organization that never got answered...
In 1.1 we always did our own myDataAdapter.fills and we liked that control
for lots of good reasons. Now the new DataSource (or is it a
TableAdapter:Dataset) automatically fills the Gridview. How can we control
that fill?
In a...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
