Home » RDBMS Server » Security » revoke create table does not work for dba - Urgent
revoke create table does not work for dba - Urgent [message #38236] Wed, 03 April 2002 11:16 Go to next message
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 ?
Re: revoke create table does not work for dba - Urgent [message #38251 is a reply to message #38236] Thu, 04 April 2002 09:18 Go to previous messageGo to next message
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.
Re: revoke create table does not work for dba - Urgent [message #38253 is a reply to message #38236] Thu, 04 April 2002 10:18 Go to previous messageGo to next message
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.
Re: revoke create table does not work for dba - Urgent [message #38255 is a reply to message #38236] Thu, 04 April 2002 11:07 Go to previous message
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
Previous Topic: Security scripts?
Next Topic: Oracle audit trail
Goto Forum:
  


Current Time: Thu May 26 16:06:46 CDT 2022