[TRAFODION-1276] LP Bug: 1465782 - Revoke privilege return dependent grant ERROR[1037]

• Type: Wish
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Component/s: sql-security
• External issue URL: https://bugs.launchpad.net/bugs/1465782
• Assignee: Roberta Marton
• Reporter: Paul Low
• Created: 22/Jul/15 18:20
• Updated: 06/Sep/18 15:36

Description

The DB_ROOT user is unable to revoke privilege granted to user after user grants privilege to another user. In the scenario below, the user is granted privilege with grant option. The user grants the privilege to another user. DB_ROOT is unable to revoke the original privilege and the WGO privilege that he first granted to the user. ERROR[1037] is returned.

Daily build: 20150613
Security is enabled.

SQL>create schema schema1;

— SQL operation complete.

SQL>set schema schema1;

— SQL operation complete.

SQL>create table tab1(a int, b int) no partition;

— SQL operation complete.

SQL>grant select on tab1 to username1 with grant option;

— SQL operation complete.

SQL>connect username1/password1;
Connected to Trafodion

SQL>set schema schema1;

— SQL operation complete.

SQL>grant select on tab1 to username2;

— SQL operation complete.

SQL>connect db_root_username/db_root_password;
Connected to Trafodion

SQL>set schema schema1;

— SQL operation complete.

SQL>revoke grant option for select on tab1 from username1;

• • • ERROR[1037] Revoke failed because of a dependent grant between authorization ID USERNAME1 and authorization ID USERNAME2

SQL>revoke grant option for select on tab1 from username1 cascade;

• • • ERROR[1037] Revoke failed because of a dependent grant between authorization ID USERNAME1 and authorization ID USERNAME2

SQL>revoke select on tab1 from username1 cascade;

• • • ERROR[1037] Revoke failed because of a dependent grant between authorization ID USERNAME1 and authorization ID USERNAME2

SQL>drop schema schema1 cascade;
— SQL operation complete.