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;

Comments

Post a Comment

Popular posts from this blog

ORA-00923: FROM keyword not found where expected

Problem Symptom While performing a simple select statement it fails with error ORA-00923: FROM keyword not found where expected like below. SQL> select 48*1024*1024*1024 decimal from dual; select 48*1024*1024*1024 decimal from dual * ERROR at line 1: ORA-00923: FROM keyword not found where expected Cause of the problem The ORA-00923 can be caused by two different reasons. 1)Oracle itself expect FROM keyword in the position but it could not find any. Like below, SQL> select 48*1024 om dual; select 48*1024 om dual * ERROR at line 1: ORA-00923: FROM keyword not found where expected As no from keyword found so error arises. Oracle expected from in place of keyword dual. 2)The secong cause can sometimes mislead you. Like in case of SQL> select 48*1024*1024*1024 decimal from dual; select 48*1024*1024*1024 decimal from dual * ERROR at line 1: ORA-00923: FROM keyword not found where exp
Read more

How to make partitioning in Oracle more Quickly

As the table size grows and full table scans happens more frequently then there is no alternative than partitioning in oracle. Partition greatly enhance performance of a query. I will start my partitioning system simply with an example. In my database the following query took 30~35 seconds to complete. After partitioning the performance increased amazingly and it then took only 1 second. My query was, SELECT DISTINCT fs.type, fs.id , fs.pid, fs.cid , fs.cr_id, fs.created_date FROM summary fs where fs.id in (select fa.id from forms fa where fa.sar in (select la.sar from login la where la.login_id=1 and fa.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') and fs.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') ) )and mode=0; After seeing above query I decide to make range partition on column created_date both in summary table and forms table. Below is the list of procedur
Read more

Copy files between Unix and Windows with rcp

There are many third party tools by which you can copy files between windows and unix machine. Some common tools are winscp, SSHSecureShellClient etc. Instead of using any third party tool, you can use original unix and windows built-in tool to do the task. The RCP (Remote CoPy) is a standard UNIX tool and a Microsoft Windows 2000 tool that copies files/folder to and from computer running the RCP service. Before you can use the RCP tool from a Windows-based computer you must do something in your UNIX computer. you must turn on the RCP service and update the security files to allow the Windows-based computer to connect to your unix machine over this service. The following steps will help you. Step 01: Turn on RCP service on UNIX Host Turn on the RCP service on unix machine by using inetd: 1. Log on as root. 2. Edit the file /etc/Inetd.conf. 3. Uncomment the lines that start with shell and that start with exec. 4. Save the file. 5. Use ps -ef |grep inetd and determine inetd service p
Read more