Create Superuser who can access more than one Schema in oracle 11G

Question

I have two Schema Schema-1 and Schema-2. I want to create one super User Who can access both Schema(Schema-1 and Schema-2).

I want to create a user with command in oracle 11g. It is possible?

Answer 1

Such an user already exists; it is called SYS, who owns the database. Though, it is not a very good idea to use it for daily jobs - you'd rather (as you wanted) create your own "superuser" who is capable of doing such things. For example:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> create user superuser identified by superman;

User created.

SQL> grant dba to superuser;

Grant succeeded.

OK, let's try it:

SQL> connect superuser/superman
Connected.
SQL> select count(*) From scott.emp;

  COUNT(*)
----------
        14

SQL> select table_name from dba_tables where owner = 'MIKE';

TABLE_NAME
------------------------------
EMP
DEPT
BONUS
SALGRADE
DUMMY
ABC

6 rows selected.

SQL> select * from mike.abc;

       KEY         ID        SEQ THINGS     DESCR
---------- ---------- ---------- ---------- ----------
         1          1          0 Food       Chicken
         2          1          1 Cars       BMW
         3          1          2 Sport      Soccer
         4          2          0 Food       Mutton
         5          2          1 Cars       Ford
         6          2          2 Sport      Tennis

6 rows selected.

SQL>

Now, is DBA right role for that user, I can't tell. Maybe it is not, so perhaps you'd rather grant only required set of privileges. Which set is it, I can't tell either.

Maybe it would be enough to grant e.g. select privileges to superuser for both schema1 and schema2 users' tables. Though, you can't do that in a single command - you'd have to do it separately for each user and for each of their tables (which means a lot of grant select statements). Let's try it:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> revoke dba from superuser;

Revoke succeeded.

SQL>

It is a boring job writing statement-by-statement, so I'll write code to write code for me:

SQL> select 'grant select on ' || owner ||'.' ||table_name || ' to superuser;' str
  2  from dba_tables
  3  where owner in ('SCOTT', 'MIKE')
  4  order by owner, table_name;

STR
--------------------------------------------------------------------------------
grant select on MIKE.ABC to superuser;
grant select on MIKE.BONUS to superuser;
grant select on MIKE.DEPT to superuser;
<snip>
grant select on SCOTT.TEST_B to superuser;
grant select on SCOTT.TEST_D to superuser;

26 rows selected.

SQL>

OK; now copy/paste the above grant statements and run them.

SQL> grant select on MIKE.ABC to superuser;

Grant succeeded.

SQL> grant select on MIKE.BONUS to superuser;

Grant succeeded.

SQL> grant select on MIKE.DEPT to superuser;

Grant succeeded.

<snip>

SQL> grant select on SCOTT.TEST_B to superuser;

Grant succeeded.

SQL> grant select on SCOTT.TEST_D to superuser;

Grant succeeded.

SQL>

Does it work?

SQL> connect superuser/superman
ERROR:
ORA-01045: user SUPERUSER lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL>

Aha! Not just yet! Revoking DBA revoked a large set of privileges, so superuser now exists as user, but can't do anything. So, let's let it connect to the database:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> grant create session to superuser;

Grant succeeded.

SQL> connect superuser/superman
Connected.
SQL> select * From scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * From mike.abc;

       KEY         ID        SEQ THINGS     DESCR
---------- ---------- ---------- ---------- ----------
         1          1          0 Food       Chicken
         2          1          1 Cars       BMW
         3          1          2 Sport      Soccer
         4          2          0 Food       Mutton
         5          2          1 Cars       Ford
         6          2          2 Sport      Tennis

6 rows selected.

SQL>

Right; much better. That's what I meant by saying "grant only required set of privileges"; don't grant more privileges than someone really needs.