Skip to Main Content
  • Questions
  • How to preventing front-end users accessing the db via sqlplus , etc


Question and Answer

Tom Kyte

Thanks for the question, Iain.

Asked: April 20, 2003 - 9:37 am UTC

Last updated: October 21, 2008 - 11:20 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

I have read the your comments on this issue. What do you think of this idea? When the user logs on to the front-end the code assigns a password-protected role to the user allowing access to the application tables. If the user logs on via odbc or sqlplus this role is not assigned therefore preventing unauthorised access.


Iain Blacklaw

and Tom said...

it would take me less then 5 seconds to get this password or figure out how to get the role enabled. It is trivial. Security via obscurity. A simple sqlnet trace and I'm in.

You can achieve this degree of security using secure application roles and ntier proxy authentication in 9i easily -- before that, it was all security via "i hope they aren't savvy enough to be able to figure it out"


  (34 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Implementing this via trigger in 8.1.7

ahmad imran, April 20, 2003 - 9:59 pm UTC

I am also looking for this that how it is possible that i restrict the user to access the database through software which is in dev 6i. I put almost every check to there in 6i. But I am still confusing that if log on through sql plus he can damage the database.

Although I create a self password form for athentication, it works fine and i am not giving the user the original db password but any user who have dba privilage(like system) can access the user 's scheema through aqlplus or other softwareeven he can delete it ...

Now how can I prevent it (triggers or not???)

Tom Kyte
April 21, 2003 - 7:09 am UTC

you cannot. If you are concerned about people with DBA privs doing bad things, you have a waaayyy bigger fish to fry. think about it -- if you cannot trust the DBA account....

A reader, April 21, 2003 - 12:14 pm UTC

The true, at least in NT, if you have access to server, you have access to the database,

To give some security using form I do the following
1) basic role, this role has the minimum to connect and enable a role.
2) applicatoin role, with password, it must be enabled.
3) schema users, that users are "locked", to connect between database I user other users.

Tom Kyte
April 21, 2003 - 12:36 pm UTC

#2 is easy to subvert as I said. so, beware. knowledge being power here -- knowing that you do not have a 100% secure solution is better then not knowing.

NT, Unix, OS/390 -- doesn't matter.

How about product_user_profile table

Arup Nanda, April 21, 2003 - 1:58 pm UTC


To prevent people from connecting via sql*plus, can't we use the product_user_profile table to enforce that? Or I am missing something?


Tom Kyte
April 21, 2003 - 2:16 pm UTC


now what.

Chuck Jolley, April 21, 2003 - 2:59 pm UTC

What do you think of password mangling?
When a user logs into our app, before they are logged into the database, the app logs into the database as a user with access to only one package.
The package is used to create a hash out of the username and password that the user entered.
The app then logs the user in using their username and the hashed password.
The users therefore do no know their own passwords and can't log in using other tools.
Of course, if they get hold of the app password and figure out the algorithm, they can log in as the app, get the hash, then log in as themselves.
Not genius proof, but at least they can't just stumble in.

Tom Kyte
April 21, 2003 - 3:31 pm UTC

it'll take me no time at all to get the hashed password. the hashed password comes back to the client app - a sqlnet trace will put that in a flat file for me. it is the same problem as above.

and you now have a hard coded password in the client application that you cannot change easily -- even when it is compromised.

Chuck Jolley, April 21, 2003 - 3:43 pm UTC

OK "i
hope they aren't savvy enough to be able to figure it out"
it is untill we get on 9i. ;)

How does "secure application roles and ntier
proxy authentication" prevent a savvy front-end user from using sqltrace and a debugger to open it up?

Tom Kyte
April 21, 2003 - 10:30 pm UTC

secure application roles allow a stored procedure to enable a role.

Ntier proxy authentication allows a middle tier to log on WITH ITS credentials on your behalf....

so, now, a stored procedure can say:

if TOM_KYTE is the real user
enable role
end if

