asked on

Create Extremely Priviliged Database User Schema Account

Need to create an Oracle database privileged database user account, but don't want this user to be able to drop database.

Sqlplus> create user InfSysAd IDENTIFIED BY xxx_XXX;

SqlPlus> grant connect, resource, DBA to SInfysAd;
SqlPlus> revoke drop database;

Is this good enough to all this new InfSysAd to create other database users, tables, etc?

SOLUTION

OMC2000

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Geert G

have you ever tried dropping a database not on the host ?
believe me, not evident

a user, by default, doesn't have that priv

slightwv (䄆 Netminder)

When you mention "drop database", are you thinking along the lines of SQL Server not Oracle?

This is over-simplified but in Oracle there is only one database per say. Oracle databases contain multiple schemas that own the core objects. If a privileged user drops a schema, all the objects in that schema are gone so the "database" likely doesn't matter any more. You can take out an entire payroll system by dropping one schema even though the database is still running fine.

Can you clarify exactly what you are hoping to achieve with the special user? I'm guessing you are probably after a custom trigger of some type that monitors what a user can and cannot do but without more detail requirements, it is hard to say for sure.