RE: Column level security
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
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
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
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
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
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 mailto: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 mailto: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 mailto: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).
