DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Assinging 'Select' previliges.

  1. #1
    Join Date
    Jul 2001
    Posts
    4
    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


  2. #2
    Join Date
    Feb 2000
    Posts
    175
    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

  3. #3
    Join Date
    Jul 2001
    Posts
    4

    Assigning 'Select' previlege

    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

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    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'


  5. #5
    Join Date
    Feb 2000
    Posts
    175
    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.


  6. #6
    Join Date
    Jul 2001
    Posts
    4
    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

  7. #7
    Join Date
    Feb 2000
    Posts
    175
    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.

  8. #8
    Join Date
    Apr 2001
    Posts
    219
    Create a DDL trigger that will run a PL/SQL procedure that will create your grants ass needed.

  9. #9
    Join Date
    Mar 2001
    Posts
    314
    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

  10. #10
    Join Date
    Apr 2001
    Posts
    219
    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 :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width