Obsessed with Oracle PL/SQL

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...

The best way to build applications on top of Oracle Database is to build lots of APIs (application programmatic interfaces) to your data with PL/SQL packages. And that means you end up with lots of packages in your application. That's just great! Now, when a user selects a feature of your application that in turn references an element in a package (invokes a procedure or function, or reads the value of a constant), that package must be instantiated and initialized for that user's session. As described in the documentation : When a session references a package item, Oracle Database instantiates the package for that session. Every session that references a package has its own instantiation of that package.  When Oracle Database instantiates a package, it initializes it. Initialization includes whichever of the following are applicable: Assigning initial values to public constants Assigning initial values to public variables whose declarations specify them Executing t...

When a SQL statement executes a user-defined function, your users pay the price of a context switch , which can be expensive, especially if the function is called in the WHERE clause. Even worse, if that function itself contains a SQL statement, you can run into data consistency issues. Fortunately, you can use PL/Scope in  Oracle Database 12c Release 2 to find all the SQL statements in your PL/SQL code that call a user-defined function, and then analyze from there. I go through the steps below. You can run and download all the code on LiveSQL . First, I turn on the gathering of PL/Scope data in my session: ALTER SESSION SET plscope_settings='identifiers:all, statements:all' / Then I create a table, two functions and a procedure, so I can demonstrate this great application of PL/Scope: CREATE TABLE my_data (n NUMBER) / CREATE OR REPLACE FUNCTION my_function1 RETURN NUMBER AUTHID DEFINER IS BEGIN RETURN 1; END; / CREATE OR REPLACE FUNCTION my_function2 ...

Oracle Database makes it easy to not only write and execute SQL from within PL/SQL, but also to execute your own user-defined functions inside SQL. Suppose, for example, I have built the following function to return a sub-string between start and end locations: FUNCTION betwnstr ( string_in IN VARCHAR2 , start_in IN INTEGER , end_in IN INTEGER ) RETURN VARCHAR2 IS BEGIN RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1)); END betwnstr; I can then call it in a SQL statement: SELECT bewtnstr (last_name, 3, 6) FROM employees Nice, right? But there's a catch (well, of course, right? No free lunches.). When the SQL engine encounters the PL/SQL function, it has to switch context to the PL/SQL engine to execute the function. Before it can do the switch or hand-off, it must also prepare the values to pass as actual arguments to the formal parameters of the function. All of that takes time. And we'd much rather it didn't. Since...

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.  ...

I've been publishing Oracle Magazine articles, blog posts and LiveSQL scripts on new PL/SQL features in Oracle Database 12c Release 2  (there, are those enough hyperlinks?). As have others. I thought it might be helpful to provide a single reference post from which you could check out all the others. I also include links to content from other experts who have posted on the same topics. I will update this post as more resources are published. First some overview articles that you will find as solid starting points: 12 Things Developers Will Love About Oracle Database 12c Release 2 , covering SQL and PL/SQL features, from Chris Saxon . It even comes with an infographic ! The Power of Cloud PL/SQL , my Oracle Magazine roundup article. No, I did not choose the title of the article. And now for specific enhancements.... PL/Scope Discovers SQL! PL/Scope is a compiler tool that gathers information about identifiers (as of 11.1) and SQL statements (as of 12.2) in you...

Way back in Oracle Database 12c Release 1, the PL/SQL team added whitelisting to the language. This means you can use the ACCESSIBLE BY clause to specify the "white list" of program units that are allowed to invoke another program unit (schema-level procedure, function, package). For details on the 12.1 ACCESSIBLE BY feature set, check out my  Oracle Magazine  article,  When Packages Need to Lose Weight . In that article, I step through the process of breaking up a large package body into "sub" packages whose access is restricted through use of the ACCESSIBLE BY  feature. I'll wait while you read the article. Tick, tock, tick, tock.... OK, all caught up now? Great! In 12.2, there are just one enhancement, but a really nice one: You can now specify whitelisting for a subprogram within a package.  This is a very nice fine-tuning and is sure to come in handy. In this post, I also show how you can specify the "unit kind" (program unit type) ...

PL/Scope is a compiler tool that gathers information about identifiers (as of 11.1) and SQL statements (as of 12.2) in your PL/SQL code. You can do all sorts of amazing deep-dive analysis of your code with PL/Scope, answering questions like: Where is a variable assigned a value in a program? What variables are declared inside a given program? Which programs call another program (that is, you can get down to a subprogram in a package)? Find the type of a variable from its declaration. Show where specific columns are referenced Find all program units performing specific DML operations on table (and help you consolidate such statements) Locate all SQL statements containing hints Find all dynamic SQL usages – ideal for getting rid of SQL injection vulnerabilities Show all locations in your code where you commit or rollback And my latest favorite: Locate multiple appearances of same "canonical" SQL statement. What does this mean and why does it matter? One great f...

Starting with Oracle Database 12 c  Release 2 (12.2), we can now use static expressions* where previously only literal constants were allowed. Here are some examples (also available in this LiveSQL script ): CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER IS c_max_length constant integer := 32767; SUBTYPE maxvarchar2 IS VARCHAR2 (c_max_length); END; / DECLARE l_big_string1 VARCHAR2 (pkg.c_max_length) := 'So big....'; l_big_String2 pkg.maxvarchar2 := 'So big via packaged subtype....'; l_half_big VARCHAR2 (pkg.c_max_length / 2) := 'So big....'; BEGIN DBMS_OUTPUT.PUT_LINE (l_big_string1); DBMS_OUTPUT.PUT_LINE (l_big_string2); END; / As you can see from this code, static expressions can now be used in subtype declarations. The definition of static expressions is expanded to include all the PL/SQL scalar types and a much wider range of operators. Character operands are restricted to a safe subset of the ASCII character set. Operat...

"Persistent code base"? What's that? Well, I suppose I could have said "aging" or "legacy" code base, but that doesn't capture my point, which starts with: If I were a Javascript programmer, I would probably be excited if my code lasted more than a year . As an Oracle Database developer, though, it is not unreasonable to expect that my code will be in production for years, perhaps decades . Now, that's persistent code. And why is that? Because, well, DATABASE . The database is the repository for your enterprise. Certainly it contains the data, and if you fully leverage the database properly , it will also contain your business logic. And while it is not all that painfully disruptive to rewrite your UI code, it can be business-threatening to do any of the following: Switch your database technology Upgrade your database software too quickly Rewrite your business logic in a new scripting language  So your database...

In my Oracle Magazine article, When Packages Need to Lose Weight , I step through the process of breaking up a large package body into "sub" packages whose access is restricted through use of the new-to- Oracle Database 12c ACCESSIBLE BY feature. The idea, to sum it all up, is that once I move code from my original too-large-to-manage package body to another package, the header moves to the spec of that package. This means that formerly-private functionality is now accessible to anyone with execute authority on that package. For that reason, I stated: The body of em_central shrinks to a fraction of its former self, because the body of each procedure is simply a redirect into the em_central_a and em_central_b packages. The subprograms in these packages   should   be invoked only by em_central. A reader contacted me with this question: For me this means that newly written subprograms should not invoke the new packages em_central_a and em_central_b directly. So the AC...