Re: Setting permission based on column

In article <a4bqp9$an3q$1_at_as201.hinet.hr>, "Stjepan says...
>
>"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
>news:a20d28ee.0202080422.1e7eaaf8_at_posting.google.com...
>> "Stjepan Brbot" <Stjepan.Brbot_at_ZG.HiNet.HR> wrote in message
 news:<a3vpj7$9lr6$2_at_as201.hinet.hr>...
>> > Since there's no possibility to grant/revoke permission on column
 basis,
>> > I'm thinking about trigger that could help in setting some kind of
>> > security checking if user is allowed to insert value in column of
 table.
>> > I.e. I have table with columns A,B,C and want only user with role
>> > "ABC_ROLE" to have the ability (from Oracle Forms app) to
 insert/update
>> > value in column B. This trigger should be raised before real
 inserting
>> > new row but don't know how to check if user has ABC_ROLE? Finally,
 need
>> > yours ideas, how should this trigger look like?
>>
>> simply not true
>> from the Oracle manual
>>
>> Granting Multiple Object Privileges on Individual Columns Example
>> To grant blake the REFERENCES privilege on the empno column and the
>> UPDATE privilege on the empno, sal, and comm columns of the emp table
>> in the schema scott, issue the following statement:
>>
>> GRANT REFERENCES (empno), UPDATE (empno, sal, comm)
>>
>> ON scott.emp
>> TO blake;
>
>Thank you Sybrad.
>According to documentation is works for Oracle8 and above. Does it work
>with Oracle v7.3.4 database?
>

easy enough to see if you have a 734 database:

scott_at_ORA734.WORLD> GRANT REFERENCES (empno), UPDATE (empno, sal, comm)   2 ON scott.emp
  3 TO public;

Grant succeeded.

yes, it does.

> BTW can you explain me this REFERENCES privilege.

it lets others setup a foreign key that references the EMPNO column of the SCOTT.EMP table.

>
>--
>
>Stjepan Brbot <Stjepan.Brbot_at_ZG.HiNet.HR>
>
>
>
>

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp