Tuesday, March 31, 2009

Changing a DBA user to a normal user in oracle

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.

SQL> desc dba_sys_privs
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.

SQL> desc user_sys_privs
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.

SQL> desc dba_role_privs
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.

SQL> desc dba_tab_privs
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.

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

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.

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

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;

No comments:

Post a Comment