Re: record level permissions

From: Andrew Protasov <oracle_at_protasov.kiev.ua>
Date: Wed, 2 Dec 98 22:25:34 +0200
Message-ID: <AL-8QPsK42@protasov.kiev.ua>

Hi,

Add column to your table:

username varchar2(30)

Fill it with user on insert. Create view with check option limited on username. Grant access on the view to users. Do not grant access on the original table. All users must work only with the view.

SVRMGR> drop table test_perm;
Statement processed.

SVRMGR> create table test_perm
     2> (id       integer
     3> ,name     varchar2(30)
     4> ,username varchar2(30) default user
     5> );

Statement processed.

SVRMGR> create or replace view v_test_perm
     2> (id
     3> ,name
     4> )
     5> as
     6> select id,name
     7> from test_perm
     8> where username=user
     9> with check option;

Statement processed.
SVRMGR> drop public synonym v_test_perm; Statement processed.
SVRMGR> create public synonym v_test_perm for v_test_perm; Statement processed.
SVRMGR> grant select,insert,update,delete on v_test_perm to public; Statement processed.
SVRMGR> connect tester/tester
Connected.
SVRMGR> insert into v_test_perm(id,name) values(1,'SMITH'); 1 row processed.
SVRMGR> insert into v_test_perm(id,name) values(2,'BLACK'); 1 row processed.
SVRMGR> insert into v_test_perm(id,name) values(3,'BROWN'); 1 row processed.
SVRMGR> commit;
Statement processed.
SVRMGR> select * from v_test_perm;
ID NAME

---------- ------------------------------
         1 SMITH
         2 BLACK
         3 BROWN

3 rows selected.
SVRMGR> connect safemaker/safemaker
Connected.
SVRMGR> insert into v_test_perm(id,name) values(4,'CLARK'); 1 row processed.
SVRMGR> insert into v_test_perm(id,name) values(5,'JACKSON'); 1 row processed.
SVRMGR> insert into v_test_perm(id,name) values(6,'PATTON'); 1 row processed.
SVRMGR> commit;
Statement processed.
SVRMGR> select * from v_test_perm;
ID NAME

---------- ------------------------------
         4 CLARK
         5 JACKSON
         6 PATTON

3 rows selected.
SVRMGR> connect prot/prot
Connected.
SVRMGR> insert into v_test_perm(id,name) values(7,'GATES'); 1 row processed.
SVRMGR> insert into v_test_perm(id,name) values(8,'ELLISON'); 1 row processed.
SVRMGR> insert into v_test_perm(id,name) values(9,'GROWER'); 1 row processed.
SVRMGR> commit;
Statement processed.
SVRMGR> select * from v_test_perm;
ID NAME

---------- ------------------------------
         7 GATES
         8 ELLISON
         9 GROWER

3 rows selected.
SVRMGR> delete from v_test_perm where id=2; 0 rows processed.

As you see, each user has access only to his rows. He can not select,update or delete rows of other users.

Andrew Protasov

> 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
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
>
Received on Wed Dec 02 1998 - 14:25:34 CST