A schema is considered all of the objects owned by a single user within the Oracle database. When a user has a privilege revoked in the database, but owns a particular object which requires that privilege, errors will result. This article will recover an error which will result from a user having a privilege revoked which is require by one of the objects he owns.

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 17 11:00:09 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Create the user LJCATT and grant him the DBA role and create procedure privileges.

SQL> create user ljcatt identified by password10;

User created.

SQL> grant dba to ljcatt;

Grant succeeded.

SQL> grant execute on utl_file to ljcatt;

Grant succeeded.

4. Connect as the user ljcatt and create a procedure named test_file.

SQL> connect ljcatt
Enter password:
Connected.

SQL> create or replace procedure test_file
is
2 3 v_record varchar2(50) := ‘Testing file creation’;
4 v_file varchar2(30) := ‘testfile’;
5 v_dir varchar2(100) := ‘/home/lcatt’;
6 v_write utl_file.file_type;
7 begin
8
9 v_write := utl_file.fopen(v_dir, v_file, ‘w’, 4000);

10 11 –utl_file.fopen(v_dir, v_file, ‘w’, 100);
12
13 utl_file.put_line(v_write, v_record);

14 15 utl_file.fclose(v_write);
16
17 end test_file;
/ 18

Procedure created.

5. Reconnect to the database with SYSDBA privileges and revoke the DBA from ljcatt and grant the create session privilege. Then attempt to reconnect as the user LJCATT.

SQL> connect / as sysdba
Connected.
SQL> revoke dba from ljcatt;

Revoke succeeded.

SQL> grant create session to ljcatt;

Grant succeeded.

SQL> connect ljcatt
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00900: invalid SQL statement
ORA-06512: at line 8

Warning: You are no longer connected to ORACLE.

6. NOTE: The error is received due to this user owning objects which he does not have privileges to create or access. This is shown below by reconnecting as SYSDBA and displaying objects owned by the user LJCATT.

SQL> connect / as sysdba
Connected.

SQL> select owner, object_name from dba_objects where object_name like ‘TEST_F%’
and owner=’LJCATT’

OWNER OBJECT_NAME
————————- ————————–
LJCATT TEST_FILE

7. As SYSDBA, re-grant the DBA role to LJCATT and attempt to reconnect.

SQL> grant dba to ljcatt;

Grant succeeded.

SQL> connect ljcatt
Enter password:
Connected.
SQL>

Now that the user LJCATT has the specific privileges required by the objects he owns, he is able to connect without error.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com