We have experienced this very rare and inexplicable behaviour. I have not found a similar case in any google search or blog search.
The schema names and table names have been changed for security but the order of what has happened is correct
1. As a DBA user SYS we have granted DBA role to a user
SQL> grant dba to test;
SQL> grant succeeded
2. We enter as the user test and try to create a table in his own schema
SQL> conn test / password
Connect�.
SQL> show user
USER est "TEST"
3. As user test we attempt to create a table in his own schema
SQL> create table test1(
id number(3),
charex varchar2(32)
);
4. The error from this failed create is the following
Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the user was granted the necessary privilege at a higher label
than the current login.
*Action: Ask the database administrator to perform the operation or grant
the required privileges.
For Trusted Oracle users getting this error although granted the
the appropriate privilege at a higher label, ask the database
administrator to regrant the privilege at the appropriate label.
5. We do not understand the error for the following reasons
i - why is the error mentioning that we have attempted to change security credentials? It is a spurious cause as we have attempted to change a user
ii - why is the databae role DBA not sufficient to allow a user to create a table in his own schema? I have never seen this is 15 years with Oracle products
iii - we are not using Trusted Oracle
6. We have a suspicion as to why thi has occured, but are unsure how the DBA has affected this. We do not have admin privs in Linux to investigate exactly what he has done
i - the DBA has said we must use a particular user for all our DDL and DML operations in the TEST schema. Let us call this user NOTTEST. As NOTTEST we can do the following
SQL> show user
USER est "NOTTEST"
SQL> create table test.test1(
id number(3),
charex varchar2(32)
);
table TEST.TEST1 created.
ii We think that there is a posibility that the installation DBA (not us) has impeded the use of the user TEST at the Operating System, such that it is prohibited from carrying out DML and DDL operations even though at the Oracle Level we have granted DBA which should be sufficient to allow TEST to create a table in his own schema. The operating system is Linux.
iii The person who did this is on holiday for three weeks
7. We know the solucion. It is that the development department send us a script with the schema name included in all DML and DDL. We have asked for this. I am simply intrigued that somehow someone has managed to over-ride the Object and System privileges inherent in the DBA role to prevent us from createing the table. It is more for interest that we wish to understand this issue.
If anyone can explian this problem I would be obliged. The DBA has given us SYS and SYSTEM passwords, but has impeded that this user can create tables. Curious,
I see that this page does not allow the encapsulation of code into boxes for easier reading. I would have liked to have done that.