TOM_KYTE identifies himself to the middle tier using username/password, x509 PKI, whatever...

the middle tier logs in using ITS credentials on TOM_KYTE's behalf.

You know 2 things - real user = TOM_KYTE, proxy_user = HR. The *only* way the proxy user can be HR is if the middle tier HR application logged in.

Nothing is happening on the client pc. the only way to log in as hr is via the hr app in the middle tier.

How should the n-tier (such as Tomcat) authenticate itself?

Jim Nasby, March 02, 2004 - 5:23 pm UTC

Tom, what would you recommend for having the middle-ware authenticate itself? My java coworkers are telling me that standard practice is to store the database password in a config file on the server, but it seems this is as risky as storing a role password in a config file on the server.

Tom Kyte
March 02, 2004 - 7:20 pm UTC

me, i don't like having middle tiers authenticate themselves. they are just software :)

they'll need to encrypt that password, and then they need to worry about how to secure the key, and keep the passwords in sync....

sorry -- don't have a "really good answer"

Jim Nasby, March 03, 2004 - 3:49 pm UTC

Hrm, maybe I should describe what I've been planning to do...

Rather than create our own system for managing user logins for our webapp, I'm giving users accounts in oracle, and various application roles (right now we just have an admin and a user role). Admins need to be able to add users, so they have alter, create, and drop user granted to that role.

But I don't want admins to be able to arbitrarily connect to the database via any tool and start dropping users at will; I only want them to have the admin role when logged in via our application. It seems the best way to do this is to login via a proxy account and have a stored proc verify that before allowing the user to enable the admin role.

I'm using user administration as the example here, but there's plenty of other things this will extend to; I'd like to use VPD features to limit visibility to certain things based on what role a user has, for example.

Ideally, the webapp would just pass the user's credentials on to Oracle and let it decide if the connection is to be allowed or not, but then it seems that there's no way to limit user's ability to activate these roles that they should only be using via our application. Of course n-tier proxy auth doesn't allow for that, but our application could at least attempt to connect to oracle as the user before it re-connects as itself and proxies in as the user.

I hope this makes sense... Is there a better way to limit access to the application roles that doesn't involve n-tier authentication? Or am I heading down the wrong road entirely?

Tom Kyte
March 03, 2004 - 4:33 pm UTC

Or put the application logic in the database -- in the form of a stored procedure so that your application to add/drop users becomes nothing more than lipstick to make this application look prettier than SQLPlus does.

In that fashion, they are ALWAYS using your application -- they are granted the ability to run a stored procedure, stored procedure decides if they should be able to do the operation based on whatever logic and then does it. the users need not have alter any user or anything like that -- only the OWNER of the procedure would.

In this fashion, you can lock it down as tight as you like.

Also, this was a typo right?

"Of course n-tier proxy auth doesn't allow for that,"

that is exactly what ntier supports! You can use an application role that can only be enabled by the ntier account.

Jim Nasby, March 03, 2004 - 5:42 pm UTC

Sorry, that paragraph is most unclear. What I was trying to say is that I'd like to use the n-tier feature of using a procedure to validate that the user can access the admin role, but also use the user's actual credentials to login to the database. Is there a way to login to the database via the application using the user's credentials *and* ensure that they can only activate these roles if they login via the application?

Yes, I've thought about using AUTHID DEFINER for doing these things, but it looks like that might be a fair amount of extra work with how things are structured right now. I'm using Oracle Objects for most of the API to the database, and I've implemented methods in the objects to handle the corresponding DML that would be needed to store the information in the actual tables (I'm not storing objects in tables). Unfortunately, there doesn't appear to be any way to grant permissions to individual methods of objects; either you can do everything in the object or nothing. Because of this, I'm granting permission to execute DML on the base tables themselves, based on the different roles. Also, I want to be able to use VPD features to limit what a user can see, and my understanding is that the only way to do that is if procedures either do all the checking themselves (sounds like extra work and a source for bugs), or they run as the current user and let the VPD stuff handle everything.

