Compiled Messages: ------------------------------------------------------------ Message no. 729 Posted by Ravinder Gaur (rgaur) on Thursday, November 2, 2006 6:56am Subject: Project 8 question #12 says you should see only 1 row but I'm noticing that the CUSTOMER table has 21 rows where CTL_UPD_USER=VPD_CLERK1. I therefore see 21 rows connected as VPD_CLERK1. Unless I'm missing something, there seems to be a mistake in the project question. - Ravi ------------------------------------------------------------ Message no. 730[Branch from no. 729] Posted by Naziya Shaik (snaziya) on Thursday, November 2, 2006 8:10am Subject: Re: Project 8 question I dont think there is any mistake in project question. I got one row as output. And I think the output should be only one row because there is only one row of this user (vpd_clerk1 user) in the dbsec's customer table. thanks Naz ------------------------------------------------------------ Message no. 731[Branch from no. 729] Posted by Krishnamurth Ashwini (kashwini) on Thursday, November 2, 2006 8:38am Subject: Re: Project 8 question Ravi, Actually it was pretty confusing to me too at first. But I thought the question meant, one row of "output" should be seen, similar to the example in page 231. Even I got 21 as the ouput result. Naziya, Did you get '1' as output? Ash ------------------------------------------------------------ Message no. 732[Branch from no. 730] Posted by Ravinder Gaur (rgaur) on Thursday, November 2, 2006 11:20am Subject: Re: Project 8 question Naz, What do you get when you run the following (connected as DBSEC) -- SELECT count(*) from DBSEC.CUSTOMER where CTL_UPD_USER='VPD_CLERK1'; - Ravi ------------------------------------------------------------ Message no. 735[Branch from no. 731] Posted by Gnaneshwar Bukka (gbukka) on Thursday, November 2, 2006 11:55am Subject: Re: Project 8 question Hi, According to the inserts to the table 'customer' the user 'VPD_CLERK1' has 21 rows from the 100 rows. Therefore, it would display 21, I agree with what Ash said about the display, similar to pg 231 (one row of data). Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 736[Branch from no. 735] Posted by Gnaneshwar Bukka (gbukka) on Thursday, November 2, 2006 12:01pm Subject: Re: Project 8 question Hi, I think there is no need to specify even the 'WHERE' clause( WHERE CTL_UPD_USER='VPD_CLERK1'), because we are already logged into user 'VPD_CLERK1' and according to the VPD concepts, only the rows related to the user would be displayed (21). Please correct me if I am wrong. Thank you. Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 737[Branch from no. 736] Posted by Krishnamurth Ashwini (kashwini) on Thursday, November 2, 2006 1:13pm Subject: Re: Project 8 question Yes, Gnaneshwar..you are right. The query should show the number of rows as 21 - whether or not you specify the WHERE clause. Ash ------------------------------------------------------------ Message no. 738 Posted by Paras Pradhan (ppradhan) on Thursday, November 2, 2006 1:45pm Subject: dbsec Frens: Got a question. I get 21 output using vpd_clerk1 user. but when i exec as dbsec. i get count =0 what might be the issue. SQL> select count(*) from dbsec.customer where ctl_upd_user = 'vpd_clerk1'; COUNT(*) ---------- 0 My policy fxn looks like: create or replace function dbsec_row_owner_where ( p_schema in varchar2, p_object in varchar2) return varchar2 as begin if (user = 'dbsec') then return ''; else return 'ctl_upd_user = USER'; end if; end; / What did i missed? Paras. ------------------------------------------------------------ Message no. 739[Branch from no. 737] Posted by Ravinder Gaur (rgaur) on Thursday, November 2, 2006 2:28pm Subject: Re: Project 8 question Gnaneshwar, Yes you are logically correct from a VPD standpoint. Keep in mind though - the rowcount would be 21 only if you are connected as VPD_CLERK1 (where the policy comes into play). To validate it against the actual data, you'd need to login as a "vpd exempted" user (preferably the table owner) and put the WHERE condition explicitly. - Ravi ------------------------------------------------------------ Message no. 740[Branch from no. 738] Posted by Ravinder Gaur (rgaur) on Thursday, November 2, 2006 2:29pm Subject: Re: dbsec Paras, Have you tried putting the user in caps -- if (user = 'DBSEC'). - Ravi ------------------------------------------------------------ Message no. 741[Branch from no. 740] Posted by Paras Pradhan (ppradhan) on Thursday, November 2, 2006 2:59pm Subject: Re: dbsec Ravi: Great. It worked.. Thanks a lot. Paras. ------------------------------------------------------------ Message no. 742[Branch from no. 740] Posted by Paras Pradhan (ppradhan) on Thursday, November 2, 2006 3:00pm Subject: Re: dbsec I did if (USER = 'DBSEC'). Paras. ------------------------------------------------------------ Message no. 743[Branch from no. 739] Posted by Rhonda Nichols (renichols2) on Thursday, November 2, 2006 3:19pm Subject: Re: Project 8 question good point Ravi! -rhonda ------------------------------------------------------------ Message no. 744[Branch from no. 739] Posted by Gnaneshwar Bukka (gbukka) on Thursday, November 2, 2006 3:59pm Subject: Re: Project 8 question Hi, Exactly Mr. Ravi, when considering your point of view then we have to specify the where clause when logged in as a different user (generally admin user). Thank you very much everyone. Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 745[Branch from no. 740] Posted by Gnaneshwar Bukka (gbukka) on Thursday, November 2, 2006 4:04pm Subject: Re: dbsec Hello Mr. Ravi, Thank you very much for letting us know that. Could you please clarify why it has to be in caps. As far as my knowledge, Oracle is case sensitive even for passwords. So, how did the case make difference. Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 746[Branch from no. 741] Posted by Anjana Divakar (adivakar) on Thursday, November 2, 2006 5:02pm Subject: Re: dbsec thanks for letting us know to put user in caps. I would have never realized that that was the problem! ------------------------------------------------------------ Message no. 747[Branch from no. 745] Posted by Ravinder Gaur (rgaur) on Thursday, November 2, 2006 5:02pm Subject: Re: dbsec Paras, You are right in that Oracle is case insensitive to usernames and passwords. However, the database inherently stores them in capitals unless you explicitly specified a lowercase username/password in quotes. Look for values in DBA_USERS. Since you are comparing two literal values here (using an IF construct), the case has to match exactly (just like 'abc' cannot be equal to 'ABC'). Look at the following example -- SQL > show user USER is "SYS" SQL > select user from dual; USER ------------------------------ SYS SQL > select count(*) from dual; COUNT(*) ---------- 1 SQL > select count(*) from dual where USER='SYS'; COUNT(*) ---------- 1 SQL > select count(*) from dual where USER='sys'; COUNT(*) ---------- 0 Also, look at this -- SQL > select count(*) from dual where sys_context('userenv','session_user')='SYS'; COUNT(*) ---------- 1 SQL > select count(*) from dual where sys_context('userenv','session_user')='sys'; COUNT(*) ---------- 0 I believe the "USER" built in function derives its value from USER_USERS dictionary view and you can verify the current user there also -- SQL > select username from user_users; USERNAME --------------- SYS Hope that helps. - Ravi ------------------------------------------------------------ Message no. 748[Branch from no. 738] Posted by Sagun Piya (srpiya2) on Thursday, November 2, 2006 5:55pm Subject: Re: dbsec Thanks Ravi for giving so much information. Sagun ------------------------------------------------------------ Message no. 755[Branch from no. 747] Posted by Suresh Methuku (smethuku) on Friday, November 3, 2006 1:39am Subject: Re: dbsec Ravi, Thankyou very much for your valuable suggestion. ------------------------------------------------------------ Message no. 756[Branch from no. 747] Posted by Gnaneshwar Bukka (gbukka) on Friday, November 3, 2006 9:53am Subject: Re: dbsec Hi, Thank you very much Ravi. It was a great explaination, I wouldnt have ever bothered to know, as I always use uppercase. Thanks again. Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 757[Branch from no. 748] Posted by Venkat Munagala (vrmunagala) on Friday, November 3, 2006 12:33pm Subject: Re: dbsec Thank you Ravi. This gave good knowledge regarding lowercase and upper case letters problems in executing. Now in some cases I can easily figure out the problem. ------------------------------------------------------------ Message no. 760[Branch from no. 729] Posted by Lakshmikar Padmaraju (lpadmaraju) on Friday, November 3, 2006 5:19pm Subject: Re: Project 8 question Hi, I did this way. After inserting data into table I ran this query and displayed data on screen select ctl_upd_user, count(*) from dbsec.customer group by ctl_upd user; I got the following result ctl_upd_user count(*) --------------- -------------- VPD_CLERK1 21 VPD_CLERK2 37 VPD_CLERK3 42 AFTER IMPLEMENTING THE POLICY FUNCTION I logged in as vpd_clerk1 i ran the same query as above and printed output on the screen I got the following result ctl_upd_user count(*) --------------- -------------- VPD_CLERK1 21 I only got one row. I hope this helps some body. Raju. ------------------------------------------------------------ Message no. 761[Branch from no. 747] Posted by Daniel Thurston (dsthurston) on Saturday, November 4, 2006 5:49am Subject: Re: dbsec great explanation thanks much DanT ------------------------------------------------------------ Message no. 765[Branch from no. 742] Posted by Eric Knuth (elknuth2) on Saturday, November 4, 2006 10:39pm Subject: Re: dbsec You only need to use uppercase for the actual username. It is the only thing stored in the database and is dereferenced. Eric ------------------------------------------------------------ Message no. 766[Branch from no. 730] Posted by Eric Knuth (elknuth2) on Saturday, November 4, 2006 10:43pm Subject: Re: Project 8 question Yes, you are correct. The "1" refers to the number of output rows, not the count in the output. Eric ------------------------------------------------------------