Re: Column level security

2003-06-23 Thread Jared Still 

In addition to views, which have already been mentioned,
column level security has been around since at least 7.0.

Look at the fine manual under 'GRANT'.

Jared

On Monday 23 June 2003 04:04, Pradeep Kumar G wrote:
> Dear All,
>
> Is there any way to implement column level security in Oracle 9i
> database ?
>
> Information on having row level security through VPD is available. But
> is it possible to have column level security ?
>
> I have seen in some sites,like
> (http://www.ftt.co.uk/C520_outline.html,
> http://www.actisit.com/outlines/forms/Or202_Or9i%20DBA%20I%20outline.pdf)
>
> mentioned about column level security.
>
> Can someone help me in this regard?
>
>
> Pradeep

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Column level security

2003-06-23 Thread Arup Nanda 
Are you referring to give grants based on columns. You could use ON COLUMN
syntaxt in the grant statemetnt for INSERT, REFERENCES, UPDATE.

If this is not acceptable, then other option is using views for each user
type and based on the user's priviege type. For instance, you may have a
table called CREDIT_CARDS, and you want to display the card numbers if the
user is a manager, just last 4 digits if analyst and nothing, if anybody
else.

create view ..
as
select ...,
   decode(emp_type,
  'MANAGER',cc_num,
  'ANALYST',substr(cc_num,13,4),
  null) cc_num
from CREDIT_CARDS cc, EMP e


Join this with VPD and you just got yourself a dynamic view which presents
information selectively.

HTH.

Arup


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, June 23, 2003 7:04 AM


>
> Dear All,
>
> Is there any way to implement column level security in Oracle 9i
> database ?
>
> Information on having row level security through VPD is available. But
> is it possible to have column level security ?
>
> I have seen in some sites,like
> (http://www.ftt.co.uk/C520_outline.html,
> http://www.actisit.com/outlines/forms/Or202_Or9i%20DBA%20I%20outline.pdf)
>
> mentioned about column level security.
>
> Can someone help me in this regard?
>
>
> Pradeep
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Pradeep Kumar G
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Column level security

2003-06-23 Thread Mladen Gogala 
Yup. It's called "views".
On 2003.06.23 07:04, Pradeep Kumar G wrote:
Dear All,

Is there any way to implement column level security in Oracle 9i
database ?
Information on having row level security through VPD is available. But
is it possible to have column level security ?
I have seen in some sites,like
(http://www.ftt.co.uk/C520_outline.html,
http://www.actisit.com/outlines/forms/Or202_Or9i%20DBA%20I%20outline.pdf)
mentioned about column level security.

Can someone help me in this regard?

Pradeep



--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Pradeep Kumar G
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Column level security

2003-06-23 Thread rgaffuri 
just make a view and dont include the columns. I dont know if there is a VPD for 
columns. 
> 
> From: "Pradeep Kumar G" <[EMAIL PROTECTED]>
> Date: 2003/06/23 Mon AM 07:04:40 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Column level security
> 
> 
> Dear All,
> 
> Is there any way to implement column level security in Oracle 9i
> database ?
> 
> Information on having row level security through VPD is available. But
> is it possible to have column level security ?
> 
> I have seen in some sites,like
> (http://www.ftt.co.uk/C520_outline.html,
> http://www.actisit.com/outlines/forms/Or202_Or9i%20DBA%20I%20outline.pdf)
> 
> mentioned about column level security.
> 
> Can someone help me in this regard?
> 
> 
> Pradeep
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Pradeep Kumar G
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Column level security

2003-06-23 Thread Naveen Nahata 
Views?

> -Original Message-
> From: Pradeep Kumar G [mailto:[EMAIL PROTECTED]
> Sent: Monday, June 23, 2003 4:35 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Column level security
> 
> 
> 
> Dear All,
> 
> Is there any way to implement column level security in Oracle 9i
> database ?
> 
> Information on having row level security through VPD is available. But
> is it possible to have column level security ?
> 
> I have seen in some sites,like
> (http://www.ftt.co.uk/C520_outline.html,
> http://www.actisit.com/outlines/forms/Or202_Or9i%20DBA%20I%20o
utline.pdf)

mentioned about column level security.

Can someone help me in this regard?


Pradeep



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pradeep Kumar G
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


DISCLAIMER:
This message (including attachment if any) is confidential and may be privileged. 
Before opening attachments please check them for viruses and defects. MindTree 
Consulting Private Limited (MindTree) will not be responsible for any viruses or 
defects or any forwarded attachments emanating either from within MindTree or outside. 
If you have received this message by mistake please notify the sender by return  
e-mail and delete this message from your system. Any unauthorized use or dissemination 
of this message in whole or in part is strictly prohibited.  Please note that e-mails 
are susceptible to change and MindTree shall not be liable for any improper, untimely 
or incomplete transmission.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Naveen Nahata
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: column level security

2002-04-23 Thread Khedr, Waleed 

 What about this:

Oracle Announcement: 

   Oracle Security Product Management has released new security alerts
   today. 

   Please note that you must log into MetaLink at
   http://metalink.oracle.com to review this document. Use MetaLink's
   advanced search option to retrieve the document by identification
   number.

   USER PRIVILEGES VULNERABILITY IN ORACLE9i DATABASE SERVER 
   Document Identification Number 185074.1 

   Thank you for using MetaLink. 
   Oracle Support Services 


  The following is an example of the error: 

   connect  system/@database  
   CREATE USER us1 IDENTIFIED BY us11 DEFAULT TABLESPACE users TEMPORARY

   TABLESPACE temp; 
   CREATE USER us2 IDENTIFIED BY us12 DEFAULT TABLESPACE users TEMPORARY

   TABLESPACE temp; 
   Grant Create  Session To us1; 
   Grant Create  Session To us2; 
   Grant Create Table To us1; 
   grant unlimited tablespace to us1; 
   Grant Create View To us2; 

   Connect us1/us11@database ; 
   Create Table t1(c1 Number(1)); 
   Insert Into t1(c1) Values(9); 
   Create Table t2(c1 Number(1)); 
   Insert Into t2(c1) Values(9); 

   commit; 
   Connect us2/us11@database ; 

   SQL> Select * From us1.t1; 
   ORA-00942: table or view does not exist 
   -- this is expected 

   SQL> Select * From us1.t2; 
   ORA-00942: table or view does not exist 
   -- this one too 

   SQL> Create View aa As Select * From us1.t1; 
   ORA-00942: table or view does not exist 
   -- and this one 

   SQL> Create View aa As Select t1.c1 As t1_c1, t2.c1 As t2_c1 
   >From us1.t1 Left Outer Join us1.t2 On t1.c1 = t2.c1; 

   View created 
   -- now this one is NOT ! 
   SQL> select * from aa; 

   T1_C1 T2_C1 
     
  99 

   This effectively means that LEFT OUTER JOIN allows to create views 
   on tables that are normally not visible (provided that unprivileged
user 
   knows table and column names). 

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 4/23/02 10:18 AM

Ok so i've been asked to research column level security, from what I can
find, we're still stuck with this:
 
if userA needs to see cols 1,3,5 of tableA and
   userB needs to see cols 1,2,3,4 of tableA.
 
We're still stuck with doing private views?
 
we dont need row level security so a VPD is kinda overkill, right?
 
thanks, joe
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).