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 ...
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
public synonyms are granted with insert , update , delete and alter priveleges ..
can i still create readonly user .
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
an example for you
Oracle9i Enterprise Edition Release 18.104.22.168.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 22.214.171.124.0 - Production
SQL> create user ds identified by ds;
SQL> grant create session to ds;
SQL> create table demo (demo number);
SQL> create public synonym demo for demo;
SQL> connect ds/ds
SQL> desc demo
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
SQL> grant select on demo to ds;
SQL> connect ds/ds;
SQL> select * from demo;
no rows selected
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 06:29 PM.
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.
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 ?
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
revoke it from public and grant it to each individual or to a role then assign role to the user.
"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 ...
Click Here to Expand Forum to Full Width