1

I am using oewizard from swingbench to create an order-entry like schema on oracle 11g. When I run the wizard I get the following errors:

ORA-01031: Cannot grant execute on dbms_lock to soe: Insufficient privilege
ORA-01031: Cannot GRANT SELECT ON SYS.V_$PARAMETER to SOE: Insufficient privilege

Now I am using the SYSTEM user as the DBA to log into the account from oewizard. Now my first question:

  1. Does the SYSTEMuser have SYS_DBAprivilege from the beginning

I explicitly grant DBA privilege to SYSTEM from sqlplus (GRANT DBA to SYSTEM; Grant Succeeded) but while connecting via SYSTEM (the same user) through JDBC (oewizard's back end), I still get the same error. So my next question is:

  1. When logged in from sqlplus (as SYSDBA), don't the grants assigned to a given user persist even after we exit sqlplus?
  2. If so what would be the best way to assign GRANTs to a given user so that they persist?
  3. Is there no way we can log in as SYSDBA via JDBC since when I tried it, it didn't allow me

Keen to hear from you

closed as off-topic by Colin 't Hart, Md Haidar Ali Khan, hot2use, Max Vernon, Philᵀᴹ Nov 9 '18 at 13:16

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Colin 't Hart, Md Haidar Ali Khan, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

5

From Oracle documentation-

When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role.

SQL> conn system
Password:


SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE
DBA

SQL> show user;
USER is "SYSTEM"

SQL> grant execute on dbms_lock to jay;
grant execute on dbms_lock to jay
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

The error has occurred because the SYSTEM user has no 'EXECUTE' privilege with GRANT option on DBMS_LOCK.

SQL>conn / as sysdba

SQL> select * from user_tab_privs where grantee='SYSTEM' and table_NAME='DBMS_LOCK';

no rows selected

Also, the DBA role has no 'EXECUTE' object privilege on DBMS_LOCK package.

SQL>  select * from role_tab_privs where role='DBA' and table_name='DBMS_LOCK';

no rows selected

In order to grant execute privilege, you need to connect as SYSDBA which connects to SYS schema, owner of DBMS_LOCK, or grant 'EXECUTE' privilege with GRANT option to the user performing the grant.

[oracle@myserver ~]$ sqlplus system as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 2 14:45:27 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password: 

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

SQL> show user;
USER is "SYS"

SQL> grant execute on dbms_lock to jay;

Grant succeeded.

When logged in from sqlplus (as SYSDBA), don't the grants assigned to a given user persist even after we exit sqlplus?

Yes, they persist.

Is there no way we can log in as SYSDBA via JDBC since when I tried it, it didn't allow me.

Here is a sample connection string which works fine for me.

Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@host:port/SID","sys as sysdba","password");

Not the answer you're looking for? Browse other questions tagged or ask your own question.