The biggest issue for me is when this is done the security moves out one layer from the owner of the object; so becomes harder to control and understand the true scope of the security. Unfortunately the reason often this syntax is used is to allow the creation of a view in SCHEMA_A with access to a table in SCHEMA_B and then with the requirement to grant access to the view in SCHEMA_A to USER_C. This cannot be done unless the access to the table in SCHEMA_B is granted WITH GRANT OPTION; Lets see an example:
SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL>
SQL>
SQL> create user schema_a identified by schema_a;
User created.
SQL> grant create session, create view to schema_a;
Grant succeeded.
SQL> create user schema_b identified by schema_b;
User created.
SQL> grant create session, create table to schema_b;
Grant succeeded.
SQL> create user user_c identified by user_c;
User created.
SQL> grant create session to user_c;
Grant succeeded.
SQL> alter user schema_a quota unlimited on users;
User altered.
SQL> alter user schema_b quota unlimited on users;
User altered.
SQL>
Now connect as SCHEMA_B and create the table that SCHEMA_B will create a view on and grant SELECT to SCHEMA_A:
SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> create table test01 (col_01 number);
Table created.
SQL> insert into test01 values ('1');
1 row created.
SQL> insert into test01 values ('2');
1 row created.
SQL> commit;
Commit complete.
SQL> grant select on test01 to schema_a;
Grant succeeded.
SQL>
Now we have a table in SCHEMA_B connect as SCHEMA_A and create the view and test it:
SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> create view view01 as select * from schema_b.test01;
View created.
SQL> select * from view01;
COL_01
----------
1
2
SQL>
Now the final step; grant SELECT on the view to USER_C and test access to the data is SCHEMA_B.TEST01 via SCHEMA_A.VIEW01:
SQL> grant select on view01 to user_c;
grant select on view01 to user_c
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'SCHEMA_B.TEST01'
SQL>
This is where the problem lies; so in order to fix this we must instead grant select on SCHEMA_B.TEST01 to SCHEMA_A WITH GRANT OPTION and then it works:
SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on test01 to schema_a with grant option;
Grant succeeded.
SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on view01 to user_c;
Grant succeeded.
SQL>
So now it works and USER_C can access the data in SCHEMA_B.TEST01 via SCHEMA_A.VIEW01:
SQL> connect user_c/user_c@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> select * from schema_a.view01;
COL_01
----------
1
2
SQL>
So this is how to have tables in one schema and access views in another and then grant select on the tables accessing the views to a third user. The problem now is that the security on SCHEMA_B.TEST01 table is no longer just controlled by SCHEMA_B but SCHEMA_A can also now make grants on SCHEMA_Bs table. First connect as SYS and create some more users to become GRANTEEs:
SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> grant create session to ua identified by ua;
Grant succeeded.
SQL> grant create session to ub identified by ub;
Grant succeeded.
SQL> grant create session to uc identified by uc;
Grant succeeded.
SQL> grant create session to ud identified by ud;
Grant succeeded.
SQL>
Connect as SCHEMA_A (the grantee of the WITH GRANT OPTION) and pass on grants:
SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ua with grant option;
Grant succeeded.
SQL> grant select on schema_b.test01 to ub;
Grant succeeded.
SQL>
Now connect as user UA and grant WITH GRANT OPTION to user UC and then connect to user UC and make a grant to user UD:
SQL> connect ua/ua@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to uc with grant option;
Grant succeeded.
SQL> connect uc/uc@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ud with grant option;
Grant succeeded.
SQL>
OK, what a messy chain of grants that we have done starting as SCHEMA_A who was passed the right to GRANT on the privilege granted to it. Then UA can do the same and finally UC can also pass on the grant. So lets connect back as SYSDBA and check out the grants:
SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2
get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 16:00:51 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.
OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Testing root object => [SCHEMA_B.TEST01]
GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
SCHEMA_A UB X
UC UD G
UA UC G
SCHEMA_A UA G
SCHEMA_B SCHEMA_A G
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
This script shows the grants done above and shows the GRANTEE and GRANTOR and also whether the grant is normal "X" or is done WITH GRANT OPTION "G". What if you go full circle and log in as user UD and grant select back to SCHEMA_B WITH GRANT OPTION:
SQL> connect ud/ud@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to schema_b with grant option;
Grant succeeded.
SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2
get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 16:08:43 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.
OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Testing root object => [SCHEMA_B.TEST01]
GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
SCHEMA_A UB X
UC UD G
UA UC G
SCHEMA_A UA G
SCHEMA_B SCHEMA_A G
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
Nothing changed so even though the GRANT from UD to SCHEMA_B said "Grant Succeeded" that clearly was not true. So what next, Oracle says that if you REVOKE a GRANT done WITH GRANT it should unravel all of the child grants to ensure that its all cleaned up. So log in to SCHEMA_B and revoke the original grant to SCHEMA_A with the WITH GRANT OPTION and does it unroll the other grants?
SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> revoke select on test01 from schema_a;
Revoke succeeded.
SQL>
Lets check the grants that still exist; if any:
SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2
get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 16:35:31 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.
OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Testing root object => [SCHEMA_B.TEST01]
GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
All gone!!, very clever, Oracle even knew about all of the grants made by SCHEMA_A to other users and even the grants they then made to yet further users. As I like to know how things work; is there any clues as to how Oracle does the revokes? First lets put all of the grants WITH GRANT back again:
SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on test01 to schema_a with grant option;
Grant succeeded.
SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ua with grant option;
Grant succeeded.
SQL> grant select on schema_b.test01 to ub;
Grant succeeded.
SQL> connect ua/ua@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to uc with grant option;
Grant succeeded.
SQL> connect uc/uc@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ud with grant option;
Grant succeeded.
SQL>
So if we check DBA_TAB_PRIVS is there any special marker to help it revoke recursively; no:
SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @print 'select * from dba_tab_privs where table_name=''''TEST01'''''
Executing Query [select * from dba_tab_privs where table_name='TEST01']
GRANTEE : UA
OWNER : SCHEMA_B
TABLE_NAME : TEST01
GRANTOR : SCHEMA_A
PRIVILEGE : SELECT
GRANTABLE : YES
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
-------------------------------------------
GRANTEE : UB
OWNER : SCHEMA_B
TABLE_NAME : TEST01
GRANTOR : SCHEMA_A
PRIVILEGE : SELECT
GRANTABLE : NO
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
-------------------------------------------
GRANTEE : SCHEMA_A
OWNER : SCHEMA_B
TABLE_NAME : TEST01
GRANTOR : SCHEMA_B
PRIVILEGE : SELECT
GRANTABLE : YES
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
-------------------------------------------
GRANTEE : UC
OWNER : SCHEMA_B
TABLE_NAME : TEST01
GRANTOR : UA
PRIVILEGE : SELECT
GRANTABLE : YES
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
-------------------------------------------
GRANTEE : UD
OWNER : SCHEMA_B
TABLE_NAME : TEST01
GRANTOR : UC
PRIVILEGE : SELECT
GRANTABLE : YES
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
Oracle doesn't need it as it could do a recursive query on the base tables of this view and look for grantor and grantee relationships and where the GRANT was WITH GRANT or GRANTABLE in DBA_TAB_PRIVS. So this option when doing granular grants on objects such as tables is a mess; it would be better to avoid this. There are two main reasons that WITH GRANT gets used; the first is to solve the view problem I showed at the start of this post and the second where a user becomes a "security user" and gets all grants WITH GRANT so that he can log in and then make grants to other users.
I would say that I don't like both of these options. Number 1 [the VIEW issue] can be solved by creating the view in the same schema as the table so that the GRANT with WITH GRANT is not needed; in fact no grant is needed as the OBJECT OWNER principal takes over and requires no grants. The security in having the data in one schema and the views in another (presumably with a limited view set on the data) is ruined by having to give WITH GRANT to that schema on the base table anyway so whilst if you block that schema and SQL injection is not possible then its OK BUT the fact still remains that this schema can now control the security of another schema; I don't like that; I prefer the risk that the view and table are in the same schema and no grants WITH GRANT are needed and the only grants made are those that expose the VIEW not the table and these are normal grants.
The Number 2 issue - the security user/schema is not a good way to manage grants. There are multiple options in this case to make grants :
- Use a DBA %ANY% privilege to make grants
- Use SYSDBA to make grants
- Connect as the schema to make grants
- Create a granting schema (number 2 above) to make grants
- Connect to the schema using proxy and make grants
Personally I prefer the proxy approach as it allows access to the schema without needing to give out the schema password; We can also audit all actions as the proxy without affecting any schema level audit and we don't need to write schema.object in code. Using WITH GRANT simply increases the attack surface.
For extra credit; users can get the grants more than once:
SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on test01 to schema_a with grant option;
Grant succeeded.
SQL> grant select on test01 to ua;
Grant succeeded.
SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ua with grant option;
Grant succeeded.
SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2
get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 18:38:50 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.
OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Testing root object => [SCHEMA_B.TEST01]
GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
SCHEMA_B UA X
SCHEMA_A UA G
SCHEMA_B SCHEMA_A G
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
So the user UA has the SELECT twice once WITH GRANT and once not; so if we revoke the grant to SCHEMA_A it will also remove one of these grants:
SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> revoke select on test01 from schema_a;
Revoke succeeded.
SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2
get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 18:52:47 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.
OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Testing root object => [SCHEMA_B.TEST01]
GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
SCHEMA_B UA X
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
If we instead try a recursive grant WITH GRANT:
SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on test01 to schema_a with grant option;
Grant succeeded.
SQL> connect schema_a/schema_a@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to ua with grant option;
Grant succeeded.
SQL> connect ua/ua@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> grant select on schema_b.test01 to schema_a with grant option;
Grant succeeded.
SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2
get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 18:56:30 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.
OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Testing root object => [SCHEMA_B.TEST01]
GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
UA SCHEMA_A G
SCHEMA_A UA G
SCHEMA_B SCHEMA_A G
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
So we granted select from SCHEMA_B to SCHEMA_A and then from SCHEMA_A to UA and then from UA to SCHEMA_A. So SCHEMA_A has the grant twice but from two users but the WITH GRANT has come full circle. What happens when we revoke?
SQL> connect schema_b/schema_b@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> revoke select on test01 from schema_a;
Revoke succeeded.
SQL> connect sys/oracle1@//192.168.1.95:1539/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @get_tab2
get_tab2: Release 1.0.0.0.0 - Production on Thu Jun 07 19:03:35 2018
Copyright (c) 2004,2009, PeteFinnigan.com Limited. All rights reserved.
OBJECT TO CHECK [XXX_XXXX]: TEST01
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SCHEMA_B
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Testing root object => [SCHEMA_B.TEST01]
GRANTOR GRANTEE S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - -
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
No matter how convoluted the grants WITH GRANT get Oracle unravels them; this is not really the point; that Oracle can unravel them. The point is not to get into this mess in the first place having grants allowed from a second schema; this is then uncontrollable by the original schema and its an increased attack surface. Try not to use these.
