Home » RDBMS Server » Security » table privileges to newly created user (11g, 11.2.0.3, solaris10)
table privileges to newly created user [message #640855] Thu, 06 August 2015 02:16 Go to next message
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 #640856 is a reply to message #640855] Thu, 06 August 2015 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
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.


Wrong.
A synonym does not give any right access, it is just another name for an object.

Quote:
Our requirement is to create one user and provide them only select privileges to selected tables.


Create a role with these privileges and grant it to the new user.

Re: table privileges to newly created user [message #640857 is a reply to message #640856] Thu, 06 August 2015 02:36 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Michel,

But, public synonym on table means any user can able to select table's data.

Please advice what kind of privileges involve in the roles so that, to restrict new users to select table's list other than provide list.

Regards,
Ashish
Re: table privileges to newly created user [message #640858 is a reply to message #640857] Thu, 06 August 2015 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But, public synonym on table means any user can able to select table's data.


No.

What does mean "table's list other than provide list"?

Re: table privileges to newly created user [message #640860 is a reply to message #640857] Thu, 06 August 2015 03:10 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Quote:
But, public synonym on table means any user can able to select table's data.

Wrong. The user (or role) also needs a GRANT on the underlying table.
Re: table privileges to newly created user [message #640864 is a reply to message #640860] Thu, 06 August 2015 03:39 Go to previous messageGo to next message
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 #640865 is a reply to message #640864] Thu, 06 August 2015 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
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.


"public synonym" and "grant to public" are 2 different things and you didn't already mentioned you only spoke about public synonyms.

Quote:
Now, new user, which has connect privileges can also execute select stmt on th table of other schema.


If it has correct privileges then it is what you want otherwise it has not correct privileges; you sentence is pointless.

Quote:
I want to restrict select stmt to the table for newly created users. How can it be possible?


Give the correct privileges.

Re: table privileges to newly created user [message #640868 is a reply to message #640865] Thu, 06 August 2015 03:53 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 #640871 is a reply to message #640870] Thu, 06 August 2015 04:03 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Revoke the privileges from PUBLIC and create appropriate roles.
Re: table privileges to newly created user [message #640877 is a reply to message #640871] Thu, 06 August 2015 04:25 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Got your point. I have tested it here.

Need to revoke the privileges of "select" from public and have to assigned to specific users. Then only my requirement will fulfill.

Thank you.
Re: table privileges to newly created user [message #640878 is a reply to message #640877] Thu, 06 August 2015 04:30 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Thank you for letting us know.
Re: table privileges to newly created user [message #655572 is a reply to message #640878] Fri, 02 September 2016 16:00 Go to previous message
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

Previous Topic: Problem with Oracle Wallet functionality in a call HTTPS web serivces
Next Topic: encryption for all tablespace
Goto Forum:
  


Current Time: Thu Mar 28 13:14:33 CDT 2024