Suppose we have a web-app where we use J2EE security and set up roles for users. And say we have the following roles: update, read-only. If the user has the update role, the update buttons would be enabled. If they have read-only, the update buttons would be disabled.
Now suppose that we have a data level security policy that says 'user A can update rows 1-100' and 'user A can read 101-200'.
How do we handle the data level security with the J2EE security policy? Do we give them 'update' and 'read-only' roles and then programatically limit what the user can do to the specific rows? That is, depending on the row they are currently working, we would enable/disable update buttons.
- Data Level Security by Paul Strack on March 29 2004 21:23 EST
- Data Level Security by Vijaykumar Natarajan on March 29 2004 23:26 EST
- Data Level Security by Sean Cohan on April 06 2004 17:43 EDT
- Data Level Security by Sean Cohan on April 02 2004 13:01 EST
- Data Level Security by Sean Cohan on April 04 2004 20:55 EDT
- Data Level Security by Erik Sliman on April 07 2004 04:40 EDT
- Data Level Security by Paul Strack on March 29 2004 21:23 EST
In order to manage this, you need to associate users to records in your database. The easiest way to do this is to have your user information in the database as well, and make it a foreign key in your tables. Many J2EE servers support storing user data in a relational database, so this may be fairly straightforward.
Once you have the database associations, you can make the userid an additional clause in your selects and updates:
SELECT * FROM TABLE WHERE [general conditions] AND USERID = ?
UPDATE TABLE SET FIELD = ? WHERE PRIMARY_KEY = ? AND USERID = ?
For the second SQL, you can check how many records were updated, using the return value of executeUpdate(), and generate and error message if no records were updated: "You do not have permission to update Record # ...".
If you are using some sort of Object-to-Relational mapping (CMP EJB, JDO, EJB), the same basic principle applies.
As a final note, you will generally want to associate users with the records they create, and you may also want "super-users" that can view/change everything.
I would suggest...
1. If at all possible, disassociate your security policies from the data. This way, they can change independent of the data itself. This means unless each row has a specific security policy, try to define common rules that affect sets of rows.
2. Check security policy before you attempt data access. For example, you've to do this for a super user check. ie. if superuser, skip checking etc. This allows you to predetermine (for example) if the update button should be enabled or not.
3. Use role based access control. Role based access control allows the actual user to change w/o causing updates all over the place (eg. the superuser was X today, tomorrow it is Y and Z..or K is no longer w/ the company, so L is taking over all her work). You can do this either by defining additional roles at the J2EE level (superuser, manager, for eg) and managing the role membership by using a database (as suggested in the previous post, except I'd use roles instead of userids and would move that information to a separate table(s)) to define your own roles and role membership rules and checking for membership in code.
Note that using the J2EE facilities keep the security policy implementation details out of your application and this allows you to change how you do it w/o affecting the application itself.
I think you shoould follow Vijaykumar Natarajan's recomandations except that:
the roles should not be J2EE roles, but application specific roles. This is because the problem you are describing is an application specific one. The J2EE roles are supposed to be used in order to grant java code permissions (what bean is allowed to invoke other bean, etc).
Best regards, Mircea
If you want a 'generic' or close to a generic solution, here is a sketch: Define a permission as a tuple (role, resource, operation). This can be easily mapped to a table. The 'role' is the role I have mentioned before, the resource could be the id of the your database row and the operation could be 'READ', 'UPDATE', 'DELETE' or whatever you need. If yoiu need to define permissions on various types of resources you could extends the format of the permission or make some conventions 'permission' = 'trype' +'.'+ 'id'. You can further extend this by imposing some hierachical organization of resources like:
"organization", "organization.departments", "organizations.departments.accounting", etc. You could also use some wildcards on the permissions; for example a permission like ('acount manger' 'organization.departments,accounting', '*') would mean that every user that is an 'account manger' vcan perform any operation on the 'organization.departments,accounting' respurce. please let me know if I am 'running wild' and I am not clear enough.
Best regards, Mircea
There's a problem with my proposed solution. If each DB user does not have their own oracle id, a malicious dba can come in through the shared account and wreak havoc. This will not fly with the client. We must not allow this. But I don't know how to support connection pooling and prevent the malicious dba from acessing the system 'anonymously' via the shared account.
Frankly, their concerns are bogus. A malicious DBA with admin rights on the database can wreak havoc on the data whether the users have their own oracle id or not. They may have additional and valid concerns, though, such as maintaining a proper audit trail in the database.
If they insist on users having their own oracle ids, you have to abandon connection pooling, because each connection will be associated with a particular user and cannot be reused. Basically you have to:
1) Log the user in to the web layer.
2) Cache the user id and password (e.g. in the session)
3) Recreate the connection every time they interact with the database, using the right userid and password.
Tell them if they want the improved level of security, they will pay for it in reduced performance. This may get them to reconsider.
Maybe you can configure the databse such that, for the shared user, it accepts connections that come only from the J2EE server IP.
Best regards, Mircea
The challenge is that roles alone doesn't seem to do the trick. Each user can be given access to a set of rows. However, another user could be given access to some or all of those same rows (and even more rows than that). We'd have to create a unique role for each unique range of rows given to a user, which doesn't fit nicely here.
The current data level security implementation is handled within the DB. A table stores the rows a user is allowed to access. And then each SQL where clause is appended with the rows (primary keys) the user is allowed to work on. I'm trying to fifure out if there is a way to move this out of the DB or at least not be tied to the DB.
I hate to say this, but I have never found a more effecient way to do data-level security other than through the database. At some point you have to make some kind of association between rows and users. If those associations are done through the database itself, you have a hope of optimizing your queries.
If those associations are outside the database (e.g. in a configuration files or something), you would have to do a lot of weird convolutions to make things work, and performance will suffer.
Given your specialized constraints, you *might* be able to do something outside the database, provided that your business rules are "exactly one user will be able to edit a particular set of data". If that is the case, you might be able to use some sort of algorithm like assigning blocks of primary keys based on userid, os that user 14 get primary keys 14000-14999.
This is pretty brittle, though, will certainly break if your business rules every change. I would stick with the database associations, myself. It sounds like the queries you are using could be better optimized, though.
I think you're right Paul. Since multiple users can be granted access to modify the same table rows, associating users to rows is probably best done in the DB. I'm sure that's what the group of developers concluded when they implemented the current solution. I don't have a crystal clear idea of how sql statements are qualified. They may have a more optimal solution that what I described. Somehow they qualify sql statements based on the rows a user is allowed to access.
The one thing they did that needs to be refactored is they gave each app user a unique oracle id. We're transitioning to web apps and as it stands, we can't take advantage of db connection pooling unless we refactor to a shared oracle id.
To support a shared connection, the current oracle security policy will have to be changed so that instead of qualifying sql based on the oracle session user id, we'll have to qualify based on an app user id. I think we can set that app userid in a package variable prior to issuing sql statements. I need to sit down with the oracle expert who implemented this security policy to get the full scoop.
Any additional thoughts are extremely welcome as this DB has a large number of tables and a boat load of money has been spent on it and the applications that use it. And a boat load will be spent on it in the upcoming years.
Can you give an example of the business rules for determining the rows a user can access?
Traditionally, this was performed using context aware database views, which requires the user of individual database user IDs. This is contrary to one of the benefits of a scalable multi-tiered solution, connection pooling. Thus, if you plan to support a large number of users, I wouldn't consider it an option.
In version 1.4 of JoshuaBranch AS (JB-AS), we plan on permitting applications to authenticate, giving them the ability to directly manipulate security data for the application. This opens the doors to new possibilities which might better address your needs, but depends on how you define the users a set of rows can update. (In version 1.2 only Administrators can dynamically update security data via a web console. The applications authenticate users, and inquire on their access to modules, but cannot create or update security objects.)
In JB-AS, anything you grant access to is called a module. Thus, in your case, you would need a module for a group of roles. With version 1.4, you may be able to automate the creation of the modules, and then have two choices.
One choice is to use a foreign key in your tables to group the rows. This method is ideal of your users are grouped are logically. That is, you'll have 10 users in a group that need to access rows 1-100.
Another choice is to use naming conventions for dynamically created modules, and then parse them. Thus, a module name could indicate the rows of data it represents:
(rows 100 through 200 of the CUSTOMERS data)
When a role is given access to a module, you can turn on/off four privileges: Create, Retrieve, Update and Delete (CRUD). Thus, you have your update indicator you suggested. In other words, you could check to see if the user who has access to the "CUSTOMERS 100,200" has update access.
This gives you a completely programmatic solution without having to include anything in your database to support it.
If you are interested in beta testing the programmatic interface for version 1.4, let me know. I'm looking for beta testers that fit certain criteria for new features coming out.
http://as.JoshuaBranch.com application security today