TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Did you know ... 3) AppsWorld 2004 4) SQL Tip – CASE statement 5) Industry News 6) ANSWER TO THE POP QUIZ -------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- How can you achieve Row-Level Security? -------------------------------------------------------- 2) Did you know ... --------------------------------------------------------- More often than not, SQL is faster than PL/SQL. I’m sure you’ve heard this before, but it’s alarming how many developers will use a procedural solution to a problem that can be handled with simple SQL. In the December, 2003 Oracle Professional newsletter, Parin Jhavari and Lev Moltyaner examine this fact with a couple of examples and how the use of SQL, as opposed to PL/SQL, can drastically improve your processing time. For more information, visit http://www.pinpub.com/html/main.isx?sub=23 -------------------------------------------------------- 3) AppsWorld 2004 --------------------------------------------------------- Registration is now open for Oracle AppsWorld 2004, which will be held Jan. 26-29, 2004, at the San Diego Convention Center. You can save $500 if you register prior to Dec 19th. For more information, visit: http://www.oracle.com/appsworld/. --------------------------------------------------------- 4) SQL Tip – CASE statement --------------------------------------------------------- Suppose you have a table that stores information about the people who access your website. One of the pieces of information you track is the year of birth for each user. Now, new legislation is coming that will restrict the type of information that you are allowed to ask users. One of these proposed changes will be that your user’s age will have to be grouped into age buckets, rather than specific years. So, how can we take our existing data and group them into buckets of years? One solution is to use the CASE function. Here’s how I do it: select sum(case when year between 1984 and 1993 then 1 else 0 end) as "10-19", sum(case when year between 1974 and 1983 then 1 else 0 end) as "20-29", sum(case when year between 1964 and 1973 then 1 else 0 end) as "30-39", sum(case when year between 1954 and 1963 then 1 else 0 end) as "40-49", sum(case when year between 1944 and 1953 then 1 else 0 end) as "50-59", sum(case when year between 1934 and 1943 then 1 else 0 end) as "60-69", sum(case when year between 1924 and 1933 then 1 else 0 end) as "70-79", sum(case when year < 1923 then 1 else 0 end) as "80+" from user; 10-19 20-29 30-39 40-49 50-59 60-69 70-79 80+ ----- -------- -------- -------- -------- -------- -------- -------- 216 3010 5220 6133 5777 3153 1242 4551 As you can see, the results are grouped nicely into my age ranges that I’m particularly interested in. You can use variations of this query to suit your needs. You can use DECODE to accomplish this, but your query will be very messy as DECODE only supports equality tests, while the CASE statement has greater flexibility in the types of relational operators you can use. So next time you need to perform some logical test (if/then/else) using SQL, consider using the CASE statement. You’ll be happy you did. -------------------------------------------------------- 5) Industry News -------------------------------------------------------- Oracle recently announced that it will be supporting a National education research project in China. The program is being organized by the National Center for Education Technology (NCET). 126 participating Primary and secondary schools in China will use Oracle's online learning environment, Think.com, as part of their education curriculum. Think.com supports the Chinese language, as well as Thai, Spanish and of course, English. For more information, read the press release at: http://www.oracle.com/corporate/press/index.html?2597199.html. * * * * Linux 2.6 is set to release in the middle of December. This new release promises significant improvements in scalability of CPUs, memory and disks. Version 2.6 will handle 32-processor servers and file-system improvements will help database systems and data-intensive applications perform better. The new Kernel is optimized for AMD’s Athlon64 desktop computer chip enabling better support for 64-bit processing. In a non-related story, Red Hat Inc.’s Chief Operating Officer, Tim Buckley, resigned Dec 1st, after 5 years with the company. * * * * PeopleSoft’s latest “poison pill” is drawing criticism in the industry. Months ago, PeopleSoft launched their new “Customer Assurance Program” that provides refunds between two and five times their license fees if the company is acquired and certain conditions aren’t met by the purchasing company. Recently, PeopleSoft has amended this program to include the event that a majority of their Directors were changed. This anti-takeover strategy is expected to be reviewed by the Securities and Exchange Commission (SEC) and may cause People Soft to restate all of their revenues that they’ve collected under this program. For more information, read the story at: http://www.thestreet.com/_yahoo/tech/ronnaabramson/10129027.html -------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- Row-level security has been historically handled using views and restricting row-level data from users through the query in the view. The problem is that if you want each user to have access to different data, your single view cannot support it. Introduced in Release 3 of Oracle (8.1.7), a featured called Oracle Label Security can help solve this problem. In essence, Oracle Label Security is a set of procedures and constraints build into the database that can manage row-level security on single tables or on the entire schema if needed. You first start by creating a security Policy, which will contain levels, such as PUBLIC, PRIVATE, etc. These Levels will be used to ‘tag’ your row- level data with a Label, which is really just a new column on the table to identify the security level of each row of data. Users are then assigned to Labels along with any other table privileges that they require. Note that Oracle Label Security works AFTER the appropriate grants are checked. When the data is inserted into the table, you have to assign the Level to each row. Be aware that once Oracle Label security is enabled, you must have privileges to update the Label column. So, let’s assume I have a table that has some data marked for PUBLIC access and other data marked for PRIVATE access and 2 users, 1 with the PRIVATE Label assigned and another with the PUBLIC Label. When user1 performs a SELECT (or INSERT/UPDATE/DELETE) from my table, they will see all of the rows of data (the PRIVATE and PUBLIC rows). But, when user2 performs the very same SELECT, they will only see the data that is Labeled PUBLIC, since the Label Policy will be defined that the PRIVATE data is not included with the PUBLIC Label Policy. For more information on Oracle Label Security, visit OTN at: http://otn.oracle.com/deploy/security/ols/index.html Please let me know if you’ve used this feature to implement Row-level Security and what experiences (good and bad) you encountered with it.