Re: Oracle data security question

From: Jim Smith <jim_at_jimsmith.demon.co.uk>
Date: 1995/08/13
Message-ID: <808300819.11260_at_jimsmith.demon.co.uk>#1/1


fgregad_at_deakin.edu.au (Greg White) wrote:

>Hello,
 

>We are in the early stages of the design phase of a new system (using Oracle
>V7.1.6, Designer 2000, Unix platform).
 

>We want to limit groups of users to restricted sets of data. We have
>contemplated using VIEWS, but given the limitations associated with them (ie.
>unable to INSERT, UPDATE or DELETE where the view's query contains table
>joins, etc), they don't appear to be a satisfactory solution.
 

>Apart from including user and/or role checks in the applications to restrict
>the availability of data, are there any other means of achieving this ?
 

>Also, if we restrict users to particular data sets at the application level
>(by granting privileges to tables and enforcing whatever means to control the
>data they can access), but they are then able to access SQL*Plus, they would
>have access to all of the data in the tables on which they have SELECT
>privileges.
 

>We could use the PRODUCT_USER_PROFILE table to deny them SQL*PLus access,
>but if we want them to have SQL*Plus access, we have a security problem.
 

>Any suggestions or comments would be appreciated.
 

>Thanks,
>Greg.
 

>************************************
>Greg White
>Deakin University
>Victoria, Australia.
>e-mail gregad_at_deakin.edu.au.
>************************************
>

You can get round the updatable view problem by using views as follows.

create view restricted_table
as
select *
from base_table
where base_table.group_code in (select groups.group_code from groups
where groups.usr_id=USER)
with check option

Assuming records in base table can be assigned an 'owner' group which can be used to restrict access.

You will also need a pre-insert trigger on the base table to insert the group_code.

The security problem with uncontrolled tools like sql*plus can be dealt with by assigning each user two roles. A restricted role which is enabled by default and a full access role which is enabled explicitly by your application at connect time.

Some illustration.

CREATE ROLE FULL_ACCESS IDENTIFIED BY PASSWORD; GRANT ALL ON TABLE1 TO FULL_ACCESS;
GRANT ALL ON TABLE2 TO FULL_ACCESS; CREATE USER JIM....
GRANT CONNECT TO JIM;
GRANT FULL_ACCESS TO JIM;
ALTER USER JIM DEFAULT ROLE ALL EXPECT FULL_ACCESS; Now when jim logs on using an "unauthorised" means, the only active role is connect, which gives no access to tables.

Your application should issue a 'set role full_access identified by password' command at connect time. This will enable the role for the current session.

Jim Smith                      jim_at_jimsmith.demon.co.uk
Oracle DBA                     
Received on Sun Aug 13 1995 - 00:00:00 CEST

Original text of this message