A Problem with REVOKE

If you grant the DBA role to a user, Oracle also grants it the UNLIMITED TABLESPACE system privilege. If you then revoke the DBA role from this user, Oracle also revokes its UNLIMITED TABLESPACE system privilege. This isn’t too much of an issue.

 

However, if you grant the UNLIMITED TABLESPACE system privilege to a user by itself THEN grant it the DBA role, Oracle seems to have no idea where the UNLIMITED TABLESPACE system privilege came from. If you then revoke the DBA role from this user, Oracle still revokes the UNLIMITED TABLESPACE system privilege from it. This may not be what you intended to do. You can see what I mean in the example below, which I tested in an Oracle 11.2 database.

 

First I created a user:

 

SQL> create user a identified by b

  2  /

 

User created.

 

SQL>

 

I checked that it had no roles nor system privileges:

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL>

 

I granted the DBA role to the user and checked that this also gave it the UNLIMITED TABLESPACE system privilege:

 

SQL> grant dba to a

  2  /

 

Grant succeeded.

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

GRANTED_ROLE

------------------------------

DBA

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

 

PRIVILEGE

----------------------------------------

UNLIMITED TABLESPACE

 

SQL>

 

I revoked the DBA role and checked that Oracle also revoked the UNLIMITED TABLESPACE system privilege:

 

SQL> revoke dba from a

  2  /

 

Revoke succeeded.

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL>

 

I granted the UNLIMITED TABLESPACE system privilege to the user independently:

 

SQL> grant unlimited tablespace to a

  2  /

 

Grant succeeded.

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

 

PRIVILEGE

----------------------------------------

UNLIMITED TABLESPACE

 

SQL>

 

I granted the DBA role to the user:

 

SQL> grant dba to a

  2  /

 

Grant succeeded.

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

GRANTED_ROLE

------------------------------

DBA

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

 

PRIVILEGE

----------------------------------------

UNLIMITED TABLESPACE

 

SQL>

 

I revoked the DBA role from the user. Oracle revoked the UNLIMITED TABLESPACE system privilege at the same time despite the fact that I had granted it separately:

 

SQL> revoke dba from a

  2  /

 

Revoke succeeded.

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

no rows selected

SQL>

How NOT to Install Oracle Software

