Category: User Privileges and Roles

ORA-29283: invalid file operation ORA-06512: at “SYS.UTL_FILE”, line 536 ORA-29283: invalid file operation

Connected to Oracle database through service name, then get  errors by running the below sample code.  There is no problem to run the sample code while connecting to database through IPC.

$sqlplus testuser/****@DBTEST

SQL>DECLARE
    l_output utl_file.file_type;
BEGIN
    l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' );
    utl_file.fclose( l_output ); 
END;
/

*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 4
$sqlplus  / as sysdba
SQL> DECLARE
     l_output utl_file.file_type;
BEGIN
     l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' );
     utl_file.fclose( l_output );
END;

 /

PL/SQL procedure successfully completed.

CAUSES

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

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.

Subscribe