Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: record level permissions

Re: record level permissions

From: Jurij Modic <jmodic_at_src.si>
Date: Wed, 02 Dec 1998 21:53:26 GMT
Message-ID: <3665b608.9528640@news.arnes.si>


On Wed, 02 Dec 1998 14:53:35 GMT, rosek_at_my-dejanews.com wrote:

>Here is my problem.
>
>I have several flatfiles that I want to load into the same database table.
>However each flatfile is owned by a different user. I would like to allow
>users to only be able to access their own data within the table.
>
>I know I could replicate the data for each user or I could include an
>attribute to denote ownership.
>
>However, I was wondering if Oracle 8 Enterprise Server supported some form of
>record level permissions - where I could permit a user to access a record or
>group of records within a table.
>
>Im looking for the following? 1. Is this possible - is there a built in
>method for this. 2. What Oracle documentation would describe this feature. 3.
>Any examples or insight into this problem. 4. What tool I can use to
>manipulate these permissions ? ie Enterprise Manager? - can I assign the
>permissions on load with SQL Loader ? Thanks Kevin Rose

That's (among other things) why VIEWs are for in SQL.

Supose your flat files have the data for three columns: c1 VARCHAR2, c2 NUMBER, c3 DATE. Here is what you should do:

CREATE TABLE my_table (c1 VARCHAR2(20),

                       c2 NUMBER,
                       c3 DATE,
                       rec_owner VARCHAR2(30) DEFAULT user);

The last column will record the oracle user that inserted the record. Now create a view based on that table:

CREATE VIEW my_view AS SELECT c1, c2, c3 FROM my_table WHERE my_table.rec_owner = user;

Now grant select, insert, update and/or delete privileges on MY_VIEW to your end users. They will have no privileges on your MY_TABLE, but they will be able to manipulate its records through MY_VIEW. And any manipulation (select, update, delete) will be restricted only to records the particular user have inserted, excluding any records from other users.

Your users will also be able to load records to MY_TABLE with SQL*Loader through MY_VIEW - again rec_owner column will automaticaly record the oracle username connected with sql*loader.                        

HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)



The above opinions are mine and do not represent any official standpoints of my employer Received on Wed Dec 02 1998 - 15:53:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US