So, while I can change my user management code to handle permissions on it's own, I certainly don't want to be doing that for everything else. Granted, at least with a VPD even if someone did get access to the tables outside the application and could run arbitrary DML, they'd only be able to modify their own stuff. But I'd still feel better if they were forced to do all DML through the API I've defined, even though I use RI, constraints, triggers, etc. to enforce rules wherever I can.


Is there any way to have n-tier auth require the end-user's credentials as well as the middleware's credentials?

Or, is there another way to secure the application roles that doesn't require n-tier auth? Maybe storing role passwords in a table that only the middleware has access to (I know... yuck)?

Thanks so much for your time!

Tom Kyte
March 04, 2004 - 7:47 am UTC

"Is there a way to login to the database via the application using
the user's credentials *and* ensure that they can only activate these roles if
they login via the application?"

Well, not really, maybe. The way N-Tier would ensure this is:

a) application logs in using credentials only IT knows (eg: user=APP, pw=secret)
b) application says "it is really bob" (eg: user=BOB)
c) your procedure that enables the role would say "is it really APP logged in on behalf of BOB - if yes, then enable role, else do not"

Now, if BOB logs in directly -- what can your procedure ask? It can ask "hey, is this BOB".

It can also ask "what is the IP address of the client" (app server). If you make it so bob cannot log into the app server machine, that might work. Procedure would say "if user=bob AND ip_addres in (set of allowed) then ok"

It can also ask "what is the name of the program that is connecting to me (in v$ tables)". This however is easily spoofed.

What About Digital Certificates

A reader, March 03, 2004 - 5:58 pm UTC


Thanks for your valuable response as usual.

Please comment on the role of digital certificates (dc)installed on the client desktop, in addition to other methods of authentication?

Is a dc relevant/applicable at all? Would it be easily setup, stolen etc?


Tom Kyte
March 04, 2004 - 7:48 am UTC

PKI authentication is very strong and pretty easy for end users. It takes something you have (the cert) and somethig you know (a password to enable the cert).

If you can use it, if it fits into your architecture, its a pretty strong choice.

Application-based Security

John Gilmore, March 04, 2004 - 5:32 am UTC

Hi Tom,

We have a Forms application which currently has all privileges (select, insert, update, delete) granted to public. All security is controlled within the application by enabling and disabling menu selections etc.

My task is to implement some form of security within this mess, however they are not prepared to go down the FGAC path due to the amount of work involved.

The best I can come up with is to use password-protected application roles which would be enabled with a call to set_role within a Forms startup trigger. I know it's not totally secure due to the issues outlined above but is there anything further that can be done given their reluctance to implement FGAC?

Thaks for your help.

Tom Kyte
March 04, 2004 - 12:32 pm UTC

"All security is controlled within the application "

hah -- that's funny almost (if it weren't so bad) there is no security here......

the password protected role is as good as anything else (it is really hard to design in security after the fact -- imagine if cars did not come with locks on them, what strange things would people do to secure their cars -- maybe a big lock and chain wrapped around the car would work...).

How to preventing front-end users accessing the db via sqlplus

Iain Blacklaw, March 05, 2004 - 4:13 am UTC

We overcame the problem by encrypting the application password. I.e. all application passwords were initially reset to match the username but encrypted. When the application user next logs on they are forced to change their password. This password will only be recognised by the application. If the user tries to log on via sqlplus, toad, etc with their application username and password the connection will fail.

Another Idea ...

John Gilmore, March 05, 2004 - 4:59 am UTC

The application runs on a dedicated server and someone has suggested checking the logon server in a global database trigger, if it's the server which runs the application then let them log in otherwise refuse the login attempt. Do you see any problems with this approach?

Tom Kyte
March 05, 2004 - 8:04 am UTC

see above:

</code> <code>

that was one of the suggestions -- look at who is logging in and the machine (app server machine) being used.

Ask Tom

David Shink, March 08, 2004 - 6:05 pm UTC

