X » 日志 » Some research on Oracle Role Privilege
Some research on Oracle Role Privilege
Robert 发表于 2008-10-08 23:04:44
Some research on Oracle Role Privilege
Sometimes we would encounter the problem that the package is not working because of lacking privilege. But actually the DBA has granted the required privileges to the role which is granted to the user who call the package. Why? Here is a little research on this.
Prepare Environment
Create two users, test_user_1, test_user_2, both users have the privilege to create session and tables.
SQL> CREATE USER test_user_1 IDENTIFIED BY "test" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PROFILE DEFAULT QUOTA UNLIMITED ON "USERS";
User created
SQL> GRANT CREATE SESSION TO test_user_1;
Grant succeeded
SQL> GRANT CREATE TABLE TO test_user_1;
Grant succeeded
SQL> CREATE USER test_user_2 IDENTIFIED BY "test" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PROFILE DEFAULT QUOTA UNLIMITED ON "USERS";
User created
SQL> GRANT CREATE SESSION TO test_user_2;
Grant succeeded
SQL> GRANT CREATE TABLE TO test_user_2;
Grant succeeded
Create a table in schema test_user_2. and insert one example record.
SQL> show user;
User is "test_user_2"
SQL> create table test_tab_2 (id number, name varchar2(10));
Table created
SQL> insert into test_tab_2 values (1, 'robert');
1 row inserted
SQL> commit;
Commit complete
Create role test_role_crt_tab_vw this role has the privilege to select on any table. Grant this role to user test_user_1.
SQL> CREATE ROLE test_role_crt_tab_vw NOT IDENTIFIED;
Role created
SQL> GRANT CREATE TABLE TO test_role_crt_tab_vw;
Grant succeeded
SQL> GRANT CREATE VIEW TO test_role_crt_tab_vw;
Grant succeeded
SQL> GRANT SELECT ANY TABLE TO test_role_crt_tab_vw;
Grant succeeded
SQL> GRANT "TEST_ROLE_CRT_TAB_VW" TO TEST_USER_1;
Grant succeeded
Have a double check
SQL> show user;
User is "test_user_1"
SQL> select * from session_roles;
ROLE
------------------------------
TEST_ROLE_CRT_TAB_VW
As role test_role_crt_tab_vw has the select privilege to any table, and it is granted to test_user_1, test_user_1 should have the select privilege on table test_user_2.test_tab_2.
Let’s have a look.
SQL> show user;
User is "test_user_1"
SQL> select * from test_user_2.test_tab_2;
ID NAME
---------- ----------
1 robert
Yes, test_user_1 can see the data in table test_tab_2 in user test_user_2 through role test_role_crt_tab_vw.
Now, let’s see what would happen if we put the query on test_tab_2 in a test_user_1 package, named and anonymous.
Named package:
SQL> CREATE OR REPLACE PACKAGE test_package AS
2 PROCEDURE test_procedure;
3 END test_package;
4 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY test_package AS
2 PROCEDURE test_procedure
3 IS
4 v_sql VARCHAR2(1000);
5 BEGIN
6 v_sql := 'create table test_tab_1 as select * from test_user_2.test_tab_2';
7 EXECUTE IMMEDIATE v_sql;
8 END test_procedure;
9 END test_package;
10 /
Package body created
Check the package status.
SQL> select object_name, object_type, status from user_objects where object_type like 'PACKAGE%';
OBJECT_NAME OBJECT_TYPE STATUS
----------------------------------- ------------------- -------
TEST_PACKAGE PACKAGE VALID
TEST_PACKAGE PACKAGE BODY VALID
Let’s see whether we can create table test_tab_1 basing on test_tab_2.
SQL> exec test_package.test_procedure;
begin test_package.test_procedure; end;
ORA-00942: Table or view does not exists
ORA-06512: In "TEST_USER_1.TEST_PACKAGE", line 7
ORA-06512: In line 1
Weird, we just checked that we can query on table test_tab_2 why it says the table doesn’t exist now?
Let’s try to use anonymouspackage.
SQL> DECLARE
2 v_sql VARCHAR2(1000);
3 BEGIN
4 v_sql := 'create table test_tab_1 as select * from test_user_2.test_tab_2';
5 EXECUTE IMMEDIATE v_sql;
6 END;
7 /
PL/SQL procedure successfully completed
Apparently, the anonymouspackage is executed successfully and the table should have been created. Let’s double check.
SQL> select * from test_tab_1;
ID NAME
---------- ----------
1 robert
Yes, it works.
If we refers to Oracle document, we can find out the root cause that if it is a named package, the role granted to the user would be disabled. Section 5.2.5
That means the test_role_crt_tab_vw is not usable and then the user test_user_1 doesn’t have the SELECT privilege on test_user_2.test_tab_2.
But the anonymous package can use the privilege received from role, so it is able to select test_tab_2.
Role in DDL
Let’s do another test. As the role test_role_crt_tab_vw has the privilege to create table and view and it is granted to test_user_1. It sounds that the user should be able to create table and view. Let’s see whether it is true.
Double check the role and user privileges.
SQL> show user;
User is "test_user_1"
SQL> select ROLE, PRIVILEGE from role_sys_privs where role = 'TEST_ROLE_CRT_TAB_VW';
ROLE PRIVILEGE
------------------------------ ---------------------------------------- ------------
TEST_ROLE_CRT_TAB_VW SELECT ANY TABLE
TEST_ROLE_CRT_TAB_VW CREATE VIEW
TEST_ROLE_CRT_TAB_VW CREATE TABLE
SQL> select * from session_roles;
ROLE
------------------------------
TEST_ROLE_CRT_TAB_VW
Now, let’s do the test.
SQL> create view tt as select * from test_user_2.test_tab_2;
create view tt as select * from test_user_2.test_tab_2
ORA-01031: Insufficient Privilege
Weird, it is saying insufficient privilege?
It is saying insufficient privilege on test_tab_2 actually, but not no privilege to create view.
Refer to Oracle document, section 5.2.6
The SELECT privilege on test_tab_2 is from role. But this privilege is not usable when it is to used in a DDL statement, creating a view here. So, it is insufficient privilege to select the table and then create the view.
SQL> create table tt as select * from test_user_2.test_tab_2;
Table created
What is interesting that we can create table successfully, something is different from what Oracle’s document says…. more research is required.
One more thing about the privilege.
Invoker's Rights Vs Definer's Right
The user test_user_1 created a package test_package, then test_user_1 is the definer.
Other user who invoker this package is called Invoker, for example test_user_2.
When test_user_2 execute the procedure in package test_package, whether the procedure can be executed successfully without having privilege problem is depending on whether test_user_1’s right, the definer’s right, but not test_user_2’s. But of course test_user_2 should be granted the privilege to execute the package first.
But if the package is created with AUTHID Clause, the oracle would check the invoker’s right but not definer’s.
收藏:
QQ书签
del.icio.us
订阅:
Google
抓虾
