关于权限设置的一些讨论
最近在上ASKTOM的网站的时候，看了一些关于权限设置方面的内容，觉得还是挺有启发的，所以摘录了一些个人觉得有用的信息来。对于权限的深入理解有了一定的提高。好，下面开始：
一、CREATE SESSION + CREATE TABLE权限无法创建表
简单说明：RESOURCE角色会默认赋给USER一个UNLIMITED TABLESPACE权限，但CREATE TABLE角色不会给用户此权限，所以需要进行手动说明一个quota unlimited on的动作，如下举例：
I recommend you
alter user USERNAME default tablespace <something OTHER than system> quota
unlimited on <something OTHER than system>;
That user can actually create tables, they just cannot create tables that
allocate space ;)
ops$tkyte@ORA817.US.ORACLE.COM> create user a identified by a;
User created.
ops$tkyte@ORA817.US.ORACLE.COM> grant create session, create table to a;
Grant succeeded.
ops$tkyte@ORA817.US.ORACLE.COM> connect a/a
Connected.
ops$tkyte@ORA817.US.ORACLE.COM> create table t (x int);
create table t (x int)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'
ops$tkyte@ORA817.US.ORACLE.COM> create global temporary table t ( x int );
Table created.
ops$tkyte@ORA817.US.ORACLE.COM> connect /
Connected.
ops$tkyte@ORA817.US.ORACLE.COM> alter user a default tablespace users
2 quota unlimited on users;
User altered.
ops$tkyte@ORA817.US.ORACLE.COM> connect a/a
Connected.
ops$tkyte@ORA817.US.ORACLE.COM> create table tt ( x int );
Table created.
二、关于权限创建的建议
It follows with this advice:
do not use connect, resource or DBA unless you have to. Somethings require DBA
(the products look for that role unfortunately) but you should not need connect
(which has WAY too much power) and resource (even more)
create your OWN roles and use those instead.
三、关于隐藏的Unlimited Tablespace权限的赋予和收回
简单说明：DBA、RESOURCE角色并不含有Unlimited Tablespace权限，但是当把角色赋予USER时，会隐含得赋予用户Unlimited Tablespace权限，当revoke时也默认得收回。但是显式的Unlimited Tablespace权限只能赋给USER，不能赋给ROLE。
UNLIMITED TABLESPACE cannot actually be granted to a role at all! so, it is
not in the data dictionary.
you cannot revoke unlimited tablespace from a role (in fact, you cannot even
really grant it to a role)
you can however from a user.
ops$tkyte@ORA9IR2> grant unlimited tablespace to scott;
Grant succeeded.
ops$tkyte@ORA9IR2> grant unlimited tablespace to connect;
grant unlimited tablespace to connect
*
ERROR at line 1:
ORA-01931: cannot grant UNLIMITED TABLESPACE to a role
the granting of RESOURCE or DBA to a user will grant them unlimited tablespace.
revoking will revoke it.
I'll hypothesize:
a) user was granted DBA and RESOURCE (that gave them unlimited tablespace)
b) user was revoked DBA (that took it away)
c) simply granting it to them will put it back...
Consider:
ops$tkyte@ORA9IR2> drop user a cascade;
User dropped.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create user a identified by a;
User created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> grant dba, resource to a;
Grant succeeded.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select 'tab', privilege from dba_tab_privs where grantee = 'A'
2 union all
3 select 'sys', privilege from dba_sys_privs where grantee = 'A'
4 union all
5 select 'rol', granted_role from dba_role_privs where grantee = 'A';
'TA PRIVILEGE
--- ----------------------------------------
sys UNLIMITED TABLESPACE
rol DBA
rol RESOURCE
now you see it...
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> revoke dba from a;
Revoke succeeded.
ops$tkyte@ORA9IR2> select 'tab', privilege from dba_tab_privs where grantee = 'A'
2 union all
3 select 'sys', privilege from dba_sys_privs where grantee = 'A'
4 union all
5 select 'rol', granted_role from dba_role_privs where grantee = 'A';
'TA PRIVILEGE
--- ----------------------------------------
rol RESOURCE
Now you don't
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> grant resource to a;
Grant succeeded.
ops$tkyte@ORA9IR2> select 'tab', privilege from dba_tab_privs where grantee = 'A'
2 union all
3 select 'sys', privilege from dba_sys_privs where grantee = 'A'
4 union all
5 select 'rol', granted_role from dba_role_privs where grantee = 'A';
'TA PRIVILEGE
--- ----------------------------------------
sys UNLIMITED TABLESPACE
rol RESOURCE
now you see it again...
四、查询所有权限清单
get the list of all privileges available：
ops$tkyte@ORA9IR2> select * from system_privilege_map;
PRIVILEGE NAME PROPERTY
---------- ------------------------------ ----------
-3 ALTER SYSTEM 0
.....
0 = valid, good
1 = was valid, no longer is a privilege.
五、查看SYNONYM对应的OBJECT的权限情况
scott@ORA10GR1> select *
2 from all_tab_privs
3 where (table_schema,table_name) in ( select table_owner, table_name
4 from all_synonyms
5 where synonym_name = 'TKYTE_T' )
6 /
GRANTOR GRANTEE TABLE_SCHEMA TABLE_NAME PRIVILEGE GRA HIE
---------- ---------- -------------- ---------- ------------- --- ---
OPS$TKYTE SCOTT OPS$TKYTE T SELECT NO NO
六、GRANT ANY ROLE权限的问题
简单说明：如果通过Package来为其他用户分配权限，则这个Package的OWNER必须要有GRANT ANY ROLE的权限，但是执行、取消执行该Package则无需GRANT ANY ROLE权限，而仅需要有操作Package的权限就可以了。但是不建议为用户赋GRANT ANY ROLE权限。
If the GRANT statment is in a package the OWNER of the package needs the
ability to grant the role, the user running the package needs no special privs.
The invoker of the procedure does not need this privilege, only the OWNER
(definer) does.
Don't grant "GRANT ANY ROLE", that will create a huge security problem.
I was suggesting to the person here, that asked the question, that they
NEED
NOT grant this super powerful priv. That all they needed to do was create a
package that gave the grants as the owner of the objects -- grant execute on
that package to this other user and they are done.
Don't grant GRANT ANY ROLE, that would be a really bad idea.
需要注意的是：GRANT ANY ROLE权限是不能分配DBA和RESOURCE角色给自己或其他USER的。这是因为这两个ROLE在赋予时会默认得将UNLIMITED TABLESPACE系统权限同时赋给USER，而GRANT ANY ROLE并没有赋予用户系统权限的权限，因此失败。
----------------------------------------------------------------------------------------------------------
Case, SYSTEM grant GRANT ANY ROLE to scott. SCOTT no able to grant DBA to self
or any other user.
system@o817> grant grant any role to scott;
Grant succeeded.
scott@o817> select * from dba_users;
select * from dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
scott@o817> grant dba to scott;
grant dba to scott
*
ERROR at line 1:
ORA-01031: insufficient privileges
scott@o817> grant dba to SYSTEM;
grant dba to SYSTEM
*
ERROR at line 1:
ORA-01031: insufficient privileges
---------------------------------------------
Try any role other then DBA or RESOURCE.
They fail because they implicity
attempt to grant the privilege UNLIMITED TABLESPACE in addition to the ROLE
(they are "magic", special). You'll find that scott can grant important stuff
like JAVASYSPRIV to anyone (including himself). Give me the ability to create
a java stored procedure and GRANT ANY ROLE and I'll wreak havoc on your
database. Or, if you have DBA-like roles you've created (as you should), they
can grant those.
Powerful? Yes, definitely.
七、ROLE危险性讨论
简单说明：除了GRANT ANY ROLE角色外，execute any procedure、alter user等权限都会造成比较严重的风险。可以使用户获得DBA权限(How?)
GRANT ANY ROLE does allow 2 roles that are granted to DBA
to be granted: EXP_FULL_DATABASE and IMP_FULL_DATABASE.
The IMP_FULL_DATABASE role has the ability to drop any
database object and create any database object. Also,
the privilege to EXECUTE ANY PROCEDURE opens up more
possibilities (as does BECOME USER).
Followup:
Good point -- if you give me:
o create session
o execute any procedure
I can take over your database and get ANY priv I want. It would take about 30
seconds for me to have DBA and anything else I wanted.