The Q & A gave me a good idea of what can and cannot be done, and how effective the things that can be done are.
I would certainly recomment this. It was more helpful than just searching on Metalink.

preventing from ODBC/Toad etc

Suhail, July 09, 2004 - 11:47 am UTC


We have similar situation and would like to prevent non-IT user accessing Database directly from sqlplus/Toad/ODBC. In your answer to this thread you mentioned use of secure application role,
---> from your answer---
so, now, a stored procedure can say:

if TOM_KYTE is the real user
enable role
end if

TOM_KYTE identifies himself to the middle tier using username/password, x509
PKI, whatever...

We have almost 30000 database users outof which we have 500 IT users, we donot want to restrict IT user from accessing db via ODBC/Toad/SQLplus. Does this mean I have to write 30000 IF then statement?

We are also planning to install OID, would you recommend us what is the best way to move from database user to OID based users?

Tom Kyte
July 09, 2004 - 1:45 pm UTC

if the real user exists in this table over and
the proxy user is HR
enable role
end if

use a lookup table.

If you want to explore the various "user" configurations you can have - I know there is a new book out there that goes over this:

</code> <code>

it explains the in's and out's of each "type" of user account. there is no way to really "move" -- you just create and manage the accounts in different places.

Interesting Thread

Robert, July 29, 2004 - 5:43 pm UTC


How does Oracle Applications handle this?
Doesn't Oracle Apps use the APPLSYSPUB user somehow to establish privs for each particular user?



Tom Kyte
July 30, 2004 - 7:00 am UTC

much of apps uses a "create a view per role" approach. similar to fine grained access control but using "view explosion". It uses a mix of many approaches really.

What about this approach?

Robert, July 30, 2004 - 8:53 am UTC


Custom Forms 6i client/server application.

Based on the understanding that in 8i there is no air-tight way to separate 'application' security from 'sqlplus' security...

Would you please comment on this approach?

1) Users must have update, etc. privs on the application's tables (no way around this!)
2) Turn on auditing for the application's tables (This keeps everyone 'honest')
3) Export application's schema daily (This provides some help against 'mistakes').

Isn't this about as good as we can get?
What do you think?



Tom Kyte
July 30, 2004 - 4:48 pm UTC

1) or execute on stored procedures.

2) sure.

3) no, proper backups do that. export is not a good backup tool

How use backups to repair lost rows in a table?

Robert, July 30, 2004 - 4:59 pm UTC


The export would be in case someone accidentally deleted half a table by mistake.
With an export you might be able to reconstruct the table.

How would you use a hot backup to reconstruct a single table?



Tom Kyte
July 30, 2004 - 6:18 pm UTC

with a hot backup you have tablespace point in time recovery -- yes.

a competent dba can use a hot backup to restore ANY piece or slice of data in the database. (they would restore a mini instance, just system, the tablespace they need and rollback and grab whatever they needed out of it)

exports are not good backup devices.

and if you are using 10g, tons of flashback capabilities to recover from these whoops operations - everything from flashing back a table (put the table back the way it was 10 minutes ago) to undropping an accidently dropped table to putting the entire database back the way it was N minutes ago and more.

I Never Knew This!!!

Robert, August 01, 2004 - 9:53 pm UTC


a competent dba can use a hot backup to restore ANY piece or slice of data in the database. (they would restore a mini instance, just system, the tablespace they need and rollback and grab whatever they needed out of it)

This is terrific info!!!
I will definitely be practicing this in my 'sand-box' databases!!

Thank you, sir!


Tom Kyte
August 02, 2004 - 7:36 am UTC

look into TSPITR -- tablespace point in time recovery. the technique used there is what I described.

Weak Security

John Gilmore, August 20, 2004 - 5:15 am UTC

If all you want to do is restrict users to performing pre-defined actions, and perhaps having an audit trail of these actions, you can have all updates to tables trapped by Table API triggers. The code defined for these actions within the triggers then restricts what users can do.

Is this a valid, albeit limited, form of security and how hard is it to work around it?

