ORA-01031: insufficient privileges by EXECUTE IMMEDIATE

“ORA-01031: insufficient privileges” was received from a PL/SQL procedure  “EXECUTE IMMEDIATE” state.

1)Create a simple PL/SQL procedure.

SQL> create or replace procedure proc_test
is
begin
          EXECUTE IMMEDIATE 'create table tbl_test ( id number)';
end;

2) logon as user ,and then execute above procedure.

SQL> exec proc_test;
BEGIN proc_test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST_USER.PROC_TEST", line 7
ORA-06512: at line 1

3) grant ‘create table’ privilege to user directly.

SQL> show user
USER is "SYS"
SQL> grant create table to test_user;

Grant succeeded.

4)logon as user ,and then execute above procedure.

SQL> show user
USER is "TEST_USER"

SQL> exec proc_test;

PL/SQL procedure successfully completed.

SQL> desc tbl_test;
Name   Null?   Type
----- -------- ----------------------------
ID             NUMBER

SQL> drop table tbl_test;

REASON: In stored procedure/function, any privileges granted by a role will NOT be effective. The privileges should be directly granted to the user.

Truncate a Table of Other User

In order to truncate a table of other user, the DROP ANY TABLE system privilege is required. Without granting this powerful privilege, instead, a procedure is created and granted to the user who can truncate other user’s tables.

In this example, user B is able to truncate table TEST of user A without needing “DROP ANY TABLE” system privilege.

1) In schema A, create a procedure called “truncate_tab”:

SQL>show user

USERA

SQL>create or replace procedure truncate_tab (tab_name IN varchar2) 
as 
begin 
   execute immediate 'truncate table '||tab_name ; 
end; 
/

2) Grant execute on procedure truncate_tab to user B:

SQL>show user

USERA

SQL>grant execute on a.truncate_tab to B;

Grant succeeded.

3) Truncate table A.TEST by user A:

SQL>show user

USERB

SQL> exec a.truncate_tab('TEST');

PL/SQL procedure successfully completed.

How do I change my Oracle user password ?

c:\>
C:\>sqlplus testuser/Password@//ractest-scan.ractest.local:1521/service

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 31 11:44:13 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options


SQL> password

Changing password for TESTUSER
Old password:
New password:
Retype new password:
Password changed

SQL>

ORA-15260: permission denied on ASM disk group

PROBLEM

Try to set ASM diskgroup attribute, then get the following errors:

$ sqlplus / as sysdba
...
..
.
SQL> alter diskgroup OCR_VOTE SET ATTRIBUTE 'compatible.asm' ='11.2.0.2';
alter diskgroup OCR_VOTE SET ATTRIBUTE 'compatible.asm' ='11.2.0.2'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15260: permission denied on ASM disk group

Subscribe to get access

Read more of this content when you subscribe today.

ORA-28002 : the password will expire within 3 days

User password can be restored back to its original one by using password hash value.

The password is unknown, and it is encrypted in application configurations. User doesn’t want to change it at the moment.

$ oerr ora 28002
28002, 00000, "the password will expire within %s days"
// *Cause: The user's account is about to expire and the password
// needs to be changed
// *Action: change the password or contact the DBA
//

SQL> select USERNAME,PASSWORD,ACCOUNT_STATUS,EXPIRY_DATE 
       from dba_users 
      where username='TESTUSER';

USERNAME         PASSWORD             ACCOUNT_STATUS    EXPIRY_DATE
---------------- -------------------- ----------------  ---------
TESTUSER                              EXPIRED(GRACE)    18-MAR-17

Get hash password value from USER$.

SQL> select password from user$ where name='TESTUSER';

PASSWORD
--------------------
512795B1F6AC27B8

Reset the password with the original one:

SQL> alter user testuser identified by values '512795B1F6AC27B8';

User altered.
SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE 
       from dba_users 
      where USERNAME='TESTUSER';

USERNAME       ACCOUNT_STATUS    EXPIRY_DATE
-------------- ---------------   -----------
TESTUSER       OPEN              14-MAY-17