Home » RDBMS Server » Security » regarding grants (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi)
regarding grants [message #359688] Tue, 18 November 2008 00:43 Go to next message
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 #359698 is a reply to message #359688] Tue, 18 November 2008 00:54 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
And what problems are you having? Simply grant the appropriate rights to the appropriate users.
Re: regarding grants [message #359703 is a reply to message #359688] Tue, 18 November 2008 00:57 Go to previous messageGo to next message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
i want to grant rights to machine not user.

i want one employee to insert but others should not.........
Re: regarding grants [message #359711 is a reply to message #359703] Tue, 18 November 2008 01:09 Go to previous messageGo to next message
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 #359718 is a reply to message #359688] Tue, 18 November 2008 01:19 Go to previous messageGo to next message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
can you give me example
Re: regarding grants [message #359738 is a reply to message #359718] Tue, 18 November 2008 02:05 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You'll have to grant privileges on every table to every user separately. There's no way to grant privileges on a whole schema; of course, it doesn't mean that you'll have to write all these GRANT statements by yourself - write an SQL which will write SQL for you (there've been examples here before - search for them!).

Also, perhaps you'd want to create a ROLE which would be granted SELECT on these tables, and then you'd simply GRANT ROLE to all users.
Re: regarding grants [message #359763 is a reply to message #359688] Tue, 18 November 2008 02:39 Go to previous messageGo to next message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
what do you mean by user?

i want grant on the employees working on a user oracle_test
Re: regarding grants [message #359814 is a reply to message #359763] Tue, 18 November 2008 04:33 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
So you are saying that you have multiple people, logging in as the same user. Baaaaaad idea!
Re: regarding grants [message #359815 is a reply to message #359814] Tue, 18 November 2008 04:34 Go to previous messageGo to next message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
yes

multiple user logging on one schema


Re: regarding grants [message #359820 is a reply to message #359815] Tue, 18 November 2008 04:46 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Then you are, as a colleague of mine would say, "On to plums". Create a user for each user (see, the hint is in the term Wink ) Give each user the appropriate rights.
Re: regarding grants [message #359849 is a reply to message #359763] Tue, 18 November 2008 06:04 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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 /forum/fa/1602/0/?) 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 /forum/fa/2877/0/, but I hope you now got the picture.
Re: regarding grants [message #359857 is a reply to message #359849] Tue, 18 November 2008 06:16 Go to previous message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
Thanks a lot!!!!!!!!!!!!!!!!!!
Previous Topic: Databse login problem
Next Topic: oracle vault
Goto Forum:
  


Current Time: Thu Mar 28 08:53:51 CDT 2024