Compiled Messages: ------------------------------------------------------------ Message no. 662 Posted by Peter Ping Liu (TEC5363PL) on Monday, October 23, 2006 8:51am Subject: Virtual Private Database (2) This week, we continue learning virtual private database. A virtual private database can be used to enforce row level and column level security. 1. What is row level security? 2. What is column level security? 3. What are the advantages of row level security, and column level security? 4. What are the disadvantages of row level security, and column level security? ------------------------------------------------------------ Message no. 663[Branch from no. 662] Posted by Anjana Divakar (adivakar) on Monday, October 23, 2006 1:40pm Subject: Re: Virtual Private Database (2) Oracle's latest foray into Oracle security management is a product with several names. Oracle has two official names for this product, virtual private databases, or VPD, which as also known as fine- grained access control. It is also commonly known as Row Level Security and the Oracle packages have RLS in the name. Regardless of the naming conventions, VPD security is a very interesting new component of Oracle access controls.At a high-level, VPD security adds a WHERE clause predicate to every SQL statement that is issued on behalf of an individual and user. Depending upon the end users access, the WHERE clause constrains information to specific rows a within the table, hence the name row-level security. The advantages of row level security are: -one of the main uses is to allow all of the data to be stored in one database for different departments or even for a hosting company to store data for different companies in one database. -The issue of having to use shared accounts is no longer a problem as application roles / groups of users do not need to be segregated for the purpose of hard coding views onto the data. Row level security can be made to work with shared accounts though, if needed. -Auditing can now be done more easily using Oracles built in features. -Using row level security makes the application more manageable due to simpler designs and less potential code. -Row level security provides a protection against ad-hoc queries as the tool does not matter anymore; the data is secured for everyone at the source. The drawback of Row-level security is that although it is quite easy to design and impose, but there are costs you need to be aware of when you migrate from one version to another. In conventional Virtual Private Database the VPD Policy is applied to the whole row. By default a Column-Level VPD Policy allows you to restrict the rows displayed only if specified columns are accessed.Column-level VPD enables you to enforce row-level security when a security-relevant column is referenced in a query. You can apply column-level VPD to tables and views. VPD Column level security is only available in Oracle 10g.Oracle extended the VPD feature from rows to columns.Oracle has the capability to restrict updates or inserts on columns using GRANT UPDATE (column) and INSERT (column). ------------------------------------------------------------ Message no. 664[Branch from no. 662] Posted by Venkat Munagala (vrmunagala) on Monday, October 23, 2006 2:45pm Subject: Re: Virtual Private Database (2) Row level security: VPD’s row-level security allows restricting access to records based on a security policy implemented in PL/SQL. A security policy, simply describes the rules governing access to the data rows. This process is done by creating a PL/SQL function that returns a string. The function is then registered against the tables, views, or synonyms you want to protect by using the DBMS_RLS PL/SQL package. When a query is issued against the protected object, Oracle effectively appends the string returned from the function to the original SQL statement, thereby filtering the data records. Advantages of row level security: 1. Oracle's row level security provides a great improvement where many users must connect to the same data but be segregated based on what parts of that data they are allowed to view and edit. 2. Maintenance becomes easier. 3. Because row level security is implemented as close to the data as possible, the loophole of accessing the data with a share account from a tool such as SQL*Plus is solved. 4. Row level security provides a protection against ad-hoc queries as the tool does not matter anymore; the data is secured for everyone at the source. Column level security One more important element in the level of security implementation is the column-level security. Oracle Database 10g, supports Column-Level Virtual Private Database. VPD and Column Access using Oracle extended the VPD feature from rows to columns. We can apply VPD policies specifically where privacy relevant column(s) are referenced in the user’s query or DML We can apply column-level VPD to tables and views, but not to synonyms. When a user has rights to access the object itself VPD can limit the individual rows returned only if the columns he or she accesses are considered security-or privacy relevant. Because access is controlled by adding a where clause, column-level access can only be maintained by defining multiple views for each class of end user. Rohit ------------------------------------------------------------ Message no. 665[Branch from no. 662] Posted by Gnaneshwar Bukka (gbukka) on Monday, October 23, 2006 3:56pm Subject: Re: Virtual Private Database (2) 1. What is row level security? Fine grained access control or row level security or virtual private databases, all mean the same. Oracle has a feature called "Virtual Private Databases" (VPD) that enables you to configure permissions at row level. Row level security is based around the idea of having a defined security policy function that is attached to a database table or view execute each time data in the table or view is queried or altered. It is based on custom triggers and functions, which set session context and define user access to the data on a row level. 2. What is column level security? Sensitive data must be protected and at the same time, non-sensitive data should be generally available, this can be done using Column-Level security. Security policy can be defined such that, you enforce row level security only when a security-relevant column is referenced in a query. 3. What are the advantages of row level security, and column level security? Row level security: · One of the main uses is to allow all of the data to be stored in one database for different departments or even for a hosting company to store data for different companies in one database. · It should be possible to retro-fit row level security to an existing application due to the fact that it is implemented on the server as close to the actual data as possible. · Using row level security makes the application more manageable due to simpler designs and less potential code. · Row level security provides a protection against ad-hoc queries as the tool does not matter anymore; the data is secured for everyone at the source. Column level security: · You can apply column-level VPD to tables and views, but not to synonyms. By specifying the security-relevant column name with the sec_relevant_cols parameter of the DBMS_RLS.ADD_POLICY procedure, the security policy is applied whenever the column is referenced, explicitly or implicitly, in a query. · If a query references a security-relevant column, then the default behavior of column-level VPD restricts the number of rows returned. 4. What are the disadvantages of row level security, and column level security? · Custom solution needed, there is no easy way to use row level security with standard out of the box solutions or solvers provided. Oracle RDBMS side coding is required. · Management of a large number of users, there can be lots of registered users in publicly accessed websites. All of these registered users would have their own accounts in Oracle RDBMS and it may become really hard to manage all these accounts. ------------------------------------------------------------ Message no. 666[Branch from no. 663] Posted by Gnaneshwar Bukka (gbukka) on Monday, October 23, 2006 3:56pm Subject: Re: Virtual Private Database (2) Good explanation Anjana, thank you. Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 667[Branch from no. 662] Posted by Gnaneshwar Bukka (gbukka) on Monday, October 23, 2006 3:59pm Subject: JoKe Sherlock Holmes and Matthew Watson On a Camping and Hiking Trip Holmes and Watson had gone to bed and were lying there looking up at the sky. Holmes said, "Watson, look up. What do you see? "Well, I see thousands of stars." "And what does that mean to you?" "Well, I guess it means we will have another nice day tomorrow. What does it mean to you, Holmes?" "To me, it means someone has stolen our tent." ------------------------------------------------------------ Message no. 668[Branch from no. 662] Posted by Paras Pradhan (ppradhan) on Tuesday, October 24, 2006 7:53pm Subject: Re: Virtual Private Database (2) 1. What is row level security? Row level security (RLS) is the mechanism in RDMS in which the access to the records of the table are resticied for use, providing security. RLS is implemented with the help of view, pl/sql and application context.For ex: a table has 100 records. A user x can see only 20 records that belong to him. 2. What is column level security? Column level security is the technique to restrict the column or fields of the table for security reasons.For example : user x can see only data of three fields/columns of a table which have ten fields. 3.What are the advantages of row level security, and column level security? Advantages of row level security: 1)Security improvements 2) Security featute is implemennted in Database rather than application codes proving easier maintainence 3) Reduced number of tables providing easy administration 4)In case of oracle, auditing can be done using it's buitin functions 5) Simpler design Advantages of column level security: 1) Security increased 2) Secured using RDMS rather than coding while programming and less work for programmers 4. What are the disadvantages of row level security, and column level security? Disadvantages of row level security: 1) Single table facilititaes for various purposes. If the access point to tables that are using RLS are compramised, loss will be very huge comparing to security implemented without RLS 2) Can be a complex task to study and implement Disadvantages of column level security: 1)Tables using column level security might have null/blank fields after the data insertion due to insert restirction. This led to the wastage of disk space in case of huge databases. 2) Administrators might find column level security difficult to implement and need special training or reading in Database security. Paras. ------------------------------------------------------------ Message no. 669[Branch from no. 668] Posted by Paras Pradhan (ppradhan) on Tuesday, October 24, 2006 7:57pm Subject: Re: Fun Two students were talking about the American Astronauts. One said to the other, "What's the big deal about going to the moon-anybody can go to the moon. We are genious and we will go direct to the sun." "But if we get within 13 million miles from the sun, we'll melt." And the first answered, "So what, we'll go at night." :) Paras. ------------------------------------------------------------ Message no. 670[Branch from no. 665] Posted by Paras Pradhan (ppradhan) on Tuesday, October 24, 2006 8:00pm Subject: Re: Virtual Private Database (2) Gnaneshwar, Regarding management of row level security and column security, i guess there are both positive and negative aspects for administration. Paras. ------------------------------------------------------------ Message no. 671[Branch from no. 667] Posted by Paras Pradhan (ppradhan) on Tuesday, October 24, 2006 8:01pm Subject: Re: JoKe This is a good joke. haha Paras. ------------------------------------------------------------ Message no. 672[Branch from no. 662] Posted by Lakshmikar Padmaraju (lpadmaraju) on Wednesday, October 25, 2006 5:02pm Subject: Re: Virtual Private Database (2) 1. What is row level security? Row level security is one of the data security features offered by oracle to restrict users where they can only access data belongs to their department or group. One data model serves all departments, and all data for specific entity from all departments reside in one table but each department sees only its own data. This is typical situation for a virtual private database. You can implement row level security using the View objects, triggers, application context and DBMS.RLS package. 2. What is column level security? Column-level VPD enables you to enforce row-level security when a security-relevant column is referenced in a query. You can apply column-level VPD to tables and views, but not to synonyms. By specifying the security-relevant column name with the SEC_RELEVANT_COLS parameter of the DBMS_RLS.ADD_POLICY procedure, the security policy is applied whenever the column is referenced, explicitly or implicitly, in a query. For example, users outside of the HR department typically are allowed to view only their own Social Security numbers. When a sales clerk initiates the query SELECT fname, lname, ssn FROM emp; the function implementing the security policy returns the predicate ssn='my_ssn' and the database rewrites the query and executes SELECT fname, lname, ssn FROM emp WHERE ssn = 'my_ssn'; 3. What are the advantages of row level security, and column level security? --Implementation of security policies that is difficult to maintain with views or queries. --Isolating applications from implementing security polices -- Multilevel security and fine grained security with row-level access control -- Integrated security across the entire set of applications accessing the database deployed in the enterprise -- Auditing can now be done more easily using Oracles built in features --with Row level security it is easy to maintain the business rules and security implementations because it is done through one PL/SQL procedure per table instead of being spread throughout the applications code. 4. What are the disadvantages of row level security, and column level security? --Row-level triggers do add a significant overhead to processing, and in particular they block some of Oracle’s high-efficiency mechanisms and convert array processing into single-row processing. --Also have problems when export is used to export data the row level security policy rules still apply. It Export warns that it may not export all of the data requested because of row level security rules. --There are issues with referential integrity where it can be possible to update records where this should not be allowed by the security policy, by using the ON UPDATE SET NULL integrity constraint. It is also possible to delete records that again should not be allowed to be deleted by using the ON DELETE CASCADE integrity constraint. ------------------------------------------- Now fun part Three is equal to four Theorem: 3=4 Proof: Suppose: a + b = c This can also be written as: 4a - 3a + 4b - 3b = 4c - 3c After reorganizing: 4a + 4b - 4c = 3a + 3b - 3c Take the constants out of the brackets: 4 * (a+b-c) = 3 * (a+b-c) Remove the same term left and right: 4 = 3 ------------------------------------- Raju. ------------------------------------------------------------ Message no. 674[Branch from no. 670] Posted by Sagun Piya (srpiya2) on Thursday, October 26, 2006 8:32pm Subject: Re: Virtual Private Database (2) you are correct paras. It depends upon the situation. Sometimes it is worth not to use row-level security if database is owned by single dept. Sagun ------------------------------------------------------------ Message no. 675[Branch from no. 667] Posted by Sagun Piya (srpiya2) on Thursday, October 26, 2006 8:32pm Subject: Re: JoKe good one bukka sagun ------------------------------------------------------------ Message no. 676[Branch from no. 662] Posted by Sagun Piya (srpiya2) on Thursday, October 26, 2006 9:16pm Subject: Re: Virtual Private Database (2) 1. What is row level security? Row level security is based on custom triggers and functions, which set session context and define user access to the data on a row level. VPD’s row-level security allows you to restrict access to records based on a security policy implemented in PL/SQL that describes the rules governing access to the data rows. 2. What is column level security? Column level security defines user access to the data on a column level. Virtual private database and column access using oracle extended the vpd feature from rows to columns. Oracle has the capability to restrict update or insert at the column level, using Grant Update and Insert. 3. What are the advantages of row level security, and column level security? The advantages of row level security and column level security are as follows. i. Oracle's row level security and column level security provide a great improvement for this type of application where many users must connect to the same data but be segregated based on what parts of that data they are allowed to view and edit. ii. Maintenance becomes easier as now the business rules and security implementations are done through one PL/SQL procedure per table instead of being spread throughout the applications code. iii. Row level security is implemented as close to the data as possible, the loophole of accessing the data with a share account from a tool such as SQL plus is solved. iv. Security policies can be associated with both database base tables and also database views. v. Using row level security makes the application more manageable due to simpler designs and less potential code. 4. What are the disadvantages of row level security, and column level security? i. It may be really difficult to manage large number of registered users accessing the same database. ii. Lot of care should be taken while implementing these kinds of securities. Sagun ------------------------------------------------------------ Message no. 677 Posted by Sagun Piya (srpiya2) on Thursday, October 26, 2006 9:28pm Subject: funn stuffs Only in America... Only in America...can a pizza get to your house faster than an ambulance... Only in America...are there handicap parking places in front of a skating rink... Only in America...do people order double cheese burgers, a large fry, and a diet coke... Only in America...do banks leave both doors open and then chain the pens to the counters... Only in America...do we leave cars worth thousands of dollars in the driveway and leave useless things and junk in boxes in the garage... Only in America...do we use answering machines to screen calls and then have call waiting so we won't miss a call from someone we didn't want to talk to in the first place... Only in America...do we buy hot dogs in packages of ten and buns in packages of eight... Sagun ------------------------------------------------------------ Message no. 678[Branch from no. 672] Posted by Kelsey Pooley (kjpooley) on Thursday, October 26, 2006 11:03pm Subject: Re: Virtual Private Database (2) Raju, Thanks for the detailed explanation. I always learn a lot from the information you post on the discussion board. Kelsey ------------------------------------------------------------ Message no. 679[Branch from no. 662] Posted by Kelsey Pooley (kjpooley) on Friday, October 27, 2006 12:16am Subject: Re: Virtual Private Database (2) What is row level security? Row level security is used to make sure that a user can only access rows that belong to them, allowing data from different departments or even different companies to be stored and managed within one database. It is implemented in a function that detects the role of the user and filters which rows of data are returned. What is column level security? Column level security is similar to row level security but it is used to hide columns (which translate to specific fields within a row) from a user. What are the advantages of row level security, and column level security? The main advantage of row level security is that data from different departments or companies can be contained within one database, but can be managed so that each entity can only access its own data. This makes database management much simpler. Also, the entire process is transparent to the application that issues the query. The security is tightly fixed to the data it protects. It is consistent, centrally managed, and it can’t be bypassed. By using VPD the database implements the security policies and therefore any application using the data will have the security policies automatically applied. What are the disadvantages of row level security, and column level security? Because the security is so tightly fixed to the data it protects (even the dba and the data owner can’t bypass the policy), row level security can cause problems with dba tasks, such as backing up the data. If you perform an export, either as a data owner or a dba while a row level security policy is enabled, you may end up with a backup file with no data. Also, complex policies can cause delays in the database response time. Joke: A helicopter was flying around above Seattle yesterday when an electrical malfunction disabled all of the aircraft's electronic navigation and communications equipment. Due to the clouds and haze, the pilot could not determine the helicopter's position and course to steer to the airport. The pilot saw a tall building, flew toward it, circled, drew a handwritten sign, and held it in the helicopter's window. The pilot's sign said "WHERE AM I?" in large letters. People in the tall building quickly responded to the aircraft, drew a large sign, and held it in a building window. Their sign said "YOU ARE IN A HELICOPTER.” The pilot smiled, waved, looked at his map, determined the course to steer to the Seattle airport, and landed safely. After they were on the ground, the co-pilot asked the pilot how the "YOU ARE IN A HELICOPTER" sign helped determine their position? The pilot responded "I knew that had to be the MICROSOFT building, because similar to their help- lines, they gave me a technically correct but totally useless answer!" Kelsey ------------------------------------------------------------ Message no. 680[Branch from no. 679] Posted by Rhonda Nichols (renichols2) on Friday, October 27, 2006 10:03am Subject: Re: Virtual Private Database (2) Row level security is when you determine which rows are returned based on the user logged in. In other words, granting access at the row level and not object level. The result is that any individual user can see a completely different set of data than other users—based on what they are authorized to see. The main advantage is the security that can be placed on the data to comply with privacy policies. Other advantages include easier maintenance since the business rules and security implementations are done through one PL/SQL procedure per table, auditing is easier, more manageable due to simpler designs and less potential code, protection against ad-hoc queries as the toll does not matter anymore because the data is secured at the source. Some of the disadvantages include the overhead created by policy functions, custom solutions are needed, management of large numbers of users. Column level security prevents (or permits) access to individual columns within rows. But because access is controlled by adding a where clause, column level access can only be maintained by defining multiple views for each class of end user. You can also go a step further and allow (or restrict) updating or inserting into the columns by using the sql GRANT UPDATE (column) or GRANT INSERT (column). -Rhonda HUMOR….. The Oldest Profession There was a doctor, a civil engineer, and a computer scientist sitting around late one evening, and they got to discussing which was the oldest profession. The doctor pointed out that according to Biblical tradition, God created Eve from Adam's rib. This obviously required surgery, so therefore that was the oldest profession in the world. The engineer countered with an earlier passage in the Bible that stated that God created order from the chaos, and that was most certainly the biggest and best civil engineering example ever, and also proved that his profession was the oldest profession. The computer scientist leaned back in her chair, and with a sly smile responded, "Yes, but who do you think created the chaos?" ------------------------------------------------------------ Message no. 681[Branch from no. 672] Posted by Rhonda Nichols (renichols2) on Friday, October 27, 2006 10:04am Subject: Re: Virtual Private Database (2) Thanks Raju! Always a pleasure to read your explanantions. -Rhonda ------------------------------------------------------------ Message no. 684[Branch from no. 662] Posted by Krishnamurth Ashwini (kashwini) on Friday, October 27, 2006 1:06pm Subject: Re: Virtual Private Database 1. What is row level security? Oracle's Virtual Private Database technology provides a logical and elegant method for applying security to the data within database tables. Oracle's row-level security feature provides access control at the individual row level. Row-level security restricts users to access to specific rows in a table instead of revealing the entire table to a user who has any privileges on the table. As a result of this the user sees only the set of data that he is authorized to see. Virtual Private Database’s row-level security allows you to restrict access to records based on a security policy implemented in PL/SQL. 2. What is column level security? Providing users with access to particular column data in a database requires that users are granted privileges to database table columns so that security policies can be applied at the column level rather than at the row level. This can be done without having to create separate tables for the labeled columns and without associating join operations. In response to a request for access to data in a particular column of a database table, column relevant data sensitivity labels and user sensitivity permission are used to determine whether the requesting user is granted access to data in the labeled column. If the requesting user's sensitivity permission meets or exceeds the sensitivity of the requested data, then return of the data is allowed. 3. What are the advantages of row level security and column level security? • Using row level security makes the application more manageable due to simpler designs and less potential code. • Because row level security is implemented as close to the data as possible, the loophole of accessing the data with a share account from a tool such as SQL*Plus is solved. • The issue of having to use shared accounts is no longer a problem as application roles / groups of users do not need to be segregated for the purpose of hard coding views onto the data. • Security policies can be associated with both database base tables and also database views. 4. What are the disadvantages of row level security and column level security? • Because access is controlled by adding a where clause, column-level access can only be maintained by defining multiple views for each class of end user. • It is hard to write an audit script that defines the exact access for each specified user. This problem becomes even more acute when security methods are mixed. • Unlike security that is managed externally, VPD requires that an Oracle user ID be defined for every person who connects to the database. This adds maintenance and overhead Ash ------------------------------------------------------------ Message no. 685[Branch from no. 662] Posted by Krishnamurth Ashwini (kashwini) on Friday, October 27, 2006 1:08pm Subject: Joke A husband read an article to his wife about how many words women use a day... 30,000 to a man's 15,000. The wife replied, "The reason has to be because we have to repeat everything to men... The husband then turned to his wife and asked, "What?" Ash ------------------------------------------------------------ Message no. 686[Branch from no. 681] Posted by Lakshmikar Padmaraju (lpadmaraju) on Friday, October 27, 2006 3:47pm Subject: Re: Virtual Private Database (2) Hi Rhonda & Kelsey, Thanks for your comments; this is good way to learn from each other. I wish some of the other classes had followed similar practice, because instead of learning from one source (BOOK, Classroom teaching) we get much more information from across the class, That means in referential integrity terms information flow from Many to Many instead of One to Many. I don't know about others I am enjoying this class and discussion format. We had similar format for Our Networking class taught by Dr. G. I also enjoyed that class too. Raju. ------------------------------------------------------------ Message no. 687[Branch from no. 677] Posted by Lakshmikar Padmaraju (lpadmaraju) on Friday, October 27, 2006 3:52pm Subject: Re: funn stuffs Nice one Sagun. Thanks Raju. ------------------------------------------------------------ Message no. 691[Branch from no. 663] Posted by Anjana Divakar (adivakar) on Saturday, October 28, 2006 8:53am Subject: Re: Virtual Private Database (2) A policeman spots a woman driving and knitting at the same time. Driving up beside her, he shouts out the window, "Pull over!" "No," she shouts back, "a pair of socks!" ------------------------------------------------------------ Message no. 692[Branch from no. 677] Posted by Anjana Divakar (adivakar) on Saturday, October 28, 2006 8:55am Subject: Re: funn stuffs really funny sagun :) ------------------------------------------------------------ Message no. 694[Branch from no. 668] Posted by Krishnamurth Ashwini (kashwini) on Saturday, October 28, 2006 10:37am Subject: Re: Virtual Private Database Nice points. Also, users can retrieve and update data from only the columns they're authorized to change. Column-level control can give good program control within applications, if you can retrieve and use the information hidden within SQL Server's internal system tables. This type of security allows you to control the access of information according to your needs. Column-level security is a nice tool for restricting access to specific columns within the database table. Ash ------------------------------------------------------------ Message no. 696[Branch from no. 692] Posted by Venkat Munagala (vrmunagala) on Saturday, October 28, 2006 2:59pm Subject: Re: funn stuffs good one sagun. ------------------------------------------------------------ Message no. 697[Branch from no. 696] Posted by Venkat Munagala (vrmunagala) on Saturday, October 28, 2006 3:10pm Subject: Re: funn stuffs --- The system crash song--- SYSTEM CRASH (to the tune of "The Monster Mash") I was working in the lab, late one night When my eyes beheld an eerie sight, Some smoke from our VAX began to rise And suddenly, to my surprise... [chorus] (There was a crash) There was a system crash (A mighty crash) I heard the disk heads smash (A system crash) It came down in a flash (There was a crash) A fatal system crash The lab manager then appeared from his room, Said: "I don't want to be a prophet of doom, But we had one like this just the other day Which blew up 4 megs and the SBA" [chorus] The system had just been booted, diagnostics had all run through, When a power fluck made it all run amuck, then SCOTTY and IRVING blew too So we'd lost all our VAXes in less than one night When a VP came in and said: "hey, that's all right, I'll loan you a Venus - here's what to do When you call up Support, tell them Gordon sent you... [chorus] ------------------------------------------------------------ Message no. 698[Branch from no. 662] Posted by Suresh Methuku (smethuku) on Saturday, October 28, 2006 5:50pm Subject: Re: Virtual Private Database (2) Row level security: Row level security can be defined as fine-grained access control ensuring that security is applied not only to the object (for example a database table) but also to each row within the object. Column level security has three possible definitions: 1. Preventing access to the column 2. Masking the column values 3. Controlling access to the values within a column Advantages of row level security and column level security: • Implementation of security policies that is difficult to maintain with views or queries. • Isolating applications from implementing security policies. • Multilevel security and fine grained security with row-level access control. • Integrated security across the entire set of applications accessing the database deployed in the enterprise. • Maintenance becomes easier as now the business rules and security implementations are done through one PL/SQL procedure per table instead of being spread throughout the applications code. Maintenance becomes easier as now the business rules and security implementations are done through one PL/SQL procedure per table instead of being spread throughout the applications code. Disadvantages: It is not possible with row level security policy function to access the base table to which policy function is attached, and there are some issues with referential integrity. Now fun part: Silly Computer Acronyms PCMCIA: People Can't Memorize Computer Industry Acronyms ISDN: It Still Does Nothing APPLE: Arrogance Produces Profit-Losing Entity SCSI: System Can't See It DOS: Defective Operating System BASIC: Bill's Attempt to Seize Industry Control IBM: I Blame Microsoft DEC: Do Expect Cuts CD-ROM: Consumer Device - Rendered Obsolete in Months OS/2: Obsolete Soon, Too. WWW: World Wide Wait MACINTOSH: Most Applications Crash; If Not, The Operating System Hangs PENTIUM: Produces Erroneous Numbers Thru Incorrect Understanding of Mathematics AMIGA: A Merely Insignificant Game Addiction LISP: Lots of Infuriating & Silly Parentheses MIPS: Meaningless Indication of Processor Speed WINDOWS: Will Install Needless Data On Whole System RISC: Reduced Into Silly Code cheers suresh ------------------------------------------------------------ Message no. 699[Branch from no. 677] Posted by Suresh Methuku (smethuku) on Saturday, October 28, 2006 6:04pm Subject: Re: funn stuffs Nice one sagun. suresh ------------------------------------------------------------ Message no. 700[Branch from no. 698] Posted by Venkat Munagala (vrmunagala) on Saturday, October 28, 2006 7:23pm Subject: Re: Virtual Private Database (2) Good collection of Acronyms suresh. ------------------------------------------------------------ Message no. 701[Branch from no. 698] Posted by Ravinder Gaur (rgaur) on Saturday, October 28, 2006 7:36pm Subject: Re: Virtual Private Database (2) Row Level Security in Oracle ============================ Row Level Security (or VPD) is a security feature in Oracle that allows enforcing finer granular level security access by attaching security policies directly on tables, view or synonyms. It attaches a SQL predicate in the form of a WHERE clause to a SQL statement dynamically behind the scenes. For example, the following simple query is executed by someone who is allowed to see only his/her record -- SELECT * FROM EMP; The VPD security policy on the EMP table enforces a predicate on this SQL, so when it is submitted for parsing, it gets rewritten as follows - SELECT * FROM EMP WHERE ENAME = ; Only a single record is pulled back by virtue of fine grained access control and the Application context value of the user session executing the above query. Column Level Security in Oracle =============================== Oracle Database 10g takes the VPD security one step further by allowing column level security making it more fine-grained. Under Oracle 10g, the security policy can be attached to a certain column or columns and the predicate is applied only when the column(s) appears in the SELECT clause. For example, if security policy is attached to "SSN" column of the EMP table, the following SQLs will execute the policy (ie. attach the predicate) -- SELECT * FROM EMP; or SELECT SSN,ENAME FROM EMP; However, the following SQL will NOT execute the security policy (ie no predicate attached) since SSN column is not referenced -- SELECT ENAME,DOB,STATUS FROM EMP; Advantages of Row Level and Column Level Security ================================================= The foremost advantage of using VPD security (whether row level or column level) is that data access is secured at the source. The predicate is attached right at the parse time (which is before execution phase), so there is no question of security getting bypassed. Also, Oracle provides a ready made out-of-the-box VPD implementation called Oracle Label Security. Fine Grained Auditing can also be achieved using the security functions. Also, once a policy function and application context gets developed, it can applied against several tables provided they have a common column against which security is desired. Also security can be built at various levels like for a University Application, it can be set at Campus, College, Department, Section, Sub-section levels. Disadvantages of Row Level and Column Level Security ==================================================== The complex nature of setting the policy makes it difficult esp. for naive administrators to properly manage and maintain this otherwise useful feature. Also there are performance implications esp. when a dynamic policy is attached to a highly active table in the database (we recently struggled tuning a policy function in Banner Application). Also, the DBA has to be careful to run the policy functions using the SYS id (SYSTEM won't work) and also use either SYS or an exempted id while exporting data. ------------------------------------------------------------ Message no. 702[Branch from no. 701] Posted by Ravinder Gaur (rgaur) on Saturday, October 28, 2006 7:39pm Subject: Re: Virtual Private Database (2) Now the fun part -- The boss of a big company needed to call one of his employees about an urgent problem with one of the main computers. He dialed the employee's home phone number and was greeted with a child's whispered, "Hello?" Feeling put out at the inconvenience of having to talk to a youngster, the boss asked, "Is your Daddy home?" "Yes," whispered the small voice. "May I talk with him?" the man asked. To the surprise of the boss, the small voice whispered, "No." Wanting to talk with an adult, the boss asked, "Is your Mommy there?" "Yes," came the answer. "May I talk with her?" Again the small voice whispered, "No." Knowing that it was not likely that a young child would be left home alone, the boss decided he would just leave a message with the person who should be there watching over the child. "Is there anyone else there in your house?" the boss asked the child. "Yes," whispered the child, "a policeman." Wondering what a cop would be doing at his employee's home, the Boss asked, "May I speak with the policeman?" "No, he's busy," whispered the child. "Busy doing what?" asked the boss. "Talking to Daddy and Mommy and the fireman," came the whispered answer. Growing concerned and even worried as he heard what sounded like a helicopter through the earpiece on the phone, the boss asked, "What is that noise?" "A hello-copper," answered the whispering voice. "What is going on there?" asked the boss, now very alarmed. In an awed hushed voice the child answered, "The search team just landed the hello-copper." Alarmed, concerned and more than just a little frustrated the boss asked, "What are they searching for?" Still whispering, the young voice replied, along with a muffled giggle "ME." ---------------------- - Ravi ------------------------------------------------------------ Message no. 704[Branch from no. 662] Posted by Daniel Thurston (dsthurston) on Saturday, October 28, 2006 9:46pm Subject: Re: Virtual Private Database (2) 1. What is row level security? A mechanism of Oracle's that allows one to apply security measures to each row of a table. For instance you can allow a user to be able to see specific rows of a table and not others. 2. What is column level security? Another mechanism of oracle's that allows one to apply security measures to each column of a table. Thus blocking a specific users ability to see or possibly interact with certain columns of a table. Both of these together (or separately is suppose) one can create what oracle calls a Virtual Private Database (VPD). Instead of having multiple DB instances one can have the data in one DB. Utilizing a VPD it is possible to restrict access to certain areas of the database. All this can be transparent to the user thus making it seem as though they are acting on their own individual DB. 3. What are the advantages of row level security, and column level security? -Easier to maintain one DB than many. -Implemented on the near data for better security -can have multiple policies -easy changes to security policies 4. What are the disadvantages of row level security, and column level security? -can get convoluted in large databases/ many users -multiple views and be confusing One could think of Column and Row level security as a giant Virtual Sharpie; blacking out rows and columns of the greater DB that the user is not supposed to see. Hmmm, it is of course more fine grained than that as sometimes you can see column data but not change it and some you can . . so maybe virtual white-out and a virtual correcting pen on the virtual DB . . .but I digress. . .horribly :) Cheers DanT ------------------------------------------------------------ Message no. 705[Branch from no. 677] Posted by Daniel Thurston (dsthurston) on Saturday, October 28, 2006 9:50pm Subject: Re: funn stuffs good one. ------------------------------------------------------------ Message no. 707[Branch from no. 702] Posted by Gnaneshwar Bukka (gbukka) on Sunday, October 29, 2006 10:59am Subject: Re: Virtual Private Database (2) Was a very naughty joke, thank you. Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 708[Branch from no. 677] Posted by Gnaneshwar Bukka (gbukka) on Sunday, October 29, 2006 11:01am Subject: Re: funn stuffs nice one sagun. Gnaneshwar Bukka. ------------------------------------------------------------