Home » RDBMS Server » Server Administration » Grant Select Permission
Grant Select Permission [message #245439] Sun, 17 June 2007 04:13 Go to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
I want to give permission to user 'arju' only select permission. Under user arju there are 300 tablse. I want to give only select permission to user arju to all these 300 tables. How I can do this
at a time without explicitly giving individual table (select) permission .

[Updated on: Sun, 17 June 2007 04:14]

Report message to a moderator

Re: Grant Select Permission [message #245441 is a reply to message #245439] Sun, 17 June 2007 04:20 Go to previous messageGo to next message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member
request is not clear .. who owns the tables you are talking about.. ? lets say if X owns the table and then you want to give read only access to y .. you create a role and grant select on all tables to that role and then grant that role to y.

-Sai Jeedigunta

Report message to a moderator

Re: Grant Select Permission [message #245443 is a reply to message #245439] Sun, 17 June 2007 04:41 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
Under user arju there are 300 tables. I want to give only select permission to user arju to all these 300 tables.

If ARJU owns those tables, he can do anything with them - query them, update, insert records into them, even drop them.

If you'd like to have only SELECT privilege, you'll have to create another user and grant SELECT explicitly for every single table, one by one.

Of course, you won't do that by typing all 300 lines in editor, but use a script which will do that for you; something like this:
SELECT 'GRANT SELECT ON ' || table_name || ' TO newly_created_user;' 
FROM user_tables;

Spool the result of the query into a file and run it at the SQL*Plus prompt.

Report message to a moderator

Re: Grant Select Permission [message #245445 is a reply to message #245439] Sun, 17 June 2007 04:49 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
Ok, Thanks. Then I cant do any operation on arju as he owns the table. I would create another user and run opration. Thanks.

select 'grant select on '||owner ||'.'||object_name||' to test;' "Permission"
from dba_objects
where object_type in ('TABLE', 'VIEW')
AND owner in ('ARJU')

and I have to run output of it.

Is there any way to revoke modify and delete permission of user arju who owns these 300 tables?

Report message to a moderator

Re: Grant Select Permission [message #245447 is a reply to message #245445] Sun, 17 June 2007 05:03 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Write a database trigger on every table which will prevent you (as the owner) and anyone else to mess up with your tables.

Report message to a moderator

Re: Grant Select Permission [message #245448 is a reply to message #245441] Sun, 17 June 2007 05:13 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
vjeedigunta wrote on Sun, 17 June 2007 15:20
request is not clear .. who owns the tables you are talking about.. ? lets say if X owns the table and then you want to give read only access to y .. you create a role and grant select on all tables to that role and then grant that role to y.

-Sai Jeedigunta


I have done it by spooling grant select...... But by creating role how I can do this?


Step1:create role select_privilege
Step2:grant select on (What will be? here)
Step3:grant select_privilege to user_name;

Report message to a moderator

Re: Grant Select Permission [message #245449 is a reply to message #245448] Sun, 17 June 2007 05:19 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Step 2 would be the same as if you did that with the newly_created_user - you'd grant SELECT on every single table to that role (instead of a user).

This approach is fine when you plan to create more than one user which is to be granted the same privileges - instead of granting them partially, user by user, you'd simply grant a ROLE to a user.

Report message to a moderator

Re: Grant Select Permission [message #245554 is a reply to message #245439] Mon, 18 June 2007 02:17 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
Ok I have created user arju who has granted select permission of prod1 schema. Now if I wanna access columns of table prod1 named hr then I have to use..
----

select * from prod1.hr

------

But I want that arju will access the table like this

select * from hr;

But under user arju there will exist no table. Tables will be exist under prod1. How is it possible?

Report message to a moderator

Re: Grant Select Permission [message #245569 is a reply to message #245554] Mon, 18 June 2007 02:52 Go to previous message
aline
Messages: 92
Registered: February 2002
Member
u can create view or synonyms to do what u want

Report message to a moderator

Previous Topic: Set Password for SYS
Next Topic: locating the init.ora
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 01:58:40 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.