Home » RDBMS Server » Security » Restricting User access (Oracle 10g)
Restricting User access [message #479457] Sun, 17 October 2010 10:26 Go to next message
preet_kumar
Messages: 152
Registered: March 2007
Senior Member
The application user owns the application schema which owns all the database objects in this schema.
Now 50 of our developers need access to this application schema but giving away the password for the application user is risk as if anyone can issue "Drop user application cascade" and wipe off all the objects.
The option i have is to create 50 separate users with tablespace as application and grant all rights to application schema.
Could anyone suggest better option in such a scenario.

Thanks
Re: Restricting User access [message #479458 is a reply to message #479457] Sun, 17 October 2010 10:30 Go to previous messageGo to next message
BlackSwan
Messages: 21944
Registered: January 2009
Senior Member
>Now 50 of our developers need access to this application schema
In which environment; DEV, QA, PROD?
EXACTLY what does this mean?
How will end users "access this application"?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Restricting User access [message #479464 is a reply to message #479457] Sun, 17 October 2010 12:31 Go to previous messageGo to next message
preet_kumar
Messages: 152
Registered: March 2007
Senior Member
To make it more clear.

This is a testing environment and database is 10.2.0.2, OS is Linux

Database user is "apps" which owns all the database objects (tables,indexes etc)
Now all 50 users connect to the database using sql workbench or any other tool with this single apps user and do all DML and DDL activity.
This means any user can login as apps and issue the drop user cascade command which will delete everything.
Re: Restricting User access [message #479467 is a reply to message #479464] Sun, 17 October 2010 12:42 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ No one but appliocation administrator (and DBA) should have access to application owner account.
2/ Each developer should have an account
3/ Oracle roles should be defined and created with privileges appropriate for functional roles
4/ Each user account should be granted one or several dependings on the tasks they have to do

If developer have to do DDL for their tasks then they should have the whole objects in their schema (and then each developer will develop with possibly different objects which may lead to problem when integrating all modifications).

Regards
Michel

[Updated on: Sun, 17 October 2010 15:09]

Report message to a moderator

Re: Restricting User access [message #479476 is a reply to message #479457] Sun, 17 October 2010 14:27 Go to previous messageGo to next message
preet_kumar
Messages: 152
Registered: March 2007
Senior Member
Thanks Michel for your response.

Yes i do agree all 50 developers should not have access to the application schema but as per our requirement all the developers need to run DML and DDL statements.
The possibility is to create individual user account and enable auditing.So i create users and assign default tablespace as application which will be the default tablespace for all 50 users.
Then grant roles to all users to access objects on apps schema.
Now the user has to suffix apps schema whenever using any statement and otherwise the objects gets created in users schema.
Re: Restricting User access [message #479477 is a reply to message #479476] Sun, 17 October 2010 14:39 Go to previous messageGo to next message
BlackSwan
Messages: 21944
Registered: January 2009
Senior Member
>i do agree all 50 developers should not have access to the application schema but as per our requirement all the developers need to run DML and DDL statements.
developers CAN issue DDL & DML against their own schema; which can have all the same objects as exist within the application
Re: Restricting User access [message #479482 is a reply to message #479464] Sun, 17 October 2010 20:58 Go to previous messageGo to next message
flyboy
Messages: 1750
Registered: November 2006
Senior Member
preet_kumar wrote on Sun, 17 October 2010 19:31
This means any user can login as apps and issue the drop user cascade command which will delete everything.
This is true under two conditions:
- the user has DROP USER privilege
- the user DROPs different user than the one he is logged in

Just follow the demonstration (you may try it yourself with different user name, use two sessions for convenience).

Log in as user with CREATE USER privilege (e.g. SYSTEM):
SQL> create user apps identified by apps;

User created.

SQL> grant create session to apps;

Grant succeeded.

Now login as APPS:
SQL> drop user apps cascade;
drop user apps cascade
*
ERROR at line 1:
ORA-01031: insufficient privileges

OK, give APPS the DROP USER privilege, logged in as user with DROP USER WITH ADMIN OPTION privilege (e.g. SYSTEM user again):
SQL> grant drop user to apps;

Grant succeeded.

Now login as APPS:
SQL> drop user apps cascade;
drop user apps cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected


But, if the user may issue DDL against the schema, he may DROP all its objects one by one. Just curious: would you like to block the user from dropping the object (e.g. table) he created in that schema by mistake? What about dropping column from the table?
Re: Restricting User access [message #479644 is a reply to message #479457] Mon, 18 October 2010 10:15 Go to previous messageGo to next message
preet_kumar
Messages: 152
Registered: March 2007
Senior Member
Thanks a lot for the detailed explanation...
Re: Restricting User access [message #485806 is a reply to message #479482] Fri, 10 December 2010 01:36 Go to previous messageGo to next message
mikailhs
Messages: 1
Registered: December 2010
Location: Baku, Azerbaijan
Junior Member
Hi, If user has privilege "DROP USER", but without ADMIN OPTION, what it would give?
Re: Restricting User access [message #485807 is a reply to message #485806] Fri, 10 December 2010 01:46 Go to previous message
flyboy
Messages: 1750
Registered: November 2006
Senior Member
mikailhs wrote on Fri, 10 December 2010 08:36
Hi, If user has privilege "DROP USER", but without ADMIN OPTION, what it would give?

Hi, what about consulting, what Oracle documentation states about this? It is available e.g. online on http://tahiti.oracle.com/

For 10gR2, GRANT command is described here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm#i2155015 Quote:
WITH ADMIN OPTION
Specify WITH ADMIN OPTION to enable the grantee to:
* Grant the role to another user or role, unless the role is a GLOBAL role
* Revoke the role from another user or role
* Alter the role to change the authorization needed to access it
* Drop the role

If you grant a system privilege or role to a user without specifying WITH ADMIN OPTION, and then subsequently grant the privilege or role to the user WITH ADMIN OPTION, then the user has the ADMIN OPTION on the privilege or role.

To revoke the ADMIN OPTION on a system privilege or role from a user, you must revoke the privilege or role from the user altogether and then grant the privilege or role to the user without the ADMIN OPTION.
So, without this option, the user will only be allowed to DROP USER, but will not be able to grant/revoke this privilege to/from other users/roles.
Previous Topic: Oracle Advanced Security
Next Topic: Object/System privilege there.. Schema privilege there?
Goto Forum:
  


Current Time: Fri Apr 18 18:59:56 CDT 2014

Total time taken to generate the page: 0.12074 seconds