Tom Kyte
August 20, 2004 - 11:15 am UTC

not sure what you are saying exactly?

but security enforced in an application is trivial to bypass by going around the application and right after the data.

the further from the data the security, the less secure the data.

A reader, February 21, 2005 - 12:19 pm UTC

Secure Application Role not supported in SE

houman, February 21, 2005 - 12:23 pm UTC

I tried to create Secure Application Role in our database guess what: Feature not enabled! It is an standard edition database. This is like saying to small and mid size businesses that you don't need proper security.

Tom Kyte
February 21, 2005 - 1:03 pm UTC

there are thousands of security features in the database, the secure application role is for n-tier proxy authentication, something you need with thousands or more users in order to get end to end identity.

There are thousands of security features, thousands of scaling features, online availability features.

and some of them are not in SE this is true. But that is what makes a "standard" vs "enterprise" edition isn't it?

SE doesn't have VPD, online index rebuilds, materialized view rewrite and so on </code> <code>
but that is precisely what sets SE apart from EE.

It is not at all like saying "you don't need proper security", SE is very secure, with tons of security features.

But it by definition doesn't have everything EE has.

How to preventing front-end users accessing the db via sqlplus

Iain Blacklaw, February 22, 2005 - 5:17 am UTC

Initially we ran a VB utility that resets the user password to an encrypted value of the user name. The next time the user logs on they enter their username as the password and are then forced to change their password to a new value.

Subsequently the user can only log on via the VB Front-end as this is only way that the password can be validated. Any attempts to log on using sqlplus, access, etc fail with incorrect password.

Changing the password using dba tools also fails, as the VB Front-end has not encrypted the new password. So when the user enters the new password access is again denied.

Tom Kyte
February 22, 2005 - 8:37 am UTC

ok, so -- tell me, how does the application authenticate this guy?

I mean, so I tell the VB app "hi, it is me tom"

does it just "trust me"?

Iain Blacklaw, February 22, 2005 - 9:24 am UTC

All users names are stored in an application table as well as oracle. The VB encrypts the incoming password then connects the user to oracle, which in turn checks the previous encrypted password.

E.g. user fred password bloggs. The VB checks that fred is a valid user by checking the application table and then encrypts the password bloggs into 12x45b. The user then connects to oracle, via the VB, and the password is checked. They match as the password was previously set after being encrypted by the VB. Therefore if fred tries to log on with an application other than the VB front-end bloggs is not encrypted and the passwords will not match.

Tom Kyte
February 22, 2005 - 9:54 am UTC

does user have ability to change password?

Iain Blacklaw, February 22, 2005 - 10:52 am UTC

The user can change their own password and a superuser can reset it. Previously when a user forgot their password it could be changed by the dba. This is no longer possible, as the password will not be encrypted. Password maintenance can only be done via the VB front-end or the VB utility previously mentioned.

Tom Kyte
February 22, 2005 - 11:36 am UTC

if the user can change the password, it would take me about 5 seconds to get the 'encrypted one' (i need *no* special privs at all)

anyone care to guess how? (literally -- 5 seconds or less)

Proxy users would be a much stronger method -- actually, killing anything that looks like client server with software on the desktop goes miles towards helping secure things.

Password intercepting

Gary, February 22, 2005 - 7:43 pm UTC

It's not hard to guess when the answer (sqlnet tracing) is already on your site :


Also, for windozey people, there's SQL Monitor which does pretty much the same thing, but is more point-and-click. <code>

Tom Kyte
February 23, 2005 - 1:57 am UTC

just trying to show this is "easy" to get around.

Proxy authentication with a locked down middle tier is the only sure fire way I know to do this (make it so my application is the only way to access the data)

My 2.5 cents...

Kashif, March 24, 2005 - 4:09 pm UTC

