Re: Oracle data security question
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 DBAReceived on Sun Aug 13 1995 - 00:00:00 CEST