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

Thread: public synonym

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    public synonym

    we have public synonyms on tables which has select , insert and update privilege .
    i cannot change these privileges , can i still create read only user .

    could u suggest some work around ...
    urgent please ...

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    sorry , dont understand what you mean?

    Even if there is a public synonym, if you dont grant the privs on the objects directly they cannot access them

  3. #3
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    public synonyms are granted with insert , update , delete and alter priveleges ..

    can i still create readonly user .

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you dont create public synonyms with select or whatever

    you create the synonym THEN do the grants, so create your user and then select from the synonym and you will get insufficent priveleges (unless they are granted to public)

    read this for synonym creation

    http://download-west.oracle.com/docs...ements_72a.htm

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    an example for you

    Code:
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.1.0 - Production
    
    SQL> create user ds identified by ds;
    
    User created.
    
    SQL> grant create session to ds;
    
    Grant succeeded.
    
    SQL> create table demo (demo number);
    
    Table created.
    
    SQL> create public synonym demo for demo;
    
    Synonym created.
    
    SQL> connect ds/ds
    Connected.
    SQL> desc demo
    ERROR:
    ORA-04043: object "SYSTEM"."DEMO" does not exist
    
    
    SQL> select count(*) from demo;
    select count(*) from demo
                         *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    
    SQL> connect system/manager
    Connected.
    SQL> grant select on demo to ds;
    
    Grant succeeded.
    
    SQL> connect ds/ds;
    Connected.
    SQL> select * from demo;
    
    no rows selected
    
    SQL>

  6. #6
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    Dave problem is present database is already having 1000's of public synonyms with grant options with insert update and delete ....granted to public

    create public synonym test1 for isr.test1 ;

    grant insert , select on test1 to public ;

    with such grants on public synonyms .
    can we still create readonly users
    Last edited by prakashs43; 03-04-2004 at 07:29 PM.

  7. #7
    Join Date
    Nov 2001
    Location
    Planet Earth
    Posts
    116
    i guess your problem is like this.
    you have public role where 1000's over object granted to it with admin option. Later you grant public role to one user say myuser.
    i guess your question is :how to make readonly user?

    the anwser is.. there nothing to do with synonym. synonym is just another name for another oracle objects like table. To make ready only user is just issue command "grant select on mytable to myuser"
    that's all. Don't confusing between synonym and access privilage. they are no relation at all.

  8. #8
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Since you have granted privileges to PUBLIC you can't restrict a specific user to use those privilges.

    Security administrators and database users SHOULD GRANT a privilege or role to PUBLIC ONLY IF EVERY DATABASE USER REQUIRES the privilege or role.

    This is a good read if you are interested..
    PUBLIC : Is it a User, a Role, a User Group, a Privilege ?
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    revoke it from public and grant it to each individual or to a role then assign role to the user.

    Simple security

  10. #10
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    "Since you have granted privileges to PUBLIC you can't restrict a specific user to use those privilges."

    I posted this thread just to double check .

    Thanks all of you ...

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