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.?
?
?