ORA-01031: insufficient privileges due to missing GRANT with ADMIN option

ORA-01031: insufficient privileges due to missing GRANT with ADMIN option

A common architectural requirement in many applications is to create a schema user for all objects that are created and a separate user to connect to the database to access these objects. This user will only have the necessary privileges to access the required objects from the main schema owner.
In such situations, if there is a need to create a table, index, sequence etc at run time you can execute a procedure which will do the needful.

Example
On the database side,
App user is scott, object owner is HR.
Users will connect to database from the application as user scott.
The requirement is to create a table in HR schema by executing a procedure owned by HR(object owner) as SCOTT(app user)

create or replace PROCEDURE HR.PR_GRANT_TEST
AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE HR.T1(C1    VARCHAR2(10))';
END;

GRANT EXECUTE ON HR.PR_GRANT_TEST TO SCOTT;

On executing this procedure you get the below error

ORA-01031: insufficient privileges
ORA-06512: at "HR.PR_GRANT_TEST", line 7
ORA-06512: at line 2
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

Solution - Giving the below system privilege to HR user will resolve the issue.

ORA-01031: insufficient privileges - When creating a Foreing Key on another schema objectGRANT CREATE TABLE TO HR WITH ADMIN OPTION;

When creating a FK constraint on a table belonging to another schema you get ORA-01031: insufficient privileges error.

Example :
You have created a EMPLOYEE_HISTORY table in SCOTT schema and you want to create a FK on the EMPLOYEE_ID column referencing EMPLOYEE_ID of EMPLOYEES table in HR schema.

ALTER TABLE SCOTT.EMPLOYEES_HISTORY ADD FOREIGN KEY(EMPLOYEE_ID) REFERENCES HR.EMPLOYEES(EMPLOYEE_ID);

Error report :
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

Solution :  
GRANT REFERENCES ON HR.EMPLOYEES TO SCOTT;

Once this grant is given from HR schema or as a DBA user you should be able to create the Foreign Key constraint.

ORA-04062: timestamp of function has been changed -- How to avoid

When accessing a function or procedure across a dblink you may sometimes get the below error ORA-04062: timestamp of function "HR.FX_SQUARE" has been changed 

This is because the schema in which the function be called, the function or may have got compiled again due to some change in the logic of the function, due to which the timestamp of the function gets changed. 

In HR schema in XEPDB1 database I have the below function

create or replace FUNCTION FX_SQUARE (num_in   NUMBER)
RETURN NUMBER
AS
num_out  NUMBER;
BEGIN
  num_out := num_in * num_in;  
  
  RETURN num_out;

END;

I have created a dblink SYSTEM_TO_HR connecting from SYSTEM to HR schema. From the SYSTEM schema I am accessing the FX_SQUARE function

create or replace PROCEDURE PR_TEST_DBLINK (p_num  NUMBER)
AS
num_sqaure NUMBER;
BEGIN
   num_sqaure := FX_SQUARE@SYSTEM_TO_HR (p_num);
   dbms_output.put_line( 'Sqaure of ' || p_num || ' is ' || num_sqaure);
END;

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = 'PR_TEST_DBLINK';

OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_ID	DATA_OBJECT_ID	OBJECT_TYPE	CREATED	LAST_DDL_TIME
SYSTEM	PR_TEST_DBLINK		77902		PROCEDURE	30-JUL-24 09:06:23	30-JUL-24 09:08:16

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = 'FX_SQUARE';

OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_ID	DATA_OBJECT_ID	OBJECT_TYPE	CREATED	LAST_DDL_TIME
HR	FX_SQUARE		77809		FUNCTION	30-JUL-24 09:01:46	30-JUL-24 09:01:16

The PR_TEST_DBLINK procedure has a later LAST_DDL_TIME "09:08:16" as compared to LAST_DDL_TIME of FX_SQUARE "09:01:16". So PR_TEST_DBLINK gets executed successfully. If the FX_SQUARE is modified and recompiled its last DDL time will change.

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = 'FX_SQUARE';

OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_ID	DATA_OBJECT_ID	OBJECT_TYPE	CREATED	LAST_DDL_TIME
HR	FX_SQUARE		77809		FUNCTION	30-JUL-24 09:01:46	30-JUL-24 09:15:16

If I now execute PR_TEST_DBLINK I will get the below error

ORA-04062: timestamp of function "HR.FX_SQUARE" has been changed
ORA-06512: at "SYSTEM.PR_TEST_DBLINK", line 5
ORA-06512: at line 6
04062. 00000 -  "%s of %s has been changed"
*Cause:    Attempt to execute a stored procedure to serve
           an RPC stub which specifies a timestamp or signature that is
           different from the current timestamp/signature of the procedure.
*Action:   Recompile the caller in order to pick up the new timestamp.

To avoid this error we can add the following line of code the the procedure PR_TEST_DBLINK calling the remote function.

execute immediate 'alter session set remote_dependencies_mode = SIGNATURE';

Now if we change and compile the function FX_SQUARE and it has a later timestamp the same error will not occur. 

To find list of DB Objects in thhe database using DB Link

DECLARE

  TYPE objCurTyp IS REF CURSOR;  -- define weak REF CURSOR type
  cur_dblink   objCurTyp;  
  CURSOR C1
  IS
  SELECT OWNER, DB_LINK FROM DBA_DB_LINKS;
  
  v_sql  VARCHAR2(1000);
  v_object_name  VARCHAR2(100);
  
BEGIN
  FOR rec in C1
  LOOP --{
    v_sql := 'select DISTINCT(name) from dba_source where upper(text) like ''%' || rec.DB_LINK || '%'' AND OWNER = ' || rec.OWNER;
--    dbms_output.put_line('v_sql ' || v_sql);
--     OPEN cur_dblink FOR 'select DISTINCT(name) from dba_source where upper(text) like ''%' || rec.DB_LINK || '%'' AND OWNER = ' || rec.OWNER;
     OPEN cur_dblink FOR 'select DISTINCT(name) from dba_source where upper(text) like ''%' || rec.DB_LINK || '%'' AND OWNER = :b' USING  rec.OWNER;
     LOOP --{
      FETCH cur_dblink INTO v_object_name;
      EXIT WHEN cur_dblink%NOTFOUND;
      dbms_output.put_line('v_owner ' || rec.OWNER || ' db_link ' || rec.DB_LINK || ' v_object_name ' || v_object_name);
      
     END LOOP; --}
     CLOSE cur_dblink;
  END LOOP; --}

END;

Quote for the day
"Don’t Let Yesterday Take Up Too Much Of Today." -Will Rogers