I’m more than a little disappointed at people not being serious about information security. One of my customers asked me to help load data from a school system into an apex application I designed for some years back. The excel spreadsheet received from the school system has teacher names and social security numbers. The excel file was not encrypted in any way shape or form. I’ll be contacting the school system on Monday to resolve this issue. If it’s not resolved in a short period of time, I’ll be sending the data to their local news paper. This has got to stop.
Chatting with a friend this morning, we were talking about todo list and being overwhelmed by everything that needs to get done. After sharing with her mindfulness meditation, that helps me keep the “chattering monkeys” at bay and has improved my focus. I mentioned that I’ve gotten away from todo list and now write out the outcome I want from today. Coming up with an outcome for the day can be quite difficult; but once you get the hang of it; the outcome for today will be self evident.
Back on 3/1 I needed to bring this practice back into focus and wrote this note to myself.
In my outcome for the day, I also establish a performance goal for the day. This performance goal can be related to my physical performance while exercising and/or my performance around people. An example of this would be even though my chair is very comfortable, I will do twenty minutes on the rowing machine and lift weight for thirty minutes. It will also include what distance will I do in those twenty minutes, and what weights / sets will I do in my exercise.
My professional outcomes for the day, may be; Catch the problem in production, before the customer catches it. This way, the customer is not surprised when there is an issue. They get an FYI instead of, them sending my team an email saying there is a problem.
The beauty of this is, when you defined the outcome; what you need to do to get that outcome should become self-evident.
Just a short post, if you design, develop, maintain, or administer applications, you need to read this document. The Ten Most Critical Web Application Security Risks.
If you’re like me, you frequently have many environments open at the same time in putty, sqlcl, sqldeveloper, or other tools. This happened quite a few years back, I was switching between my dev, test, and production environments, doing some work, then the “Oh S^#t” happened. I shutdown production when I thought I was in dev.
You can change your prompt to show what environment you are working in; yea’ that works … but I’ve taken this a couple steps further. Out of habit, if I’m not actively doing work in production, I close it. Second, I changed to colors of my screens to tell me what environment I’m in at a glance. Test is green, dev is blue and production is RED.
In putty, bring up the configuration dialog. Load the configuration for the environment, click on color under the Window tree, select default foreground, then modify button and select your foreground color. Then select default background, then modify button and select your background color. Go back to session tree and click save.
There you have it, this will make things a lot safer when you have multiple environments open.
I recommend closing your production connection if you are not actively using it. You can’t be too safe.
Grab a cup of coffee or a cup of tea. This is not a short post; There is a lot to explain, and many point are repeated. You need to understand all the in’s and out’s of CBAC. However; once you have an understanding, CBAC really quite easy to impalement.
A lot about Code Based Access Control is not intuitive. In fact a lot of people I talk to are confused about what CBAC is and what CBAC is not. The basic premise of CBAC is that a subprogram of a package can only execute only using the privileges that have been granted to the package through a role.
NOTE: What I’m explaining is a multi-schema model. I’m intentionally keeping the model simple to show just the CBAC aspect of a secure architecture. This model is using three schemas, hr that holds data and hr_api that is going to hold packages that will access the data, and hr_decls that holds common types that are used across schemas. Also note, this is not the full secure architecture. There are a number of elements of my secure architecture I am leaving out to focus on CBAC.
Before we go too far, we need to understand some subtleties about roles. In order to grant a role to a package, the role must first be granted to the owner of the package with either admin option, delegate option, or you must connect as sys to grant the role to the package and the owner of the package must have created the role. Yea’ that’s a bit to digest, so let’s examine the three different types of role grants that must be made.
Three different ways to grant a role to a package.
#1 --As a user with create role privileges and the ability to grant -- the required privileges to the role. ie: DBA conn rob_dba@demo create role <role>; grant <privilege> on <object owner>.<object name> to <role>; grant <role> to <package owner> with admin option; conn <package owner>@<instance> grant <role> to package <package name>;
#1 GRANTING THE ROLE TO THE PACKAGE OWNER WITH ADMIN OPTION: Granting the package owner the role with admin option works, but the package owner can grant the role to other schemas. Using a user with create role privilege and the ability to grant the required privileges on the underlying objects (I normally use my dba account rob_dba). This is an unnecessary security risk. Like I said, the package owner can now grant the role to other schemas, thereby making the trusted path weaker. I’m sure there are use cases for granting the package owner the role with admin option; however that is a corner case and should be justified, not the norm and quite frankly, I can not think of a use case that would justify using this method.
#2 -- as package owner, create the role.the package owner must have -- create role privileges. conn <package owner>@<instance> create role <role>; conn sys@<instance> as sysdba grant <role> to package <package owner>.<package name>;
#2 USING SYS TO GRANT THE ROLE TO A PACKAGE: The package owner must have the create role privilege and the package owner must have created the role. In this method, we are creating the role as the package owner and connecting as sys to grant the role to the package. I hope there is no need to explain what a bad idea it is to use the sys account. There is a huge security issue, doing work as sys.
#3 --As a user with create role privileges and the ability to grant -- the required privileges to the role. ie: DBA conn rob_dba@demo create role <role>; grant <privilege> on <object owner>.<object name> to <role>; grant <role> to <package owner> with delegate option; conn <package owner>@<instance> grant <role> to package <package name>;
#3 GRANTING THE ROLE TO THE PACKAGE OWNER WITH DELEGATE OPTION: This is the preferred method to grant a role to a package. It appears the same as #1; however we are granting the package owner the role with delegate option. Using this method, we can grant the package the required role, but the package owner can not grant the role outside of it’s schema. This is the least amount of privileges needed to execute the task.
Remember, we want to operate with the least amount of privilege and still be able to do our job.
The hr_decls schema:
My hr_decls schema is used to define common types that will be referenced between schemas. This is not required for using CBAC, however it makes referencing common types between schemas much easier and makes maintenance simpler. Once this package is created, execute is granted to a role (exec_emp_decl_role) and that role is granted to any users that need to use it and not compile code against it. Mainly this grant is for testing purposes. Note: PL/SQL grant execute through a role or grant execute directly? If you are executing a pl/sql package using an anonymous block, you can pick up the privilege through a role. If you are compiling a pl/sql package that references a pl/sql package in a different schema, you must grant execute on the package directly.
conn rob_dba@demo create role exec_emp_decl_role; conn hr_api@demo -- create the decls package. This package is used to create types -- that are used between schemas. create or replace package hr_decls.emp_decl authid current_user as cursor emp_cur is select * from hr.employees; subtype st_emp is emp_cur%rowtype; type tt_emp is table of st_emp index by pls_integer; end emp_decl; / -- usr1 (our test user) required this role to reference the package -- through an anonymous block grant execute on exec_emp_decl_role to usr1; -- hr_api requires a direct grant on the package hr_decls.emp_decl grant execute on hr_decls.emp_decl to hr_api;
create package hr_api.emp_select_pkg authid current_user as
Hey, we are granting a role to the package, why do we need to grant privileges on the underlying objects directly to the package owner? In order to compile your package, you need to grant privileges on the underlying object to the executing schema.
If you use authid definer (that is the default authid. So if you don’t specify current_user or definer, the package is created with definer’s rights.), the package will pick up the privileges from the schema it resides in. If we grant a lot of privileges to the schema the code resides in, or heaven forbid, the code resides in the schema with the data, and there is a flaw in your code; the hacker owns your database.
By using authid current_user, the package inherits the privileges of the user executing the code. If the user does not have any privileges on the underlying data objects then subprogram in a package can only execute with the privileges granted to the package through a role.
Now let’s start setting this up from the beginning. Connecting as a dba, or another appropriate user that has the proper privileges, we need to create a role, grant privileges to that role, and grant that role to hr_api with admin option.
We are going to create the role hr_emp_sel_role. Then we are going to grant to the role select on hr.employees.
create role hr_emp_sel_role; grant select on hr.employees to hr_emp_sel_role;
Once we have these roles and grants let’s grant it to hr_api with delegate option.
grant hr_emp_sel_role to hr_api with delegate option;
Here is a niggle; you need to get your package to compile. The schema, in this case hr_api needs to be granted select on the underlying data. So, we are going to grant select on hr.employees to hr_api. I don’t really like granting privileges directly to a schema, but it’s needed for the code to compile. Using this grant, the code that selects against hr.employees can compile. But remember, the code we are writing will be authid current_user. We now have the underlying roles with the appropriate grants to start building our application.
grant select on hr.employees to hr_api;
There is one more role you need. This is the role that will be granted execute on the api package, that will inturn be granted to the user.
create role EXEC_EMP_SEL_API_ROLE;
Here is the rolls and all the grants we have done so far.
conn rob_dba@demo create role hr_emp_sel_role; grant select on hr.employees to hr_emp_sel_role; grant hr_emp_sel_role to hr_api with delegate option; grant select on hr.employees to hr_api; create role EXEC_EMP_SEL_API_ROLE; grant execute on hr_decls.emp_decl to hr_api; grant EXEC_EMP_SEL_API_ROLE to usr1;
grant execute on hr_decls.emp_decl to exec_emp_decl_role; grant exec_emp_decl_role to usr1;
Now we need to connect as hr_api and start building the application. In my environment, hr_api is granted create procedure through a password protected role. We are creating one function in the package, sel_hr_emp_phone that returns an employees information based on the phone number.
conn hr_api@demo SET role hr_api_admin_role identified by My#Supper7Secret#Password2; -- note authid current_user. create or replace package hr_api.sel_emp_phone_api authid current_user as function sel_hr_emp_phone(p_phone hr.employees.phone_number%type) return hr_decls.emp_decl.tt_emp; end sel_emp_phone_api; /
grant hr_emp_sel_role to package sel_emp_phone_api;
We’ve flipped on the privilege the package needs. So sel_emp_phone_api can only execute with the privileges granted to it from the role, or privileges inherited from the user executing the package. (you must understand, the package can still inherit more privileges from the user.) Let’s finish off by filling in the package details.
create or replace package body hr_api.sel_emp_phone_api as function sel_hr_emp_phone(p_phone hr.employees.phone_number%type) return hr_decls.emp_decl.tt_emp is ltt_emp hr_decls.emp_decl.tt_emp; begin select * bulk collect into ltt_emp from hr.employees where phone_number = p_phone; return ltt_emp; exception when no_data_found then -- <fixme> insert error handler. raise_application_error(-20000,''); End sel_hr_emp_phone; end sel_emp_phone_api; /
We’ve got all the objects we need together, now we need two more grants to run this. We need to grant execute on hr_api.sel_emp_phone_api package to the exec_emp_sel_api_role and we need to grant the role to a user to execute the api. I have a test user usr1 that is used to test various security configurations. Here is the the setup that has already been done.This test user is granted create session, exec_emp_sel_api_role and exec_emp_decl_role.
conn rob_dba@demo create user usr1 identified by AHotRedKotenok; grant create session to usr1; create role exec_emp_decl_role; grant execute on hr_api.sel_emp_phone_api to exec_emp_sel_api_role; -- so the user can execute the api through an anonymous block. grant exec_emp_sel_api_role to usr1; -- so the user can reference the hr_decls.emp_decl package from an -- anonymous block. grant execute on hr_decls.emp_decl to exec_emp_decl_role; grant exec_emp_decl_role to usr1;
Now we can test this out. Connect as usr1, and call the function. Let’s see if we get anything back.
conn usr1@demo declare ltt_emp hr_decls.emp_decl.tt_emp; begin ltt_emp := hr_api.sel_emp_phone_api.sel_hr_emp_phone( p_phone => '+1.800.555.1212'); end; /
March 21 – 22: I will be speaking at Utah Oracle Users Group Training Days (and getting some Spring Skiing in.) http://utoug.org/TrainingDays I will be speaking on Holistic Database Security and Secure Coding. My Holistic Database Security presentation has come a long way over the past ten years. As new attack vectors, mistakes, mitigations come out I update this presentation. So, if you’ve seen this presentation before, don’t worry there is a lot of new material in there. My Secure Coding Presentation goes through coding standards, common errors, and how to implement a trusted path for you data.
April 18: I will be speaking at Twin Cities Oracle Users Group on Oracle Database Vault and a Hybrid Holistic Database Security presentation that will be focused at DBA’s. Many DBA’s fear or don’t like Oracle Database Vault, because it changes the paradigm of how they work. We are accustomed to being the God of our databases. We will be looking at how to make Database Vault your friend, and customizing it for your needs.
I will be in Moscow Russia and Helsinki Finland May 18 – 26. Speaking at Oracle Users Group Finland. http://www.ougf.fi/index.php/en/ Again I will be speaking on Holistic Database Security and Secure Coding.
I will be in Moscow Russia, Belgrade Serbia, and Borovets Bulgaria from June 1 – 10. I’ll be quite busy, Speaking in Borovets Bulgaria. When the agenda is finalized, I’ll fill y’all in on my talks.
I’m not even sure how to approach talking about this. One customer with a complex application that is both financial and regulatory was given a backdoor into the system to manually make changes to data without audit or validation. To keep the internal auditors from learning of this, the backdoor was put on the test application server, the user changes one parameter and the backdoor points to production. The purpose of this was, if there was an issue with processing a transaction, the user could just go into this backdoor, manually change the data and continue processing.
I learned about it when the business unit was complaining that the application server was down. Imagine my surprise, after checking the production application server several times to find there was no issue (with production). We were doing work on the test application server; causing the users to have issues getting into their backdoor.
We wound up having a discussion with the business unit that uses this application and backdoor. When I brought it up, on why this falls into “worst practices,” the users got stressed (stressed is an understatement) about losing their backdoor.I realize they have a business need to make changes to the data; so we came up with a compromise. 1) access to the backdoor will be tightly controlled to two people. 2) every time the customer is forced to use the backdoor, they need to open a ticket with a complete description of why they used the backdoor and what data was changed. 3) we are documenting the customer use cases for the backdoor, and developing an application for the user to make changes that are required,addressing audit, business rules, and data quality. 4) Transactions that have been closed will not be allowed to be changed. 5) The backdoor will be going away in the near future and be replaced with an application that meets the business units needs. See #3. This issue has become a top priority, the existence of a backdoor gives me the hebegeebees.
Why is this a bad idea?
- Removing audit controls. We are relying on the integrity of the user. We would like to think that only honest employees are hired, however; that is not always the case. This customer has several documented incidents where a user defrauded the customer of several hundreds of thousands of dollars. (gee, wonder why)
- Audit controls not only protect the customer, the controls are also in place to protect the user. When a transaction is posted and there are questions about it; with a good audit trail the user can confidently say “this is what was done, and this is why I did it.” The auditor will have data to backup what the user stated and it stops there. However; if the audit trail is in question, then the auditor is going to keep digging; generally making everyone’s life stressful.
- We have a issue with data quality. Business rules are used to validate the data that is entered. There are several instances in this database where status codes don’t match known codes. There are also several instances in this database where data that should be not null have null values, and there are quite a few transactions that are no longer in balance. To overcome the not null issue, the columns were defined as null. The financial transactions that are out of balance, well because the audit trail is in question, it’s going to require a forensic auditor to sort that out. These transactions go back several years. Even though, there is a financial close, a user can go back and change data on transactions that have been closed out.
Guys, it’s a really bad idea to give users a backdoor.
“I will judge you by how you treat the people who serve you.”
I’ve spent a lot of time on social media helping some friends with their travel issues. Mostly I give people little hacks that I learned the hard way. These hacks and rules can make a trip more pleasant and if you break some of these rules, then things can get downright nasty.
Over the next few weeks, we’ll talk about, how you dress, your attitude, luggage, your flight attendants, comfort, TSA, customs and immigration, and dealing with jetlag.
We can all agree that over the past few years, airline travel has become less than pleasant. The seats are smaller, the flight attendants and customer service people don’t seem to really care, and dealing with TSA can be challenging. What’s more, after a ten hour flight, you have a multi-hour layover before your connection to your ultimate destination. Yea’ is it any wonder nobody has anything nice to say about the airlines these days.
How you dress and your attitude is as important as remembering your passport.
Economy class has been overbooked again, some are going to get bumped, some are going to take their seat, and some are going to get upgraded. At the end of the day, we will have some people who are not happy and people who think they are lucky. Luck has some to do with the outcome, however you can improve the odds. Or worse yet, your flight has been canceled, there is a fight out tonight that because of the cancellation is fully booked, and there is a flight tomorrow. Are you going to get out tonight or are you going to be spending the night?
Before I get into that all important catching your flight, getting bumped, or getting upgraded story. Let me tell about a few things that have gone wrong with my wardrobe while traveling. About four years ago, I was traveling to Estonia. I had just lost about forty five pounds and had not replaced my wardrobe because I was still losing weight. When it was time for me to get into the body scanner, I had taken my belt off and was holding up my pants with one hand. I stepped into the body scanner, raised my hands and my pants wound up around my knees. Everyone had a good laugh, well maybe not everyone, I felt a bit exposed.
About a year later, I was heading to Helsinki from Washington DC, I had an eight hour red eye to London, a four hour layover, followed by a three hour flight to Helsinki. By this time, I had mostly replaced my wardrobe, and was wearing a new pair of Levis 501 blue jeans. These were the worst pants to wear, they were tight, but they looked good, and the bunched at the crotch. I spent the next seventeen hours tugging at my jeans, trying to get comfortable and relieve the pain.
Now my travel uniform is a Tommy Copper compression shirt (because I have problems with my left shoulder), a comfortable pair of linen pants, either a silk or cashmere sport coat, and comfortable shoes that I can easily slip on or off. This is not high fashion, but it presents nice, I don’t get exposed, and I’m not tugging at my cloths for hours on end. That’s a win win in my book.
Okay, back to our getting bumped situation. A few year ago, I don’t remember what country I was in, I did not speak the language, and I just had a less than productive conference. I arrived at the airport two hours early, got through passport control and made my way over to the boarding area. There were two very long lines at the gate, noone was sitting down waiting to board.
A stone faced lady behind the counter was listening to people explain their situation, she would shake her head, say a few words a dejected customer would walk away muttering. I did not speak the language, but I could tell my flight had been canceled, my blood pressure was moving into the red, I was tired, and needed to get home. After what seems like hours watching people walk away either dejected or angry. (More than a few people wound up raise their voice to the stone faced lady. ) Finally the gentleman in front of me was chatting with her, I would see our stone faced lady was not moved by his predicament, she said something, shook her head and he walked away. It’s my turn, I really hoped she spoke english, I mustered by biggest smile, let my southern accent kick in, and as I was handing her my passport, I mentioned how much I enjoyed one of the local dishes and how much I appreciated the people in her city being so friendly and hospitable. This seemed to crack the stone face, she smiled and told me my flight had been canceled, but there was another flight later that evening it’s full and there is two flights tomorrow. She would see what she could do to get me home. As she was typing away, we made small talk about the food, what sites I got to see, why was I in town, you know, small talk. In all she was very pleasant, she even chucked at my southern accent. She handed me a boarding pass, I had been upgraded me to first class on tonight’s flight. I thanked her in her language (I mispronounced the word) our now friendly customer service representative thanked me for my feeble attempt at trying to speak some of the language. I then made my way to the business class lounge to have a glass of champagne and wait for my flight home.
What was the hack I used. First let’s consider the passengers before me who walked away dejected. Everyone of them, was dressed for comfortable travel, basically looking like they did not care how they looked. Yea’ I’m seeing a lot of people like that these days. Secondly, everyone approached her as it was a confrontation. Okay, her expression and body language told everyone that she was ready for a confrontation, that did not help.
I was in my “standard travel uniform.” Not dressed for a business meeting, but dressed like I actually cared about my appearance. Secondly, I recognized she had the power to either get me home or let me spend another night there. I smiled, was friendly, and said nice things about “her” city. On a side note, most people I have met around the world are very proud of their country and city. Recognizing and respecting this one thing will carry you a long way. Then after she helped me, I thanked her in her language. Really, it’s not a hack, I set myself apart from my fellow passengers. Someone once told me, if you want to be part of the one percent, you have to do what the other ninety nine percent don’t do. To set yourself apart you can start by looking like you actually care about your appearance and having good manners.
Now, picture yourself as the customer service representative, you have very few options to help the line in front of you. You have several seats on tomorrow’s flight and one maybe two first class seats for tonight’s flight. The person in front of you has an attitude, looks like they don’t care about their appearance, and is blaming you for things going wrong. Then you have someone who comes up, is friendly, cares about his appearance, and understands, you have no control over a flight being canceled. Who gets the flight tomorrow and who gets a first class upgrade on tonight’s flight?
I’ve been working on putting together some performance test for my secure coding talk coming up at Hotsos and encountered something I can not quite explain. This test case does a bulk select into a type and returns the type to the calling program. When I execute the code through the API, I get 1,100+- PGA Memory Operation Events. When I don’t use the API, and just use straight pl/sql I get 600+- PGA Memory Operation Events.
First we need to build two schemas and one test user. I’m keeping these pretty simple. HR_CODE is to hold business logic. HR_API is to hold the API for the HR schema. The user executes the code in the business logic schema. Here is the basic architecture. NOTE: this is not the full Secure Architecture implantation in this test case. Code Based Access Control and accessible by has not been implemented in this test case.
create user usr1 identified by x; create user hr_code identified by x; create user hr_api identified by x; -- grant create session to usr1; grant alter session to usr1; -- create the required roles. create role hr_big_table_all; create role hr_big_table_sel; -- create a test table in the hr schema create sequence hr.big_table_seq; create table hr.big_table (id number not null, c number not null, d number not null); -- setup permissions. grant select on hr.big_table to hr_big_table_sel, hr_api; grant select, update, insert, delete on hr.big_table to hr_big_table_all; grant hr_big_table_all to grant hr_big_table_sel to usr1; -- populate the table with 1M rows insert into hr.big_table ( select hr.big_table_seq.nextval, ceil(sys.dbms_random.value(0,10)), sys.dbms_random.value(-1,5000) from dual connect by rownum <= 1000000); -- commit; -- add in the constraints and indexes alter table hr.big_table add constraint big_table_pk primary key (id); -- create index hr.big_table_idx1 on hr.big_table(c); -- analyze the schema exec sys.dbms_stats.gather_schema_stats(ownname => 'HR'); -- create the decls package. this is used so all schemas can -- reference common definitions. create or replace package hr_decls.big_tab_decl as cursor big_tab_cur is select id, c, d from hr.big_table; subtype st_big_tab is big_tab_cur%rowtype; type tt_big_tab is table of st_big_tab index by pls_integer; end; / -- grant execute on the decls package to expose definations grant execute on hr_decls.big_tab_decl to hr_api, hr_code, usr1; -- -- create the api packages create or replace package hr_api.big_tab_api authid current_user as function sel_hr_big_tab_range(p_low number, p_high number) return hr_decls.big_tab_decl.tt_big_tab; end big_tab_api; / -- grant execute on the api to the code schema grant execute on hr_api.big_tab_api to hr_code; --create the package body -- create or replace package body hr_api.big_tab_api as function sel_hr_big_tab_range(p_low number, p_high number) return hr_decls.big_tab_decl.tt_big_tab is ltt_big_tab hr_decls.big_tab_decl.tt_big_tab; begin select id, c, d bulk collect into ltt_big_tab from hr.big_table where d between p_low and p_high; return ltt_big_tab; exception when no_data_found then -- <fixme> insert error handler raise_application_error(20000,'sel_hr_big_tab_range'); end sel_hr_big_tab_range; end big_tab_api; / -- create the code package. this holds the business logic -- to create a shell around the API. -- you will notice, no business logic has been added in -- at this point. create or replace package hr_code.sel_perf_test authid definer is function sel_hr_big_tab_range(p_low number, p_high number) return hr_decls.big_tab_decl.tt_big_tab; end sel_perf_test; / create or replace package body hr_code.sel_perf_test is function sel_hr_big_tab_range(p_low number, p_high number) return hr_decls.big_tab_decl.tt_big_tab is ltt_big_tab hr_decls.big_tab_decl.tt_big_tab; begin ltt_big_tab := hr_api.big_tab_api.sel_hr_big_tab_range(p_low => p_low, p_high => p_high); return ltt_big_tab; end sel_hr_big_tab_range; end sel_perf_test; / -- grant execute on the business logic code to the user -- The user only has one way to get to the data, that is -- through the business logic. grant execute on hr_code.sel_perf_test to usr1; -- test the user executing the business logic code. conn usr1/x@demo ALTER session SET timed_statistics=TRUE; ALTER SESSION SET TRACEFILE_IDENTIFIER = "perf_test_api"; alter session set sql_trace=TRUE; alter session set events='10046 trace name context forever, level 12'; -- test it. declare ltt_big_tab hr_decls.big_tab_decl.tt_big_tab; begin -- ltt_big_tab := hr_code.sel_perf_test.sel_hr_big_tab_pk(p_id => 100); -- ltt_big_tab := hr_code.sel_perf_test.sel_hr_big_tab_value(p_value => 4); -- for i in 0 .. 50 -- LOOP ltt_big_tab := hr_code.sel_perf_test.sel_hr_big_tab_range(p_low => 50, p_high => 900); -- END LOOP; end; / -- to run the next test, the user will need select on hr.big_table. conn rlockard@demo grant hr_big_table_sel to usr1; conn usr1/x@demo ALTER session SET timed_statistics=TRUE; ALTER SESSION SET TRACEFILE_IDENTIFIER = "perf_test_noapi"; alter session set sql_trace=TRUE; alter session set events='10046 trace name context forever, level 12'; declare ltt_big_tab hr_decls.big_tab_decl.tt_big_tab; begin -- for i in 0 .. 50 -- LOOP select id, c, d bulk collect into ltt_big_tab from hr.big_table where d between 50 and 900; -- END LOOP; end; /
I noticed a error in my code for setting PLSCOPE_SETTINGS and PLSQL_WARNINGS. QuicTip Logon.sql What I did was get the instance name out of v$instance to figure out if I was connecting to a production environment or one of the lower environments. The problem with this is, not everyone is going to have permissions to select on sys.v_$instance. The better way to do this is to use sys_context to get the instance name. This way, you won’t have to chase down additional privileges from your DBA.
<code> select sys_context('userenv','instance_name') into sInst from dual; </code> Here is the corrected code for my logon.sql <code> DECLARE sInst varchar2(1); BEGIN -- rlockard: 2018/02/23 commented out getting instance name from v$instance. -- used the more apporiate sys_context('userenv','instance_name') --select upper(SUBSTR(instance_name, 1,1)) --INTO sInst --FROM SYS.V_$INSTANCE; select sys_context('userenv','instance_name') into sInst from dual; -- test to see if this is a production instance -- all production instances start with P so ... -- if it's not a production instance set up -- session properties approiate for dev / test / sandbox. IF sInst != 'P' THEN execute immediate 'ALTER SESSION SET PLSCOPE_SETTINGS=' || '''IDENTIFIERS:ALL'''; execute immediate 'ALTER SESSION SET PLSQL_WARNINGS=' || '''ENABLE:ALL'''; END IF; END; / </code>