Re: Q: Column Level Security
Date: 24 Feb 95 15:22:48 GMT
Message-ID: <3iktk8$9kp_at_alterdial.UU.NET>
jpadilla_at_epix.net wrote:
>
>
> Hola!
>
> I am working in a application in which we need to be able to enforce column
> level security dynamically. What I mean by dynamically is that we will be
> having a pool of modules that we need to assign to users depending on the
> user position, etc. The problem we have is due to the use of ODBC in some
> of our clients. We need to be able to restrict in some way access to the server
> or allow users to see just a selected information in the database. We need
> this because of some business rules that are enforced with our application.
>
> etc...
>
I'm not sure if I get the question correctly, but what I think you are asking is 'Is it possible for user A to see all rows in a table, and user B see only a subset'. If so the following is a possible solution...
(1) Create a user_group table. The records in this table would be
ADMIN, SALES etc or whatever business groups you desire.
(2) Create a user_groups table that for a selected user has a
number of entries - the groups that that user may access
(3) For each table that you want to restrict access to, add a link
to the user_group table
(4) Create a view for each table that uses the user_group entries
to limit access to the data.
The tables/views are as follows...
CREATE TABLE USER_GROUP
(IDENTITY VARCHAR2(20),
DESCRIPTION VARCHAR2(255));
CREATE TABLE USER_GROUPS
(USER_GROUP VARCHAR2(20),
USERNAME VARCHAR2(30),
CONSTRAINT PK_USER_GROUPS PRIMARY KEY (USER_GROUP,USERNAME),
CONSTRAINT FK_USER_GROUPS_GROUP FOREIGN KEY (USER_GROUP)
REFERENCES USER_GROUP (IDENTITY));
ALTER TABLE whatever
ADD USER_GROUP VARCHAR2(20)
ADD CONSTRAINT FK_whatever_USER_GROUP FOREIGN KEY (USER_GROUP)
REFERENCES USER_GROUP(IDENTITY);
And finally the view
CREATE OR REPLACE
VIEW whatever_VIEW AS
SELECT * FROM whatever
WHERE ((USER_GROUP IS NULL) OR
(USER_GROUP IN (SELECT USER_GROUP FROM USER_GROUPS
WHERE USERNAME = USER)));
Now whenever you select from the view, the group security will take effect. Note that if you want all users to access a particular row, simply set the USER_GROUP value to NULL.
Hope this clears up your question. Received on Fri Feb 24 1995 - 16:22:48 CET