| Restricting User access [message #479457] |
Sun, 17 October 2010 10:26  |
preet_kumar
Messages: 204
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 #479464 is a reply to message #479457] |
Sun, 17 October 2010 12:31  |
preet_kumar
Messages: 204
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 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
preet_kumar
Messages: 204
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 #479482 is a reply to message #479464] |
Sun, 17 October 2010 20:58 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
preet_kumar wrote on Sun, 17 October 2010 19:31This 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 #485807 is a reply to message #485806] |
Fri, 10 December 2010 01:46 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
mikailhs wrote on Fri, 10 December 2010 08:36Hi, 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
