http://exploreoracle.blogspot.com/2009/03/changing-dba-user-to-normal-user-in.html

Many times you have granted a user DBA super role instead of giving individual privilege to a user. Later whenever you want to revoke DBA role you need to care of which privilege you need to give the user.

Before example let's take a overview about some views related to privileges and roles in oracle.

1)DBA_SYS_PRIVS describes system privileges granted to users and roles.

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
GRANTEE                                   NOT NULL VARCHAR2(30)
PRIVILEGE                                 NOT NULL VARCHAR2(40)
ADMIN_OPTION                                       VARCHAR2(3)


2)USER_SYS_PRIVS describes system privileges granted to the current user.

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                           VARCHAR2(30)
PRIVILEGE                                 NOT NULL VARCHAR2(40)
ADMIN_OPTION                                       VARCHAR2(3)


3)DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database.

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
GRANTEE                                            VARCHAR2(30)
GRANTED_ROLE                              NOT NULL VARCHAR2(30)
ADMIN_OPTION                                       VARCHAR2(3)
DEFAULT_ROLE                                       VARCHAR2(3)


4)DBA_TAB_PRIVS describes all object grants in the database. Note that in the table the column TABLE_NAME does not display only table rather it displays any object, including tables, packages, indexes, sequences, and so on.

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
GRANTEE                                   NOT NULL VARCHAR2(30)
OWNER                                     NOT NULL VARCHAR2(30)
TABLE_NAME                                NOT NULL VARCHAR2(30)
GRANTOR                                   NOT NULL VARCHAR2(30)
PRIVILEGE                                 NOT NULL VARCHAR2(40)
GRANTABLE                                          VARCHAR2(3)
HIERARCHY                                          VARCHAR2(3)


In this example, we want to change a DBA user named "OMS" to a normal user.
Let's see the user OMS has the available roles granted.

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
OMS                            RESOURCE                       NO  YES
OMS                            JAVAUSERPRIV                   NO  YES
OMS                            DBA                            NO  YES


These roles may contain many privilege. For example the role RESOURCE contains following privileges.

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO

8 rows selected.


Let's see the privilege assigned to user OMS.

SQL> select * from dba_sys_privs where grantee='OMS';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
OMS                            UNLIMITED TABLESPACE                     NO


Now let's see which tablespaces contain the objects owned by the user OMS. We need to assign quota on those tablespaces and then revoking DBA role.

The tablespaces contain objects of user OMS.



SQL> DEFINE owner='OMS'
SQL> select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';'
   from dba_tables where owner='&OWNER'
   UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name
||';' from dba_indexes
   where owner='&OWNER'
   UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name
||';' from dba_tab_partitions
   where table_owner='&OWNER'
   UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name
||';' from dba_ind_partitions
   where index_owner='&OWNER';
old   1: select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';'
new   1: select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';'
old   2: from dba_tables where owner='&OWNER'
new   2: from dba_tables where owner='OMS'
old   3: UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';' from dba_indexes
new   3: UNION select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';' from dba_indexes
old   4: where owner='&OWNER'
new   4: where owner='OMS'
old   5: UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';' from dba_tab_partitions
new   5: UNION select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';' from dba_tab_partitions
old   6: where table_owner='&OWNER'
new   6: where table_owner='OMS'
old   7: UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';' from dba_ind_partitions
new   7: UNION select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';' from dba_ind_partitions
old   8: where index_owner='&OWNER'
new   8: where index_owner='OMS'

'ALTERUSEROMSQUOTAUNLIMITEDON'||TABLESPACE_NAME||';'
-----------------------------------------------------------------
Alter user OMS quota unlimited on OMS_INDX_SPC;
Alter user OMS quota unlimited on OMS_SPC;



Let's see if any objects privileges granted to user OMS.

SQL> select * from dba_tab_privs where grantee='OMS';

no rows selected

Now we give privilege and assign quota to user OMS and then revoking DBA role.

Assigning privilege by,
GRANT CREATE SESSION, CREATE TRIGGER, CREATE SEQUENCE, CREATE TYPE, CREATE PROCEDURE,
CREATE CLUSTER, CREATE OPERATOR, CREATE INDEXTYPE, CREATE TABLE TO OMS;

Giving quota on the tablespaces by,

ALTER USER OMS QUOTA UNLIMITED on OMS_SPC;
ALTER USER OMS QUOTA UNLIMITED on OMS_INDX_SPC;

Now revoking DBA role by,

REVOKE DBA FROM OMS;