USER ROLES

[shrik_m]

I have an user called X with create session privilege and I created a table in that user X by connecting as System user.

Now I connected to the user X, I tried to drop the table which is created. I am able to drop it.

The question is I have only the create session privilege and I don't have create table privilege. When I dont have the privilege of creating a table how am I able to drop the table...? What all the roles are given to create session system privilege by default.....?


thanks,
Srikanth

[brhynold]

Check dba_sys_privs and dba_role_privs to ensure that User X only has the create session privilege and has not been granted any other privilege or role such as DBA

[sln81]

May be that you have granted CONNECT role to the user X, not just the CREATE SESSION privilege. The CONNECT role contains CREATE TABLE privilege also.

[shrik_m]

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SYS MANAGE ANY QUEUE YES
SYS SELECT ANY SEQUENCE NO
SYS SELECT ANY TABLE YES
SYS UPDATE ANY TABLE NO
SYSTEM UNLIMITED TABLESPACE YES
TEST CREATE SESSION NO

when I queried select * from dba_sys_privs I got the above result.. what do we do now..?

Srikanth

[akkerend]

SELECT * FROM dba_role_privs;

Or connect as user X and try:
SELECT * FROM session_privs;

[sln81]

Hi,
I see it like this. The TEST user has only CREATE SESSION privilege. But you might have given him some quota on his default tablespace. SYSTEM user has CREATE ANY TABLE priv. So, as user SYSTEM you are able to create a table in TEST schema. Though SYSTEM has created that table, as it is under TEST schema, TEST is the owner of that and hence he is able to drop it. This is my view.

[akkerend]

sln81 is right. I just read the documentation again. If you are the owner of a table, you can drop it.

[shrik_m]

So as a developer who does not have any privilege of creating a table and with create session privilege only can drop a table. Something fishy...

[sreddy]

I guess system is an exceptional user as it has dba privs for this kinda behaviour. I don't think its true with every user like xyz creating a table in ABC schema and ABC could drop it.

Try as non-DBA user and dropping it and see what happens.

[akkerend]

The owner of a table and user with DROP ANY TABLE privilege can drop a table.