| regarding grants [message #359688] |
Tue, 18 November 2008 00:43  |
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
|
Member |
|
|
Hi
I have a schema named ORACLE_TEST. I have many tables in it.
There are developers working on this schema and tables
my requirement is that
i want only one employee to have rights to insert , update, delete from this tables.
other should only have the rights to view this tables..
i have only one schema
Thanks
|
|
|
|
|
|
|
|
| Re: regarding grants [message #359711 is a reply to message #359703] |
Tue, 18 November 2008 01:09  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Machines don't code. People do.
So grant privs to people, not to machines.
I bet all your developers use the object owner's username to logon to the database, right?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: regarding grants [message #359849 is a reply to message #359763] |
Tue, 18 November 2008 06:04 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
| gaikwadrachit wrote on Tue, 18 November 2008 09:39 | what do you mean by user?
i want grant on the employees working on a user oracle_test
|
Pablolee is a native English speaker (really, what language do you speak in Scotland? Scotch  ?) so - just in case you can't understand that, let me try again, this time in Croatian English:
You work in a company. There is a Boss and four employees: Wilma, Fred, Betty and Barney. You have also decided to create a database; tables are stored in a schema and owned by user. Its name is ORACLE_TEST.
As Boss is THE boss, he knows password which is used to connect to the ORACLE_TEST schema and he can read, update and delete anything he wants.
However, the Boss and you agreed that the rest of the employees should only read (i.e. SELECT) data. You can not do that by giving them ORACLE_TEST's password and ask them not to modify anything they see (because, someone of the WILL change something, some day). Therefore, you'll have to do something else: create one database user per employee, for example: ORACLE_FRED, ORACLE_BARNEY, ORACLE_WILMA and ORACLE_BETTY.
You'd then grant some privileges to these users: SELECT and UPDATE to Barney and Betty, just SELECT to Fred and Wilma. You can do that by creating roles, grant privileges to these roles and - finally - grant roles to database users. Or, you can grant privileges directly to users - whichever you prefer.
You'll also create synonyms in their schemas which will point to the "original" ORACLE_TEST's tables.
Tomorrow morning, Fred will connect to the database as ORACLE_FRED/FREDDIE@MERCURY (or whatever your database alias is) and will be able to query records; any other action will be followed by the "insufficient privileges" error message.
That was one of many possible scenarios  , but I hope you now got the picture.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
) Give each user the appropriate rights.