Once upon a time there was a DBA who always used the Oracle Universal Installer (OUI) to install Oracle software. Then a manager asked him to install it by copying an Oracle home from one place to another using operating system commands. The DBA said this was a bad idea but was told to do it anyway as it had never caused any problems in the past. The new Oracle home worked OK for some time until the company hit an Oracle bug. Oracle knew about this bug and there was a patch for it. However, the company were unable to apply it as OPatch (Oracle's patching utility) would not work. Oracle could not help them either as the installation method used was unsupported. After that the company always used the OUI to install Oracle software and OPatch worked happily ever after.

I think this was the bug which caused the problem. It was bug 4192148, also known as bug 4604896. It affected Oracle 9.2.0.7.0. The following SQL statement creates a view. Line 2 contains a select a.* and the comma separating it from the bytes column is at the start of line 3:

SQL> create or replace view object_sizes as

  2  select a.*

  3  , bytes

  4  from user_objects a, user_segments b

  5  where a.object_name = b.segment_name

  6  /

View created.

SQL>

You can select object_name, which is at the start of select a.*:

SQL> select object_name from object_sizes

  2  where rownum < 6

  3  /

OBJECT_NAME

-------------------------------------------------------

TOT_READ_WRITES

PACSTAT1

PACSTAT2

LOCK_TYPES

MFSESS_IO

SQL>

... and you can select secondary, which is at the end:

SQL> select secondary from object_sizes

  2  where rownum < 6

  3  /

S

-

N

N

N

N

N

SQL>

... but if you try to select bytes, you get an ORA-00936:

SQL> select bytes from object_sizes

  2  where rownum < 6

  3  /

select bytes from object_sizes

                  *

ERROR at line 1:

ORA-00936: missing expression

SQL>

That's because the view is not stored correctly. There is a comma at the end of the 4th line after a."SECONDARY". There is another comma at the start of the 5th line before bytes but there is nothing between them:

SQL> set long 4000

SQL> select text from user_views

  2  where view_name = 'OBJECT_SIZES'

  3  /

TEXT

-------------------------------------------------------

select a."OBJECT_NAME",a."SUBOBJECT_NAME",a."OBJECT_ID"

,a."DATA_OBJECT_ID",a."OBJECT_TYPE",a."CREATED",a."LAST

_DDL_TIME",a."TIMESTAMP",a."STATUS",a."TEMPORARY",a."GE

NERATED",a."SECONDARY",

, bytes

from user_objects a, user_segments b

where a.object_name = b.segment_name

SQL>

You cannot recompile the view either:

SQL> alter view object_sizes compile

  2  /

alter view object_sizes compile

                              *

ERROR at line 1:

ORA-00936: missing expression

SQL>

Assuming you have used the OUI to install the Oracle software, you can apply the appropriate patch (I can no longer remember the number but I guess it must still be on My Oracle Support). If not, you can rewrite the view by putting the comma on the same line as the select a.* then everything works correctly:

SQL> create or replace view object_sizes as

  2  select a.*,

  3  bytes

  4  from user_objects a, user_segments b

  5  where a.object_name = b.segment_name

  6  /

View created.

SQL> select object_name from object_sizes

  2  where rownum < 6

  3  /

OBJECT_NAME

-------------------------------------------------------

TOT_READ_WRITES

PACSTAT1

PACSTAT2

LOCK_TYPES

MFSESS_IO

SQL> select secondary from object_sizes

  2  where rownum < 6

  3  /

S

-

N

N

N

N

N

SQL> select bytes from object_sizes

  2  where rownum < 6

  3  /

     BYTES

----------

     16384

     40960

     32768

     49152

     16384

SQL> set long 4000

SQL> select text from user_views

  2  where view_name = 'OBJECT_SIZES'

  3  /

TEXT

-------------------------------------------------------

select a."OBJECT_NAME",a."SUBOBJECT_NAME",a."OBJECT_ID"

,a."DATA_OBJECT_ID",a."OBJECT_TYPE",a."CREATED",a."LAST

_DDL_TIME",a."TIMESTAMP",a."STATUS",a."TEMPORARY",a."GE

NERATED",a."SECONDARY",

bytes

from user_objects a, user_segments b

where a.object_name = b.segment_name

SQL> alter view object_sizes compile

  2  /

View altered.

SQL>

ORA-00990

This was tested on Oracle 11. You can grant roles and system privileges in the same statement. But if you want to include object privileges, you need to use a new GRANT statement:

SQL> grant dba, alter system,

  2  execute on sys.dbms_lock

  3  to andrew

  4  /

grant dba, alter system,

      *

ERROR at line 1:

ORA-00990: missing or invalid privilege

SQL> grant dba, alter system

  2  to andrew

  3  /

Grant succeeded.

SQL> grant execute on sys.dbms_lock

  2  to andrew

  3  /

Grant succeeded.

SQL>

remote_os_authent

The Oracle RDBMS used to have a parameter called remote_os_authent. This specified whether or not you could connect to an instance remotely using OS authentication. Setting it to true was a security risk, especially if you used OS authentication for database users which had the DBA role. For example, you might have an externally identified user in your database called ORACLE and grant the DBA role to that user. A malicious user with admin rights on a remote machine could create a user called oracle on that machine and use it to connect to your database as an administrator without providing a password. In version 11, the Oracle RDBMS deprecated this parameter but have retained it (for now) for backward compatibility. The example below illustrates this. I ran it on a UNIX server as a UNIX user called oracle. First I connected to the database as SYS and set remote_os_authent to true in the server parameter file:

 

SQL> conn / as sysdba

Connected.

SQL> alter system set

  2  remote_os_authent = true

  3  scope = spfile

  4  /

 

System altered.

 

SQL>

 

Then I bounced the database. The Oracle RDBMS displayed an error message when it saw the deprecated parameter:

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

 

Total System Global Area  158703616 bytes

Fixed Size                  2086736 bytes

Variable Size              83888304 bytes

Database Buffers           67108864 bytes

Redo Buffers                5619712 bytes

Database mounted.

Database opened.

SQL>

 

I reconnected to the database remotely and reset remote_os_authent in the server parameter file:

 

SQL> conn /@test11

Connected.

SQL> show user

USER is "ORACLE"

SQL> alter system reset remote_os_authent

  2  scope = spfile

  3  /

 

System altered.

 

SQL>

 

Then I bounced the database again. This time there was no error message:

 

SQL> conn / as sysdba

Connected.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  158703616 bytes

Fixed Size                  2086736 bytes

Variable Size              88082608 bytes

Database Buffers           62914560 bytes

Redo Buffers                5619712 bytes

Database mounted.

Database opened.

SQL>

 

Changing the remote_os_authent parameter stopped the remote connection working:

 

SQL> conn /@test11

ERROR:

ORA-01017: invalid username/password; logon denied

 

Warning: You are no longer connected to ORACLE.

SQL>