Wednesday, March 10, 2021

Alter a trigger by a procedure ORA-01031: insufficient privileges

Tried to disable and enable a trigger (which is own by another user)  by a procedure but we are getting error  "ORA-01031: insufficient privileges" event though calling user(current user) have all the neccesary proviliges like "alter any trigger"

Solution:

I hav added  "AUTHID CURRENT_USER" clause in the procedure then it started working as below - 

Just as example-

CREATE OR REPLACE PROCEDURE test
   AUTHID CURRENT_USER  ----set Invoker Rights not
definer rights
is

begin

EXECUTE IMMEDIATE 'alter trigger tri_name disable'; 
.......
........
EXECUTE IMMEDIATE 'alter trigger tri_name enable'; 

 end ; 


  For Details

Alter Trigger fails with ORA-01031: insufficient privileges in Oracle

A small thing but if you don't know it might make you confused and need to spend lots of times.

 Today, we are trying to disable a trigger which is own by a differen user but we are keep geting error "ORA-01031: insufficient privileges" even though we are trying with SYS user.  

The Trigger was a "after logon on database"  trigger and database version 19c

Cause:   

Oracle enforced administer database trigger for both Current_User and Trigger Owner (provably from oracle database 12.2 onwards) .

 

Solution:  

Need to provide a direct grant of "administer database trigger" for the trigger owner and may be also for current user.     

In our case below example worked. 

example- 

Trigger owner:  SCOTT     

Then scott needs have below privilage directly (even from a ROLE which has this privilege won't work, need to provide directly)

SQL> grant administer database trigger to SCOTT ;  


 

 

 

 

 

Monday, March 8, 2021

Is APEX installed by default in all latest Oracle Databases.

Just for clearification- I was under impression that, Apex is installed in all latest oracle versions but that's not true.

 Till oracle database 12.1.0.2, Apex schemas were installed by default. But from version 12.2.0.1 onwards they are not installed by default (that's a good idea) but it come with following supplied versions-

 
With Database 12.2.0.1 is Application Express 5.0.4. APEX is not installed by default.
With Database 18.0.0.0 is Application Express 5.1.3. APEX is not installed by default.
With Database 19.0.0.0 is Application Express 18.2. APEX is not installed by default.
With Database 21c is Application Express 20.1. APEX is not installed by default.

Friday, January 15, 2021

Oracle Apex Installation using ORDS and running in standalone mode

Step by step full installation.  I used oracle Apex version 20.2 using ORDS installation and configuration. database version Oracle 12c. added three link or steps to run into standalone mode, deploying into weblogic server or apache tomcat server.  

Is apex installed by default in all oracle databases!

1)    Downloading the latest version oracle apex software (I used English only)  and then unzip

https://www.oracle.com/tools/downloads/apex-downloads.html

2)  Change your working directory to apex (unzipped APEX software) AND then connect to the main Oracle database with sys user from SQLPLUS as below-

(I have multiple version of database in my machine so I explicitly invoke 12c env.) 

 

3)   Create a tablespace (apex_tbs) for only APEX metadata –

 

    SQL> CREATE TABLESPACE apex_tbs DATAFILE 'C:\app12c\oracle\oradata\atlantadb\apex_tbs_01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M;

 

4)  Install apex using below command –

Syntax:

@apexins.sql tablespace_apex tablespace_files tablespace_temp images

 

Description:

·        tablespace_apex is the name of the tablespace for the Oracle Application Express application user.   In our case apex_tbs

·        tablespace_files is the name of the tablespace for the Oracle Application Express files user. In our case apex_tbs

·        tablespace_temp is the name of the temporary tablespace or tablespace group. In our case temp

·        images is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/.

 

SQL> @apexins.sql apex_tbs apex_tbs temp /i/

 

After successfully complete, you see output like this-

 


When Application Express installs, it creates the following database accounts:

  • APEX_200200 - This account owns the Application Express schema and metadata. (This name might differ based on Apex version)
  • FLOWS_FILES - This account owns the Application Express uploaded files.
  • APEX_PUBLIC_USER - This minimally privileged account is used for Application Express configuration with Oracle REST Data Services or Oracle HTTP Server and mod_plsql.

If you configured RESTful Web services, then these additional accounts will be created:

  • APEX_INSTANCE_ADMIN_USER - A minimally privileged account used for the REST
  • APEX_REST_PUBLIC_USER - The account used when invoking RESTful Services definitions stored in Oracle Application Express.
  • APEX_LISTENER - The account used to query RESTful Services definitions stored in Oracle Application Express.

