Re: Security

stanw_at_bnr.ca (Stan Wolfe) wrote:
>
> I am starting to work on the port of an application from a custom
> Mac-based server to an Oracle server.
>
> Currently, the application handles all of the security (at both the row
> and column levels). If it was just a straight port to Oracle, we could
> retain a lot of the application logic to handle the security, but we want
> to open the data up to other (non-application) users using generic
> (third-party) data access tools. Obviously, we don't want the
> non-application users to get at all of the data. We want to impose a
> similar level of security on these users, as well. The reason for opening
> up the data is to allow ad-hoc queries and drill-down data access and
> analysis which the current application does not provide.
>
> We are currently considering using views to control which rows and columns
> that users can see. However, the number of users will be between 500 and
> 1000 and the number of views could go into the hundreds! Database
> administration will be a nightmare!!
>
> Any suggestions regarding the use of Oracle IDs, views and grants would be
> greatly appreciated.
>
> Stan Wolfe
> stanw_at_bnr.ca

Hi,

Currently we are using a very simple mechanism to implement similar security requirements. I am not sure it will work for you or not.

All our views are based on the tables with a correlated subquery which gives users access to only selected rows.

e.g. (a simplified example)

1. First create a table to map user's access. create table user_profile ( user_id varchar2(30); /* Oracle user id */ dept_id number; /* Department to which user has access to */
2. Now create a secure view. create view my_dept_emps select * from employees e where dept_id in ( select dept_id from user_profile where user_id = user) WITH CHECK OPTION;
3. Use oracle's ROLE features to create roles for each type of access. SECURE_ACCESS_ROLE (For limited access), QUERY_ROLE (For access to all the data).
4. Grant select, insert....etc. ON my_dept_emps to SECURE_ACCESS_ROLE. Grant select on employees to QUERY_ROLE.
5. Grant appropriate role to users. So, the users with restricted access gets access to my_dept_emps. (You can create a private synonym employees on my_dept_emps so that it is transperent to users.)
6. If you are using Dev2K, you can set the user roles at time.

This is a simple and easy to maintain implementation.

Hope this helps,

Kalpesh S. Patel

kpatel_at_row-HQ_at_smtpgtwy.row.com

R.O.W. Sciences, Inc.

301-294-5590 (T)
301-294-5401 (F)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Standard Disclaimer apply.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++