FILE: /u02/shareplex/var/log/sp_ausrpts_sunshpxunx001_opo_ddl_01.log DATE: Tue Oct 3 03:38:30 2006 LOG#: 1
06-10-03 04:01:59.578589 23251 -1232990080 9i DDL, User Name = "SYSTEM" Default Schema = "SYSTEM" 06-10-03 04:01:59.578635 23251 -1232990080 DDL_SQL = "grant select any table to splex3 with admin option" 06-10-03 04:01:59.582612 23251 -1232990080 SUCCESS on DDL - Ora
To revoke privileges you must have SYSDBA privileges as well as connect as sysdba.
The problem has nothing to do with the user SYS or SYSTEM. For Oracle 9i DDL we will become the user who executed the DDL by issuing a :
Alter table set current_schema=user; where user can be SYS or SYSTEM.
The problem is that to revoke privileges you must have SYSDBA privileges as well as connect as sysdba.
An example follows: grant sysdba to userA; Grant succeeded. SQL> connect userA/userA Connected. SQL> revoke sysdba from system; revoke sysdba from system * ERROR at line 1: ORA-01031: insufficient privileges
SQL> connect userA/userA as sysdba Connected. SQL> revoke sysdba from system; Revoke succeeded. It may be possible to get this to work for Shareplex but two things would need to be done. First you must grant sysdba�to the splex user. Second, you would need to go into orasetup and when asked for the splex username/password you might be able use the as sysdba. If it accepts it then everytime we connect we will connect as sysdba and then the DDL should work.