| table privileges to newly created user [message #640855] |
Thu, 06 August 2015 02:16  |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear All,
In our production environment, all tables are having public synonym. So any new users can able to access tables data even if provided only connect privileges.
Since public synonym work in the same ways.
Our requirement is to create one user and provide them only select privileges to selected tables. Can this be possible without doing any changes on production coding?
Regards,
Ashish Kumar Mahanta
|
|
|
|
|
|
|
|
|
|
|
|
| Re: table privileges to newly created user [message #640864 is a reply to message #640860] |
Thu, 06 August 2015 03:39  |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
As, I already mentioned, available tables has public synonym (grant select to public is provided) so any of the user can able to execute select stmt.
Now, new user, which has connect privileges can also execute select stmt on th table of other schema.
I want to restrict select stmt to the table for newly created users. How can it be possible?
|
|
|
|
|
|
| Re: table privileges to newly created user [message #640868 is a reply to message #640865] |
Thu, 06 August 2015 03:53 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
To be clear, if you've granted select to public and don't want everyone to get select then you need to revoke the select to public and only grant select to the people who should have it.
There's no way to get public grants to selectively not apply to certain users.
|
|
|
|
| Re: table privileges to newly created user [message #640870 is a reply to message #640865] |
Thu, 06 August 2015 03:58 |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear Michel,
Please let me know the correct privileges. Since we cannot do any changes on public synonym as it is working fine at production.
Have to create user who have only select privileges to few tables (assigned tables). What privileges can i impose on new user to restrict it to execute select stmt on other tables which has public synonym (with grant select privileges)?
|
|
|
|
|
|
|
|
|
|
| Re: table privileges to newly created user [message #655572 is a reply to message #640878] |
Fri, 02 September 2016 16:00 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
You are missing the big picture. make 2 roles. Replace the select to public that you issued to select to the first roll name. Then grant ONLY the limited rights to the second role. Then grant the first role to all users except the one you want limited rights to and assign them the second role.
The nice thing about using roles is that when you get a new object (table,view...) all you have to do is grant the rights on that object to the role and everyone who has the role will immediately get the new grants.
Sorry I just noticed that this issue was last updated in 2015. My apologizes.
[Updated on: Fri, 02 September 2016 16:00] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