If you are upgrading from a previous release, then FLOWS_FILES already exists and APEX_PUBLIC_USER is created if it does not already exist.

 

5) For Changing Apex Instance Administrator password using (apxchpwd.sql )

What Is an Instance Administrator?
Instance administrators are superusers that are responsible for managing an entire Oracle Application Express instance, including managing workspace provisioning, configuring features and instance settings, and managing security.

·  Change your working directory to the apex directory where you unzipped the installation software.

·  Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role     

        

SQL>  @apxchpwd.sql

Output-

 

Note: You must run the apxchpwd.sql script in the following scenarios:

·        New Oracle Application Express installations -

·        Converting of a runtime environment to a development environment

·        Changing Your Instance Administrator Password

·        Unlocking Your Instance Administrator Account

 

6)   Unlock and change passwork of user – APEX_PUBLIC_USER 

 

SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY apex123$ account unlock ;

 

   Note to remember: Password might expire in 180 days, so need a separate profile with never expire password then assign to this user.

 

----------------------------Apex software Installation completed--------------------------

Now need to Install ORDS to run the application and (this is must from APEX 20 onwards)


7)    Configuring Oracle RESTful Services (ORDS)

In a new installation of Oracle Application Express, you must run the configuration script apex_rest_config.sql to configure RESTful Services.

SQL>@apex_rest_config.sql

 


When Prompted, enter a password for the APEX_LISTENER and APEX_REST_PUBLIC_USER accounts.

When configuring RESTful Services in Oracle Application Express, it creates two new database accounts.

  • APEX_LISTENER - The account used to query RESTful Services definitions stored in Oracle Application Express.
  • APEX_REST_PUBLIC_USER - The account used when calling RESTful Services definitions stored in Oracle Application Express.  

8)         Unlock all below users if they are locked and change password if you forgot

 

ALTER USER APEX_LISTENER IDENTIFIED BY Apex123$ ACCOUNT UNLOCK;
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY Apex123$ ACCOUNT UNLOCK;
ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY Apex123$ ACCOUNT UNLOCK;
 

 

9)     Download and install ORDS Oracle rest data services

https://www.oracle.com/database/technologies/appdev/rest-data-services-downloads.html

I downloaded - “Oracle REST Data Services”

a)     After download unzip the file –  something like - C:\apex_20.2_en\ords

b)     Make a conf folder something like -  C:\apex_20.2_en\ords\conf

c)     Setup  ords param file :  C:\apex_20.2_en\ords\params\ords_params.properties with below properties -  it should be based on you environments

db.hostname=my-PC

db.port=1521

#db.servicename=

db.sid=atlantadb

db.username=APEX_PUBLIC_USER

db.password=Apex123$

migrate.apex.rest=false

plsql.gateway.add=true

rest.services.apex.add=true

rest.services.ords.add=true

schema.tablespace.default=apex_tbs

schema.tablespace.temp=TEMP

standalone.mode=true

# Next 3 lines for standalone mode only.

standalone.use.https=true

standalone.http.port=8080

standalone.static.images=C:\apex_20.2_en\apex\images

user.apex.listener.password=Apex123$

user.apex.restpublic.password=Apex123$

user.public.password=Apex123$

user.tablespace.default=apex_tbs

user.tablespace.temp=TEMP

#sys.user=SYS

#sys.password=sys

# Enable REST Enabled SQL.

restEnabledSql.active=true

# Enable SQL Developer Web. Available from 19.4 onward. Requires REST Enabled SQL.

feature.sdw=true

# Enable database APIs. Available from 19.1 onward.

database.api.enabled=true

 

d)     Specifying configuration file :

C:\apex_20.2_en\ords>java -jar ords.war configdir C:\apex_20.2_en\ords\conf

e)     Installing ORDS :

C:\apex_20.2_en\ords>java -jar ords.war

Output-

 

10) After a successful installation you can Validate ORDS services

C:\apex_20.2_en\ords> java -jar ords.war validate

11)Running ORDS in Standalone mode ( it should be running for using apex like http server)  to stop the service Please use CTRL+C

 

ords>java -jar ords.war standalone

 

This value will show when it is running in standalone mode


C:\apex_20.2_en\ords>java -jar ords.war standalone

Enter the APEX static resources location [C:apex_20.2_enapeximages]:C:\apex_20.2_en\apex\images

Enter 1 if using HTTP or 2 if using HTTPS [1]:2

Enter the HTTPS port [8443]:

Enter the SSL hostname:my-PC

