Monday, October 26, 2009

CREATE VIEW FAILED with DBA Privilege

Today one of our developer came to me and informed me that he is getting below error:
ERROR at line 1:
ORA-01031: insufficient privileges
He could select the table from the user where he wanted to create the view he also informed me that the user had given DBA privilege.
I was wondering what could be the reason ?
If any user has DBA privilege it will get both SELECT ANY TABLE & CREATE ANY VIEW privileges.
So there is no way that the view creation would fail.

Then I had simulated the whole process as below:

SQL> conn / as sysdba
Connected.
SQL>
SQL> create user user1 identified by user1;
User created.
SQL> grant create session to user1;
Grant succeeded.
SQL> grant create table to user1;
Grant succeeded.
SQL> alter user user1 quota 5M on users;
User altered.
SQL> create user dbauser identified by dbauser;
User created.
SQL> conn user1
Enter password:
Connected.
SQL> create table tbl(names varchar2(10)) tablespace users;
Table created.
SQL> insert into tbl values('aaa');
1 row created.
SQL> insert into tbl values('bbb');
1 row created.
SQL> insert into tbl values('ccc');
1 row created.
SQL> select * from tbl;
NAMES
----------
aaa
bbb
ccc
SQL> conn / as sysdba
Connected.
SQL> grant dba to dbauser;
Grant succeeded.
SQL> conn dbauser
Enter password:
Connected.
SQL> select * from user1.tbl;
NAMES
----------
aaa
bbb
ccc
SQL> create view usertbl as select * from user1.tbl;
create view usertbl as select * from user1.tbl
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> grant select any table to dbauser;
Grant succeeded.
SQL> conn dbauser
Enter password:
Connected.
SQL> create view usertbl as select * from user1.tbl;
View created.
SQL>

(after revoking SELECT ANY TABLE, i tried with 'GRANT SELECT ON USER1.TBL TO DBAUSER' it allowed DBAUSER to create view)

Above was surprising to me. Then searching the web I found below:
"In order to create a view in a schema, that schema must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The view owner must be granted these privileges directly, rather than through a role. The reason is that privileges granted to roles cannot be inherited via objects."

Thursday, October 1, 2009

Installing JVM Manually

We have some Oracle 9i Databases on HP-UX which were installed without JVM.
Some of our new applicatons demanded Java Packages. So, we had to unstall JVM manually on those servers.

We did this following below steps (for all OS,tested on solaris, windows, HP-UX):

1. Additional 60 MB of system tablespace
2. 50 MB of SHARED_POOL_SIZE
3. 20 MB of JAVA_POOL_SIZE
4. run $ORACLE_HOME/javavm/install/rmjvm.sql [If JVM previously installed of partly
installed]
5. run $ORACLE_HOME/javavm/install/initjvm.sql


If the oracle DB version in 10g then after step 5 below error will be occured

ORA-29558: JAccelerator (NCOMP) not installed

To resolve this JAccelerator needed to be installed from oracle database 10g companion CD.
http://www.orafaq.com/forum/t/69986/2/
http://www.dbaportal.eu/?q=node/13
Metalink 276554.1 "How to Reload the JVM in 10.1.0.X and 10.2.0.X"

Follow below steps if some one needs xml options:

6. run $ORACLE_HOME/xdk/admin/initxml.sql
7. run $ORACLE_HOME/xdk/admin/xmlja.sql
8. run $ORACLE_HOME/rdbms/admin/catjava.sql
9. run $ORACLE_HOME/rdbms/admin/catexf.sqla

SP2-1503: Unable to initialize Oracle call interface

It was the first prblem i had to solve for my users as DBA.

One of our user uses a 10g client(10.2.0.1.0) on windows server 2003.
From that machine he uses sqlplus from command prompt.
On that day he was complaining that after reboot (it was the first reboot after installation of orace client) he is getting SP2-1503 error when he uses sqlplus from command prompt.

At first I thought that It should be because of improper value of environment vaiable PATH.
So that, on command prompt I typed:

echo %PATH%

and found that it was ok. Then I started searching the we and found below solution on OTN:

Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2.0 to 10.2.0.1.0 Oracle Server - Standard Edition - Version: 10.1.0.2.0 to 10.2.0.1.0 Microsoft Windows 2000 Microsoft Windows XP Microsoft Windows Server 2003

Symptoms:
When logged on to the Windows server as a non-Administrator OS account using Microsoft Terminal Services client (mstsc.exe), starting SQL*Plus fails with:

SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

Starting SQL*Plus works when logged on to the Windows server using an Administrator OS account.
Starting SQL*Plus works when logged on locally to the Windows server console using the non-Administrator OS account.

Cause:
The issue is related to a Windows Security configuration. The problem is caused by a security policy called "Create Global Objects". The user account that is used to run the program does not have the "Create global objects" user right. This security policy was introduced with Windows 2000 SP4, and determines if applications started during a Terminal Services session can create or access globally accessible memory.
By default, members of the Administrators group, the System account, and Services that are started by the Service Control Manager are assigned the "Create global objects" user right.

Solution:
Assign the "Create global objects" user right to the non-Administrator account.
1. Click Start, point to Programs, point to Administrative Tools, and then click Local Security Policy.
2. Expand Local Policies, and then click User Rights Assignment.
3. In the right pane, double-click Create global objects.
4. In the Local Security Policy Setting dialog box, click Add.
5. In the Select Users or Group dialog box, click the user account that you want to add, click Add, and then click OK.6. Click OK.

http://kr.forums.oracle.com/forums/thread.jspa?threadID=338426