(oracle)11g与10g中alter session权限差异

SQL> select * from v$version;

BANNER
---------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

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

GRANTEE              PRIVILEGE                      ADMIN_
-------------------- ------------------------------ ------
LUOXUAN              CREATE SESSION                 NO
LUOXUAN              UNLIMITED TABLESPACE           NO

SQL> conn luoxuan/luoxuan
Connected.
SQL> alter session set sql_trace=true;

Session altered.

这里alter session 好像没有问题！！！下面看一下11g的情况。

SQL> select * from v$version;

BANNER
-------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

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

GRANTEE                        PRIVILEGE                                ADM
------------------------------ -----------------------------------
LUOXUAN                        CREATE SESSION                           NO
LUOXUAN                        UNLIMITED TABLESPACE                     NO

SQL> conn luoxuan/luoxuan
Connected.
SQL> alter session set sql_trace=true;
alter session set sql_trace=true
*
ERROR at line 1:
ORA-01031: insufficient privileges

11g中尽然报越权。

继续往下看：

10g:
改变一下sort area

SQL> alter session set sort_area_size=10240; 

Session altered.

11g:
改变一下sort area

SQL> alter session set sort_area_size=10240; 

Session altered.

小结：alter session在10g,11g中，都没有问题，那为什么alter session set sql_trace=true就会报越权错误呢？

11g:

SYS用户

SQL> grant alter session to luoxuan;

Grant succeeded.

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

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
LUOXUAN                        ALTER SESSION                            NO
LUOXUAN                        CREATE SESSION                           NO
LUOXUAN                        UNLIMITED TABLESPACE                     NO

LUOXUAN用户
SQL> show user
USER is "LUOXUAN"
SQL> alter session set sql_trace=true;

Session altered.

通过alter session授权后，就可以了。