Clever Geek Handbook

Java.sql.SQLException: ORA-06550: line 1, column 13: after granting user permission for EXECUTE package

I spent months developing a JAVA application with a basic Oracle db interface. I am using Netbeans as my IDE and Oracle 12c on a laptop as my database. All PL / SQL was compiled without errors.

After extensive testing (registered as schema owner) I tried to add a user. I created a user, logged in as SYS_DBA using CREATE USER E566299 IDENTIFIED BY tempPswrd

, then granted permission using GRANT CREATE SESSION

and GRANT EXECUTE ON C##FAI_CODE.FAI_ADMIN_PKG TO E566299

and received confirmation Grant succeeded

.

I can log into my application as this new user without error using the following:

private static Connection getDbConn(String user, String password) throws SQLException{
        OracleDataSource ods = null;
        Connection dbConn = null;
        user = "c##" + user;
            ods = new OracleDataSource();
            ods.setURL("jdbc:oracle:thin:@//localhost:1522/orcl.global.ds.XXXXXXXX.com");
            ods.setUser(user);
            ods.setPassword(password);
            dbConn = ods.getConnection();
        return dbConn;
    }

Then it throws an error the first time the package procedure is called:

java.sql.SQLException: ORA-06550: line 1, column 13: PLS-00201: identifier 'FAI_ADMIN_PKG.CHECK_USER_FOLLOWED' must be declared ORA-06550: line 1, column 7: PL / SQL: expression is ignored

at oracle.jdbc.driver.T4CTTIoer.processError (T4CTTIoer.java:450) at oracle.jdbc.driver.T4CTTIoer.processError (T4CTTIoer.java:399) at oracle.jdbc.driall.T4C8process ) at oracle.jdbc.driver.T4CTTIfun.receive (T4CTTIfun.java:655) at oracle.jdbc.driver.T4CTTIfun.doRPC (T4CTTIfun.java:249) at oracle.jdbc.driver.T4CALL8Oall.do 566) at oracle.jdbc.driver.T4CCallableStatement.doOall8 (T4CCallableStatement.java:210) at oracle.jdbc.driver.T4CCallableStatement.doOall8 (T4CCallableStatement.java:53) at T4CCallableStatement.java:53) at T4CCallableStatement.java:53) at T4CCallableStatement.java:53) at oracle.jdbc.driver.Tava:53 : 938) in oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout (OracleStatement.java:1075) in oracle.jdbc.driver.OraclePreparedStatement.executeInternal (OraclePreparedStatement.java:3820) at oracle.jdbc.driver.OraclePreparedStatement.execute (OraclePreparedStatement.java:3923) at oracle.jdbc.driver.OracleCallableStatement.execute (OracleCallableStatement.java.java:56d) .execute (OraclePreparedStatementWrapper.java:1385) at faidb.FAIdb.checkSubscription (FAIdb.java:549) at faidb.faidbUI.run (faidbUI.java:186) at faidb.faidbLogin $ 3.run. at faidbLogin (faidbLog3) java.awt.event.InvocationEvent.dispatch (InvocationEvent.java:311)OraclePreparedStatementWrapper.execute (OraclePreparedStatementWrapper.java:1385) at faidb.FAIdb.checkSubscription (FAIdb.java:549) at faidb.faidbUI.run (faidbUI.java:186) at faidb.faidun.java:186) at faidb.faidbLogin $ at java.awt.event.InvocationEvent.dispatch (InvocationEvent.java:311)OraclePreparedStatementWrapper.execute (OraclePreparedStatementWrapper.java:1385) at faidb.FAIdb.checkSubscription (FAIdb.java:549) at faidb.faidbUI.run (faidbUI.java:186) at faidb.faidun.java:186) at faidb.faidbLogin $ at java.awt.event.InvocationEvent.dispatch (InvocationEvent.java:311)

I'm at a loss, I've tried every possible combination of grant wording, all caps, all lowercase, single quotes, double quotes, prefixed with schema owner name C##FAI_CODE.FAI_ADMIN_PKG

, without FAI_ADMIN_PKG

, nothing will allow this new user to execute the package.

I spent a lot of time on this project and almost panic that no one can use it.

Question:

Why can't my new user run the package after getting permission?

Edit:

I get NullPointerException

on the last line with the call rset.close();

, the result set is never initialized, but it doesn't throwSQLException 

public Vector<String> fillBox() throws SQLException, NullPointerException{
        CallableStatement callStatement = null;
        ResultSet rset = null;
        String fillBox = "{call fai_admin_pkg.get_end_item_pn(?)}";
        Vector<String> boxFill = new Vector<>();
        try{
            callStatement = conn.prepareCall(fillBox); 
            callStatement.registerOutParameter(1, OracleTypes.CURSOR);
            callStatement.execute();
            rset = (ResultSet) callStatement.getObject(1); 
            boxFill = buildRsVector(rset);
        }
        finally{
            callStatement.close();
            rset.close();
        }
        return boxFill;
    }

It is called:

Vector<String> boxFill = new Vector<>();
            try{
                boxFill = uiInst.fillBox();
            }catch(SQLException e){
                JOptionPane.showMessageDialog(frame, e.getMessage());
            }catch(NullPointerException e){
                JOptionPane.showMessageDialog(frame, e.getMessage());
                e.printStackTrace();
            }

Here are my permissions as a new user ... that don't include batch execution? I do not understand no permission after granted

+3


source to share


2 answers


The problem has been resolved. The absence of a global set of synonyms, or current_schema

for a new user session, the installed package owner, leads to any calls pack owner procedures requiring the owner: prefix <package owner>.<package>.<procedure>

. The calls that I made under the new user were only with <package>.<procedure>

, which resulted in the output SQLException

because this package does not exist for the compiler.

Also, I will investigate both global synonyms and user sessions to avoid having to use this quick fix.



Thanks for the help!

+2


source


creating a session will just let you log into the database. you may need other permissions to do what you want to do.

you can refer to this link for help



https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljgrant.html

0


source






More articles:


All Articles