Is it possible, using database roles or any other method, to allow users SELECT access to all rows of tables, but CREATE, UPDATE and DELETE on only certain rows, depending on the data?
I need users to be able to view all corporate data, but only to be able to change data that falls under their particular department.
Thanks
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
since I'm using forms, I don't want to use views, since I would be constantly changing the base table on blocks. There are lots of tables beneath the department level that all need the same security.
if I use pl/sql routines to perform all the dml, does that mean making all my forms blocks non-base table blocks and sending all the field data to the procedures?
I hoped there would be a way to achieve this without radically changing the forms.
Actually, it sounds like the best solution might be to create a 'validation function' - in the database - that gets passed some kind of user identifier and the dept # from the table being updated. It checks the user against the dept of the row in question, and responds with 'OK, matching dept' or 'No, different dept'. You can use a single function, since this much is common to all tables. There are reasons why you might want to wrap it up in a package (security, performance), but you don't have to.
Then create before INSERT, UPDATE, and DELETE triggers (probably the 'FOR EACH ROW' variety) on each of the tables you want to treat in this manner. The trigger just calls the function, and generates an exception if the function return is 'No, different depts'.
You can define your own error numbers (Oracle has reserved a range of numbers for user-defined errors), so the error can be defined and handled by the standard Oracle error handlers in any product.
The base tables for the forms continue to be the actual tables, not views. You grant all users INSERT, UPDATE, and DELETE on every table, in addition to SELECT. The security is procedural, not declarative. The security is enforced consistently (even if a user uses Plus or some other tool; even through ODBC). These are exactly the problems that database-level PL/SQL was designed to resolve.
Business Accounts
Answer for Membership
by: frankrPosted on 1997-11-11 at 14:12:56ID: 1080793
There are a couple of ways you could approach this.
The classic approach is to grant SELECT against the table itself,
and grant SELECT, INSERT, UPDATE, DELETE against a view which
filters out only those rows in the users own dept, e.g.
SELECT tt.col1, ... , tt.coln
FROM target_table tt, emp
WHERE tt.dept = emp.dept
AND emp.lastname = USER
This assumes both the emp and target_table has a dept column, and
that you can associate the system variable 'USER' with some
column from emp... You could grant these privs to roles or to
user accounts directly.
If you're using Forms to access the data, there's still the
problem of referencing both the original table (not updateable)
and the view from the same form. Which is the base table?
It's probably easiest to dedicate one form to query-only, and
another to updates, the former using the table for a base table and the latter using the updateable view.
You could also create a database PL/SQL routine to perform all
the DML. Grant SELECT to all users, and have some stored module
perform the actual updates in proxy for the users. One of the
things it could check is that the user is in the group before
processing any updates. Then the owner of the database module
needs DML privs on the table and those who perform updates need
EXECUTE priv (the security is implemented in the code, and
enforced procedurally). This is far more flexible - there's
virtually no limit to what you could do - but it's also more
complex.