Obsessed with Oracle PL/SQL

Virtual Private Database (VPD), also referred to as row-level security or RLS, is a feature built into the Oracle Database that allows you to set up security policies on tables that restrict which rows a user can see or change based on the policy logic. One of the nicest things about VPD is that this logic (and the fact that a filter is being applied) is completely invisible to the user. They just see the data relevant to them and none the wiser about all that other data in the data. Here's a simple example to drive the point home: suppose I am building a health care application and it contains a patients table. The security policy is straightforward: A patient can only see their own information. A doctor can see only the information about their own patients. A clinic administrator can see information only about the patients in their clinic. In all three cases, the user would sign on to the application and execute this identical query and only  their  rows would app...

Virtual Private Database (VPD), also referred to as row-level security or RLS, is a feature built into the Oracle Database that allows you to set up security policies on tables that restrict which rows a user can see or change based on the policy logic. One of the nicest things about VPD is that this logic (and the fact that a filter is being applied) is completely invisible to the user. They just see the data relevant to them and none the wiser about all that other data in the data. Here's a simple example to drive the point home: suppose I am building a health care application and it contains a patients table. The security policy is straightforward: A patient can only see their own information. A doctor can see only the information about their own patients. A clinic administrator can see information only about the patients in their clinic. In all three cases, the user would sign on to the application and execute the same query: SELECT * FROM patients and only thei...

Oracle Database 12c offers several enhancements to improve security in your PL/SQL program units. These features include: Avoid privilege escalation: Use the INHERIT [ANY] PRIVILEGES privilege to make it impossible for a lower-privileged user to take advantage of a higher-privileged user via an invoker rights unit. I covered this topic here . Code-based access control: fine-tune access to database objects inside program units by  granting roles to program units ( doc ), rather than - or in addition to - roles granted to schemas. That's the topic for this post. Note: Oracle Magazine also offers this content (both blog posts) in a single article here . Securing your database – and properly restricting access to the data and data structures within your database – ranks at the very top of the "most important things to do" list when building applications. The best way to avoid unintended access or actions is to apply the "least privilege" principle: give a...

Oracle Database 12c offers several enhancements to improve security in your PL/SQL program units.  These features include: Code-based access control: fine-tune access to database objects inside program units by granting roles to program units , rather than - or in addition to - roles granted to schemas. Avoid privilege escalation: Use the INHERIT [ANY] PRIVILEGES privilege to make it impossible for a lower-privileged user to take advantage of a higher-privileged user via an invoker rights unit. In part 1, I will explore the use of INHERIT [ANY] PRIVILEGES to clamp down on possible privilege escalation. Which means, of course, that I should first give you an example of what privilege escalation is, how it can come about, and what sorts of damage it can do. Suppose that there is a schema named POWERFUL_BOSS in the database instance, which is the boss's schema and has lots of privileges on many critical database objects, including the PERFORMANCE_REVIEWS table.  ...