OraFAQ Forum: Security » revoke create table does not work for dba

Home » RDBMS Server » Security » revoke create table does not work for dba - Urgent

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

revoke create table does not work for dba - Urgent [message #38236]

Wed, 03 April 2002 11:16

Murali Krishna
Messages: 9
Registered: April 2002

Junior Member

hi
i connected to oracle as a dba.I revoked the create table privilige and create any table privilige for the dba role as well as from the user. but i was still able to create tables on the user schema.
how does roles and priviliges work ?

Report message to a moderator

Re: revoke create table does not work for dba - Urgent [message #38251 is a reply to message #38236]

Thu, 04 April 2002 09:18

andrew again
Messages: 2577
Registered: March 2000

Senior Member

"create ANY table" is primarily to allow table creating in another schema. See if you can revoke "create table". You could also assign a TS quota of 0 on tablespaces (if the schema is empty). You could also create a schema level trigger to raise_appliaction_error on table creation etc.

Report message to a moderator

Re: revoke create table does not work for dba - Urgent [message #38253 is a reply to message #38236]

Thu, 04 April 2002 10:18

Grant
Messages: 578
Registered: January 2002

Senior Member

create table is also granted with other roles like the CONNECT and RESOURCE role. If you want to limit a user you should create your own role and grant only what privilege you want them to have and then grant that role to the user. Here is what you get with CONNECT and RESOURCE:

Roles granted to user

GRANTED_ROLE ADM DEF
-------------------- --- ---
CONNECT NO YES
RESOURCE NO YES

Table Privileges granted to a user through roles

no rows selected

System Privileges assigned to a user through roles

GRANTED_ROLE PRIVILEGE
-------------------- ---------------------------
CONNECT CREATE VIEW
CONNECT CREATE TABLE
CONNECT CREATE SYNONYM
CONNECT CREATE SESSION
CONNECT CREATE SEQUENCE
CONNECT CREATE DATABASE LINK
CONNECT CREATE CLUSTER
CONNECT ALTER SESSION
RESOURCE CREATE TYPE
RESOURCE CREATE TRIGGER
RESOURCE CREATE TABLE
RESOURCE CREATE SEQUENCE
RESOURCE CREATE PROCEDURE
RESOURCE CREATE OPERATOR
RESOURCE CREATE INDEXTYPE
RESOURCE CREATE CLUSTER

16 rows selected.

Table privileges assigned directly to a user

no rows selected

System privileges assigned directly to a user

PRIVILEGE ADM
--------------------------- ---
UNLIMITED TABLESPACE NO

Note that UNLIMITED TABLESPACE is given through the RESOURCE role but is assigned directly by the system. As you can see privileges are granted through multiple roles.

Report message to a moderator

Re: revoke create table does not work for dba - Urgent [message #38255 is a reply to message #38236]

Thu, 04 April 2002 11:07

Murali
Messages: 54
Registered: December 1999

Member

hi
thanks for the response.i am bit querious to know why a dba user is still able to create table even though
both the "create table" and "create any table" privilige is revoked from a dba. what factor overides my revoke privlige when it comes to a dba.
murali

Report message to a moderator

Previous Topic:	Security scripts?
Next Topic:	Oracle audit trail

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jul 04 16:53:26 CDT 2026