Compiled Messages: ------------------------------------------------------------ Message no. 773 Posted by Peter Ping Liu (TEC5363PL) on Monday, November 6, 2006 1:40pm Subject: Problem in Project 8 Here is a code segment from a submission of Project 8: --Create a policy function CREATE OR REPLACE FUNCTION DBSEC_ROW_OWNER_WHERE(p_schema in varchar2, p_object in varchar2) return varchar2 as begin return 'CTL_UPD_USER = USER'; END; / What is the consequence of the above code, in relation to DBSEC, the owner of the CUSTOMER table? In other words, what will happen if DBSEC tries to query the CUSTOMER table? There were three (3) students who had this problem. I did not take any points off on this problem. But, I like you to learn the skill. Have fun. ------------------------------------------------------------ Message no. 779[Branch from no. 773] Posted by Ravinder Gaur (rgaur) on Tuesday, November 7, 2006 7:24am Subject: Re: Problem in Project 8 With the above function code, I'm sure that DBSEC (ie the owner of the customer table) won't be able to query any data from the customer table i.e. would just get a 0 rowcount. Typically we make the owner and some powerful admin users (like SYSTEM) "exempted" from the VPD policy function and this is a violation of the practice. The only users that would show any rowcounts would be the ones that have values in the ctl_upd_user column. - Ravi ------------------------------------------------------------ Message no. 784 Posted by Paras Pradhan (ppradhan) on Thursday, November 9, 2006 1:12pm Subject: DDL and Triggers hi everyone, Just going through the book there are nice ways for DML auditing. What about DDL auditing? Paras. ------------------------------------------------------------ Message no. 793[Branch from no. 773] Posted by Krishnamurth Ashwini (kashwini) on Thursday, November 9, 2006 7:21pm Subject: Re: Problem in Project 8 I have a question...when I saw the code attached by Dr.Liu, I changed my previous code from 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; / (TO) CREATE OR REPLACE FUNCTION DBSEC_ROW_OWNER_WHERE(p_schema in varchar2, p_object in varchar2) return varchar2 as begin return 'CTL_UPD_USER = USER'; END; / CONN DBSEC/sec#1new; SELECT COUNT(*) FROM CUSTOMER; The output of the above query is still 100. How is this possible? Ash ------------------------------------------------------------ Message no. 798[Branch from no. 793] Posted by Ravinder Gaur (rgaur) on Friday, November 10, 2006 7:43am Subject: Re: Problem in Project 8 OK, I tried it myself but don't see how you can still get 100 rows with the changed function. I suspect that you created a new function under a different schema (SYS or SYSTEM; however you logged in while running the new code). The original function exists under DBSEC and the new (changed) function should replace that. Do this (before making any changes) -- Login as system and run this query -- col owner for a10 col object_name for a25 col object_type for a15 select owner,object_name,object_type from dba_objects where object_name='DBSEC_ROW_OWNER_WHERE'; => see how many rows of output you get above. BTW, here's my screenshot for everyone else to look at -- --------------------------------------------------------------------------------------- SQL> connect system/oracle Connected. SQL> show user USER is "SYSTEM" SQL> col owner for a10 SQL> col object_name for a25 SQL> col object_type for a15 SQL> select owner,object_name,object_type from dba_objects 2 where object_name='DBSEC_ROW_OWNER_WHERE'; OWNER OBJECT_NAME OBJECT_TYPE ---------- ------------------------- --------------- DBSEC DBSEC_ROW_OWNER_WHERE FUNCTION SQL> connect dbsec/sec#1new Connected. SQL> show user USER is "DBSEC" SQL> select count(*) from customer; COUNT(*) ---------- 100 SQL> CREATE OR REPLACE FUNCTION DBSEC_ROW_OWNER_WHERE(p_schema in varchar2, 2 p_object in varchar2) 3 return varchar2 4 as 5 begin 6 return 'CTL_UPD_USER = USER'; 7 END; 8 / Function created. SQL> select count(*) from customer; COUNT(*) ---------- 0 ----------------------------------------------------------------------------------------- Hope this helps. - Ravi ------------------------------------------------------------ Message no. 799[Branch from no. 798] Posted by Krishnamurth Ashwini (kashwini) on Friday, November 10, 2006 9:28am Subject: Re: Problem in Project 8 Ravi, I see what you are saying. I did not create this function outside DBSEC schema. I just replaced the function that I had created earlier( in DBSEC) with the new one. When I run the script it (using @file_path), it still shows the count as 100. Anyway, I tried your code and it works. Thanks:-) Ash ------------------------------------------------------------ Message no. 804[Branch from no. 799] Posted by Anjana Divakar (adivakar) on Saturday, November 11, 2006 12:17pm Subject: Re: Problem in Project 8 I tried it out as well. thanks! ------------------------------------------------------------ Message no. 806[Branch from no. 773] Posted by Sagun Piya (srpiya2) on Saturday, November 11, 2006 2:11pm Subject: Re: Problem in Project 8 yes I got count with zero rows Sagun ------------------------------------------------------------ Message no. 816[Branch from no. 784] Posted by Kelsey Pooley (kjpooley) on Saturday, November 11, 2006 8:47pm Subject: Re: DDL and Triggers By using DDL triggers, the DBA can automatically track all changes to the database, including changes to tables, indexes and constraints. Unlike traditional triggers that invoke a procedure when an object has changed, system-level triggers are global and are not tied to any schema objects. Because the Oracle system-level triggers can interface with Oracle tables, the logical approach is to create Oracle tables to hold the data. You start by defining a table to hold the changes. The DDL trigger executes every time a DDL statement is executed, and adds new entries to your new table. Whenever a change is made, the details will be written to your table, and you can create audit reports for management. This report is beneficial for a DBA who must track changes to their database. This report uses the DDL trigger and produces a complete audit log of all Oracle databases changes. See http://www.dba-oracle.com/art_builder_proper_oracle_design_for_auditing.htm for more detail. Kelsey ------------------------------------------------------------ Message no. 817[Branch from no. 816] Posted by Paras Pradhan (ppradhan) on Saturday, November 11, 2006 9:04pm Subject: Re: DDL and Triggers kelsey That is what i was exaclty looking for. i will check the url u provided. Thanks Paras, ------------------------------------------------------------ Message no. 818[Branch from no. 773] Posted by Naziya Shaik (snaziya) on Saturday, November 11, 2006 9:12pm Subject: Re: Problem in Project 8 I too got '0' as output..after creating the function ..DBSEC_ROW_OWNER_WHERE() Thanks to Ravi and Ash Naz ------------------------------------------------------------ Message no. 819[Branch from no. 818] Posted by Venkat Munagala (vrmunagala) on Sunday, November 12, 2006 1:43am Subject: Re: Problem in Project 8 I got it working with 0 as output too. Thanks for the information. Rohit ------------------------------------------------------------ Message no. 820[Branch from no. 816] Posted by Suresh Methuku (smethuku) on Sunday, November 12, 2006 1:45am Subject: Re: DDL and Triggers Thanks for the link kelsey ------------------------------------------------------------