在Oracle11G中创建可以访问多个Schema的超级用户

Question

我有两个模式模式-1和模式-2。我想创建一个可以同时访问Schema(Schema-1和Schema-2(的超级用户。

我想在oracle11g中创建一个带有命令的用户。有可能吗？

Answer 1

这样的用户已经存在；它被称为SYS，拥有该数据库。不过，在日常工作中使用它并不是一个好主意——你宁愿(按照你的意愿(创建自己的"超级用户"，他有能力做这些事情。例如：

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> create user superuser identified by superman;
User created.
SQL> grant dba to superuser;
Grant succeeded.

好的，让我们试试：

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>

现在，我不知道DBA是否适合该用户。也许不是，所以也许您宁愿只授予所需的一组特权。是哪一套，我也说不清。

也许对于schema1和schema2用户的表向superuser授予例如select特权就足够了。不过，您不能在一个命令中做到这一点——您必须为每个用户和他们的每个表分别执行这一操作(这意味着有很多的grant select语句(。让我们试试：

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

一句接一句地写是一项无聊的工作，所以我会写代码为我写代码：

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；现在复制/粘贴上面的grant语句并运行它们。

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>

它有效吗？

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

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

啊哈！还没有！撤销DBA撤销了一大组权限，所以superuser现在作为用户存在，但不能做任何事情。所以，让它连接到数据库：

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>

对；好多了。这就是我所说的"只授予所需的特权"的意思；不要给予比某人真正需要的更多的特权。