Enter 1 to use the self-signed certificate or 2 if you will provide the SSL certificate [1]:1

2021-01-14T02:44:55.057Z INFO        HTTPS and HTTPS/2 listening on host: localhost port: 8443

2021-01-14T02:44:55.101Z INFO        Disabling document root because the specified folder does not exist: C:\apex_20.2_en\ords\conf\ords\standalone\doc_root

2021-01-14T02:44:56.358Z INFO        Configuration properties for: |apex||

database.api.enabled=true

db.connectionType=basic

db.hostname=my-PC

db.port=1521

db.sid=atlantadb

feature.sdw=true

...............

..............

2021-01-14T02:44:58.905Z INFO        Oracle REST Data Services initialized

Oracle REST Data Services version : 20.3.0.r3011819

Oracle REST Data Services server info: jetty/9.4.30.v20200611

 

 

12)                   Now it is time to login and test

Admin login

https://my-pc:8443/ords/apex_admin

 

apex instance admin user : Admin

password for me:  Halim123$

 

Developer Login with workspace name:

https://my-pc:8443/ords

 

workspace name: Halim

 

https://my-pc:8443/ords/f?p=4550:10:14418526633984:::::

 

https://my-pc:8443/ords/f?p=4550:1:2335960019865:::::

 

Success!!

 

 

Note: I used auto SSL (for HTTPS) when running ORDS. it uses jetty and port 8443  (Make sure this port is open in your network or you can use different port)

Auto SSL (HTTPS)

ORDS will automatically create a self-signed certificate for use with SSL if you don't specify a valid certificate and key.

Edit the "/u01/ords/conf/ords/standalone/standalone.properties" file, setting the following parameters. Adjust the port as desired.

Or It default it will be added when you provided in the prompt

jetty.secure.port=8443

 

13)  ---Deploying to Weblogic server 

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.2/aelig/installing-REST-data-services.html#GUID-63AD5B59-CD6A-47A6-BBB4-0D614090C51F

 

14)  ----Deployment to apache Tomcat

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.2/aelig/installing-REST-data-services.html#GUID-3F2AE730-69D0-4A64-A13A-76745B7467CD

 

References:  

----Downloading and installing Oracle APEX

https://docs.oracle.com/en/database/oracle/application-express/20.2/htmig/downloading-installing-apex.html#GUID-7E432C6D-CECC-4977-B183-3C654380F7BF

https://oracle-base.com/articles/misc/oracle-application-express-apex-installation

---Configuring and Installing Oracle rest data services (ORDS)

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.2/aelig/installing-REST-data-services.html#GUID-34C2C8C8-4AF1-402A-A956-E0A42FCED85E

       https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-installation-on-tomcat#ords-validate

---Running on Standalone mode

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.2/aelig/installing-REST-data-services.html#GUID-3DB75A67-3E66-48EF-87AC-6948DE796588

      https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-standalone-mode

 

Issues and solution:

 Issue #1:   Image directory not setup correctly in http mode.

"There is a problem with your environment because the Application Express files have not been loaded. Please verify that you have copied the images directory to your application server as instructed in the Installation Guide. In addition, please verify that your image prefix path is correct. Your current path is /i/ (it should contain both starting and ending forward slashes, such as the default /i/). Use the SQL script reset_image_prefix.sql if you need to change it."


 Solution:  

1. Go to the apex\utilities   directory. 

cmd>  cd   C:\apex_20.2_en\apex\utilities

2. Login to database with sys user 

I have multiple version of oracle that's why I run the specific version's exe. in your case this might be just login with sqlplus /nolog

        C:\apex_20.2_en\apex\utilities>C:\app12c\oracle\product\12.1.0\dbhome_1\BIN\sqlplus.exe

SQL*Plus: Release 12.1.0.2.0 Production on Fri Apr 2 09:00:48 2021

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>


3. Then run this script reset_image_prefix.sql  as mentioned above.

  SQL> @reset_image_prefix.sql

Just enter.


4.   Correct the standalon property file  - standalone.properties

in this location-  C:\apex_20.2_en\ords\conf\ords\standalone  

                content of  standalone.properties

jetty.port=8080     # after installation I changed the port 8080 for http mode to run the application
standalone.context.path=/ords
standalone.doc.root=C\:\\apex_20.2_en\\ords\\conf\\ords\\standalone\\doc_root
standalone.scheme.do.not.prompt=true
standalone.static.context.path=/i
standalone.static.path=C\:\\apex_20.2_en\\apex\\images

 Make sure blue highlighted line is correct.