If I understand the requirement properly, the poster basically wants the ability to prevent users from accessing the database from SQL Plus etc., and only allow access via the front-end app. If that's it then how about an after logon system trigger that checks to see if you have permission to access the database via that particular application? For example, create a table (lets call it APPS_ALLOWED) that stores the userids and the applications using which the user can access the database. This table will need to be stored in a secure account such as the DBA's accout. Next, create an after logon trigger (which is fired right after you log in). In this trigger, check to see whether the program the person used to log in (found in v$session) is in the list of authorized applications stored in the APPS_ALLOWED table. If it is, welcome to the database, if not, raise an exception/error. Because the after logon trigger fails, the person's session is aborted and he/she is not allowed inside the database. I've seen this done in some applications although I'm curious to know your thoughts, Tom, about the use of such a technique to prevent access from unauthorized apps. Thanks.


Tom Kyte
March 24, 2005 - 4:25 pm UTC

$ cp sqlplus ./allowed_program
$ ./allowed_program

unless you use a middle tier, and ntier proxy authentication, it can be worked around.


Marcio Portes, August 24, 2006 - 11:56 am UTC

What about that? Even if I change the sqlplus name to xpls, I guess module will be always 'SQL*Plus'.

--------------- ------------------------- ------------------
OPS$MPORTES xpls@lath09 (TNS V1-V3) SQL*Plus

Could we code something to check it out and kick them off? Right?

Tom Kyte
August 27, 2006 - 8:00 pm UTC

sqlplus is calling dbms-application-info and setting the module, the action and the client.

i can, from sqlplus just call dbms_application_info myself.

ops$tkyte%ORA10GR2> select module from v$session where sid = (select sid from v$mystat where rownum=1);


ops$tkyte%ORA10GR2> exec dbms_application_info.set_module('fooled','you');

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select module from v$session where sid = (select sid from v$mystat where rownum=1);



Marcio Portes, August 27, 2006 - 9:37 pm UTC

Great! Thank you.

how to change password?

A reader, September 14, 2006 - 1:51 pm UTC

I have heard changing the password for APPLSYSPUB user on Oracle 11i is not so easy. Do you know how to achive this?


Tom Kyte
September 14, 2006 - 1:55 pm UTC

please utilize support or an Oracle applications forum for assistance with Oracle applications (sort of outside of my realm here)

Is this more secure?

laurent, July 15, 2008 - 9:48 am UTC

Hi Tom,

What would you think of this approach using secure roles and proxy users :

conn / as sysdba

-- This user will own application objects.
create user app_owner identified by app_owner default tablespace ORA temporary tablespace TMP;
grant connect,resource to app_owner;
grant create procedure, create role, create session, drop any procedure, drop any role, select any dictionary to app_owner;

create user app_user identified by app_user default tablespace ORA temporary tablespace TMP;
alter user app_user default role none;

-- end_user does not need to have a valid password
create user end_user identified by values 'NO_PASSWORD' default tablespace ORA temporary tablespace TMP;
grant create session to end_user;
alter user end_user grant connect through app_user ;

conn app_owner/app_owner

create role secure_role identified using secure_role_package;
grant secure_role to app_user;

create or replace package secure_role_package authid current_user
  procedure check_and_activate_my_role;

create or replace package body secure_role_package
  procedure check_and_activate_my_role
    v_ipaddress varchar2(15);
    v_real_user varchar2(50);
    v_proxyuser varchar2(50);
    v_client_id varchar2(50);

    if v_ipaddress='' -- IP address of the app server
       and v_real_user='END_USER'
       and v_proxyuser='APP_USER'
       and v_client_id='cVat5h1Zm$z9x8OZZtYj#7fYEk7mdX1NaTdJ9s24UQMIBUSOme' -- Only the client app knows that passphrase 
      raise_application_error( -20001, 'Pas de bras, pas de chocolat!' );
    end if;

grant execute on secure_role_package to end_user;

create table my_tab1 (col1 number, col2 varchar2(10));
insert into my_tab1 values (1,'aaaaaaaaaa');
insert into my_tab1 values (2,'bbbbbbbbbb');
insert into my_tab1 values (3,'cccccccccc');

