Assinging 'Select' previliges.

Hi!

I have created one user, 'user1' and have created (say 10) tables.Now, if i want another user (say 'user2') to view all the tables created by the user 'user1' How do i do it.Kindly enlighten me on this.

thanks in advance
vanitha

Hi,

Issue the following for each table...

grant select on table_name to user2;

Where table_name is the name of the table you wish user2 to be able to select on.

Cheers

Moff

Hi moff,

Sorrry, i for the sake of example i said 10 tables.What if i have so many tables and i can't keep giving grant statement for each & every table?Is there a way to grant 'select' previlege on the whole for the Schema of one user.

thanks.

vanitha

1) if 10 tables is many, I hope you'll never face a real DB !!
2) just do :
select 'grant select on ' || table_name || ' to user2;'
from dba_tables
where owner = 'user1'

hi,

I don't think you can issue a select privilege to a user for a whole schema. You could grant the 'select any table' privilege although this would allow that user to see any table in any schema.

You could write a simple script to create the sql needed to grant the select privilege to all tables in a schema.....

Something like this would work....

spool grant_sel.sql
select 'grant select on '||table_name||' to user2;'|| from user_tables;
spool off
@grant_sel

This should produce a file (grant_sel.sql) that contains a grant statement for each table in the schema of the user that ran the script. The it simply runs the script to grant the privileges....

Cheers

Moff.

Using the following

spool grant_sel.sql
select 'grant select on '||table_name||' to user2;'|| from user_tables;
spool off
@grant_sel

will work only for the existing set of tables.But actually i have around 1000 tables and what about the tables that will be created after executing the above script.I have been using only this to do.But this should be done after every addition of new tables and the tracing should be done for incremental grant.

thanks
vanitha

Hi,

As I said before... I'm not aware of the ability to grant a select privilege on all tables in a schema which would automatically grant the privilege to all new tables as they are created....
As far as I can see you have a couple of options....

1. grant 'user2' the 'select any' privilege. This will allow him/her to select from any table in the database including tables that are created after you issue the grant statement.

2. Run the script posted previously to grant the 'select' privilege on all existing tables in user1 schema (If run when connected as user1). Issue a grant for all new tables when they are created.

3. Run the script posted previously throughout the day....Pick a satisfactory time interval and schedule the script to run after the time interval has elapsed.


Has anyone else any other ideas......

Cheers
Moff.

Create a DDL trigger that will run a PL/SQL procedure that will create your grants ass needed.

Hi Zaggy,

Can you please provide an example ?

As far as I know, you can't have DDL statements in a DDL trigger (e.g. AFTER CREATE ON SCHEMA) and "GRANT SELECT ON table TO user" is a DDL right? (I am not sure on this one though! - please correct me if I am wrong :))

-amar

Oops, you are right, but I think this might just work.

Here is a link to the CREATE TRIGGER syntax:

http://otn.oracle.com/doc/oracle8i_8...h4h2.htm#40704

I would wrap the grant in an AUTONOMOUS TRANSACTION like:

PROCEDURE table_grant(table_name IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
error_tracking VARCHAR2(10);
BEGIN
execute immediate 'grant select on ' || table_name || ' to user2;'
END table_grant;


Then create a trigger that calls the procedure. I think it might work. No garuntees :-)

Even with autonomous transaction pragma it will NOT work!

-amar

Ok, I think I got it. Create a trigger that will write out a SQL file that contains your grants. Then use a java program to call an OS command. The OS command will call Sqlplus, which in turn calls your SQL file. Will this work?

P.S. I have the Java code, if you want it?