Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Not allowed to drop owned table

wunderdata
SQL-Fighter

HI,
I encountered some odd behavior on a client's environment:

My user was granted ALL PRIVILEGES on a sandbox schema, as well as create rights.
When I create objects in that schema, I somehow do now seem to own them though:


EXA_DB.SANDBOX> create table wtest (id integer)
[2021-12-10 01:50:40] completed in 277 ms
EXA_DB.SANDBOX> drop table wtest
[2021-12-10 01:50:40] [42500] insufficient privileges for dropping table (Session: 1718718221681098752)

Any Idea what I'm missing here? (Granting drop any table is not an option...)

1 ACCEPTED SOLUTION

Accepted Solutions

exa-Nico
Community Manager
Community Manager

@wunderdata Malte is correct here. The owner of the object is not the person who created it, but is the assigned owner of the schema in which it resides. If the schema is owned by SYS, then SYS is the owner of the table. There's also a slight difference in how this works using certain system privileges. 

A user who has the "CREATE TABLE" privilege can create tables in schemas they are the owner of. With this permission, DROP TABLE is an implicit permission because the owner of an object can drop the objects they own. 

This is not the case with the CREATE ANY TABLE privilege however. CREATE ANY TABLE lets you create a table anywhere in any schema. Sometimes this can result in weird constellations where you are able to create a table, but then aren't able to even see it after creating it (due to missing SELECT privileges). Similar to CREATE ANY TABLE, you would need the DROP ANY TABLE privilege to be able to drop any of the tables anywhere. These are really powerful permissions though. 

Probably the "cleanest" solution would be to:

  • create an admin role
  • assign the new role as an owner of the schema
  • grant the required privileges to this new admin role (ie CREATE TABLE, CREATE VIEW, etc) (think policy of least privileges)
  • Grant the admin role to your user, thereby inheriting the permissions and ownership.  (SYS does not need to be granted this because SYS can do everything anyway).

Keeping SYS as the owner of schemas is of course an option, but is very restrictive then as to what other users can do on a given schema. Maybe this is desired in your organization, but the workarounds with CREATE ANY TABLE or DROP ANY TABLE seem to contradict it. 

Hope that helps!

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

View solution in original post

9 REPLIES 9

exa-Kristof
Team Exasol
Team Exasol

Hi @wunderdata ,

can you please post which grants you executed? If you indeed executed "GRANT ALL PRIVILEGES" this should also include DROP ANY TABLE. 

But

  1. this is not what you wanted and
  2. you would then be able to drop the table. 😉 

Maybe you can additionally check which privileges the user actually has:

SELECT * FROM EXA_USER_OBJ_PRIVS;
SELECT * FROM EXA_USER_SYS_PRIVS;

 And for the sake of completeness please check the table owner with 

SELECT * FROM EXA_ALL_TABLES;

Best regards,

Kristof

wunderdata
SQL-Fighter

 

SELECT object_name,object_type,privilege,left(Grantee,2)||'*****' as grantee, left(GRANTOR,2)||'*****' as GRANTOR ,owner
FROM EXA_USER_OBJ_PRIVS where object_name = 'SANDBOX';

 

 

wunderdata_1-1639154637264.png

 

 

SELECT left(Grantee,2)||'*****' as grantee,privilege,admin_option FROM EXA_USER_SYS_PRIVS;

 

 

wunderdata_2-1639154756588.png

 

 

SELECT * FROM EXA_ALL_TABLES where table_name = 'WTEST';

 

 

TABLE_SCHEMATABLE_NAMETABLE_OWNERTABLE_OBJECT_IDTABLE_IS_VIRTUALTABLE_HAS_DISTRIBUTION_KEYTABLE_HAS_PARTITION_KEYTABLE_ROW_COUNTDELETE_PERCENTAGETABLE_COMMENT
SANDBOXWTESTSYS169414280192falsefalsefalse00.0NULL

mwellbro
Xpert

Hi @wunderdata ,

to quote from the documentation of DROP TABLE:

mwellbro_0-1639225747735.png

src: https://docs.exasol.com/sql/drop_table.htm

I see three conditions for DROP TABLE to be usable and none of those apply to your user it would seem.

1) No "DROP ANY TABLE"
2) You are not the owner ( since it´s SYS )
3) The table is not owned by a group/role you belong to ( since it belongs to SYS )

Or am I missing something here ?

Cheers,
Malte

wunderdata
SQL-Fighter

Hi @mwellbro ,

the question is: Why do I not own the table, after I just created it myself? It should not be owned by sys then, right?

mwellbro
Xpert

Hi @wunderdata ,

could it be that the schema you create "your" table in is owned by SYS ? I´ve had that constellation in the past I think - you´d have to ALTER SCHEMA ... CHANGE OWNER <yourself> ; before actually being the owner of tables created in the schema ( this is from memory, haven´t back-tested the answer right now ).

Cheers,
Malte

wunderdata
SQL-Fighter

Yeah but this makes no sense. The admin will surely want sys to keep ownership, If I get write access to a schema, and the right to create and fill tables there, I should also be able to delete them.

 

exa-Nico
Community Manager
Community Manager

@wunderdata Malte is correct here. The owner of the object is not the person who created it, but is the assigned owner of the schema in which it resides. If the schema is owned by SYS, then SYS is the owner of the table. There's also a slight difference in how this works using certain system privileges. 

A user who has the "CREATE TABLE" privilege can create tables in schemas they are the owner of. With this permission, DROP TABLE is an implicit permission because the owner of an object can drop the objects they own. 

This is not the case with the CREATE ANY TABLE privilege however. CREATE ANY TABLE lets you create a table anywhere in any schema. Sometimes this can result in weird constellations where you are able to create a table, but then aren't able to even see it after creating it (due to missing SELECT privileges). Similar to CREATE ANY TABLE, you would need the DROP ANY TABLE privilege to be able to drop any of the tables anywhere. These are really powerful permissions though. 

Probably the "cleanest" solution would be to:

  • create an admin role
  • assign the new role as an owner of the schema
  • grant the required privileges to this new admin role (ie CREATE TABLE, CREATE VIEW, etc) (think policy of least privileges)
  • Grant the admin role to your user, thereby inheriting the permissions and ownership.  (SYS does not need to be granted this because SYS can do everything anyway).

Keeping SYS as the owner of schemas is of course an option, but is very restrictive then as to what other users can do on a given schema. Maybe this is desired in your organization, but the workarounds with CREATE ANY TABLE or DROP ANY TABLE seem to contradict it. 

Hope that helps!

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

mwellbro
Xpert

Adding to what Nico already posted, from my point of view it does have merit to separate the ownership of a schema ( for DDL changes ) from users operating on the data ( DML changes ) - personally, I wouldn´t keep "general ownership" on SYS if I can avoid it.

Cheers,
Malte

wunderdata
SQL-Fighter

Thx Nico, already thought about a similar construct,too. That post clears up a lot though, I suggest to add this information to the documentation in the topic "user privileges", since  there it doesn't got into detail that much, and this also differs from other db systems. Especially the part of "create any table" not granting ownership to those tables.