grant select,insert,delete,update on my_tab1 to secure_role;

-- can connect only through proxy user on machine
connect app_user[end_user]/app_user@my_base
exec dbms_session.set_identifier('cVat5h1Zm$z9x8OZZtYj#7fYEk7mdX1NaTdJ9s24UQMIBUSOme');
exec app_owner.secure_role_package.check_and_activate_my_role;
select * from app_owner.my_tab1;

-- you cannot connect directly as app_user.
SQL> conn app_user/app_user@my_base
ORA-01045: user APP_USER lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

-- you cannot login as end_user either because there is no valid password.

Tom Kyte
July 15, 2008 - 7:54 pm UTC

grant connect, resource - don't do that.

and v_client_id='cVat5h1Zm$z9x8OZZtYj#7fYEk7mdX1NaTdJ9s24UQMIBUSOme' --
Only the client app knows that passphrase

- don't rely on that. It is right there, many people know that catchphrase. But it is probably OK - I don't see why you need it anyway.

otherwise - sure, you are ensuring that only the app_user, proxying to the end_user, can activate the role and only from the right machine(s)

Does APP_OWNER require those privileges at all?

Max, October 16, 2008 - 8:49 am UTC

For example: One could have created app_owner's objects without being logged on as sys_owner ...
Tom Kyte
October 17, 2008 - 8:57 pm UTC

require what privileges?

I said "do not use connect, resource", but what privileges beyond that are you talking about

(I'm not a fan of created app_owers objects 'as someone else', that is really confusing stuff... I'd rather create the app owner with the barest minimum set of privileges needed to create their stuff - sort of documents what privileges they actually NEED to accomplish their job, any super user creating the stuff for them will obfuscate this all - I want to know what privileges are needed for that account and to be able to say "why" they are needed. So do auditors mostly....)

Re: grant connect, resource - don't do that.

Stew Ashton, October 18, 2008 - 9:10 am UTC

Tom, you say above "do not use connect, resource" but you do not give much detail about "why".

1) Could it be because these are generic roles?

In this case if you want app_owner to create its own stuff, app_owner would need "create session" privileges, right?

2) I must admit I have been creating app_owners objects 'as someone else'. I was trying to distinguish between rights needed to install (which go to the super user) and rights needed to execute (which app_owner doesn't need at all). I do see your point and will rethink. As a side benefit, it will be easier to see app_owner objects in SQL Developer if I can log in directly as him.

Now suppose I do want to prevent anyone but the super user from effectively updating the app_owner schema? Would proxying app_owner through the super user accomplish that?
Tom Kyte
October 18, 2008 - 10:59 pm UTC

I believe in

a) least privileges
b) well documented privileges

in earlier releases, these generic roles - which we own and are subject to change - granted WAY too much (like unlimited tablespace with resource). In current releases - we changed that (which means, your old stuff might not work the same...)

So, do you own granting, create your own roles.

The super user in this case would be anyone with the ANY style privileges - again, the concept of LEAST privileges should dominate, so that users with the ANY privileges are few and far between....

and they are audited, fully.

Re: "and they are audited, fully"

Stew Ashton, October 19, 2008 - 3:54 am UTC

Much better answer than my question, thanks!

I generally have one super-duper user per instance with all possible privileges in order to avoid using SYS/SYSTEM. I gather from your answer that this guy should be used only to manage users, roles and grants, leaving object creation to the object owners.

Should SYS and/or SYSTEM be audited fully as well? Any performance drawbacks?
Tom Kyte
October 21, 2008 - 11:20 am UTC

... Should SYS and/or SYSTEM be audited fully as well? Any performance drawbacks? ...

absolutely and "so what if there were, you need to do that"

but in reality:

absolutely and "no, and besides - you do so few things as SYS AS SYSDBA and nothing really as SYS or SYSTEM"

Great stuff, thanks ! Will do.

Stew Ashton, October 21, 2008 - 3:40 pm UTC