User with select only privelege

We have a production system with one main user for the database, this user has all priveledges that allow him to select, update, insert and delete.
I want to create a safe user that everyone can log into the database as. This user must only have select priveleges and that is all.i.e. Not be able to change anything, add anything or delete.
How should I go about this?
NavtejAsked:
Who is Participating?
 
MindphaserConnect With a Mentor Commented:
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **
0
 
RMZCommented:
hi
create a new user
then make synoynum for all tables and views
then grant select them to user
or
in  owner schema
grant select  on table1 to newuser
and so on
----rmz-----
0
 
vanmeerendonkCommented:
I think it is better that you create a role, give select privileges on all tables, views to that role
and grant the role to the users or a specific user
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
konektorCommented:
.. after adding new object to privileged user, u must grant slect on it to 'safe' user ..
0
 
RMZCommented:
HI
conn system
CREATE ROLE READ_ONLY;
GRANT CONNECT,RESOURCE TO READ_ONLY;
-------------------------------------------------
CREATE USER TABLE1 IDENTIFIED BY TABLE1
DEFAULT TABLESPACE USR
TEMPORARY TABLESPACE TEMPORARY;
GRANT READ_ONLY TO TABLE1;
-----
CREATE PUBLIC  SYNONYM P_Scientific_Degree FOR MOTZ.P_Scientific_Degree;    
CREATE PUBLIC  SYNONYM P_Employee_Doct FOR MOTZ.P_Employee_Doct;
CREATE PUBLIC  SYNONYM FACULTY_SUBJECT_SEQ  FOR MOTZ.FACULTY_SUBJECT_SEQ  ;
CREATE PUBLIC  SYNONYM STD_SUB_TERMS  FOR MOTZ.STD_SUB_TERMS  ;
-------conn owner here motz
GRANT SELECT ON  P_Scientific_Degree TO READ_ONLY;
GRANT SELECT ON  P_Employee_Doct TO READ_ONLY;
GRANT SELECT ON  P_SUBJECTS TO READ_ONLY;
GRANT SELECT ON  P_GROUP  TO READ_ONLY;
GRANT SELECT ON  P_EMP_Seq TO READ_ONLY;
GRANT SELECT ON  FACULTY_SUBJECT_SEQ  TO READ_ONLY;
GRANT SELECT ON  FACULTY_SUBJECT_SEQ  TO READ_ONLY;
GRANT SELECT ON  STD_SUB_TERMS  TO READ_ONLY;
-----rmz----
0
 
Mark GeerlingsDatabase AdministratorCommented:
You need to create synonyms for all objects in the main schema.  These can either be public synonyms that you create as a DBA, or they could be private synonyms that you create in the new, read-only user's schema.

You also need to log in as the schema owner, then grant select on all tables and views to the new schema (or role).
Or, you could as a DBA just:
grant select any table to [read-only user/role]

Remember if you ever add tables or views, you have to create synonyms for them.  Also, remember to grant select on them to the read-only user (or role) if you did individual table grants rather than the "select any table" system privilege.
0
 
KongCommented:
Hi RMZ,

You should not grant resource to the read_only role if you only want to give select privs:

HI
conn system
CREATE ROLE READ_ONLY;
GRANT CONNECT,RESOURCE TO READ_ONLY;
-------------------------------------------------

Cheers!
0
 
marek_wiechulaCommented:
Kong is correct that you should not grant connect and resource to the role.  However, you should grant CREATE SESSION to the read only user.  (I would advise against mixing object privileges and system privileges in the same role.)

If you are planning to have only one additional user it doesn't make much difference if you grant these privileges to a role or directly to the user.  If you are considering adding a number of read-only users (say one per person using the database)then you should consider either the role or simply granting the SELECT privileges to PUBLIC with the GRANT option.  This second approach would be the best if you were planning to have a lot of read only users and were going to allow them to make their own views and/or stored procedures.

You can save yourself a lot of typing in generating the commands that RMZ has suggested with command below.  In this example I have used his suggested role READ_ONLY.  However you can subsitute the particular read only username is going to have or put in PUBLIC instead.  

SET PAGESIZE 0
SET LINESIZE 300
SET ECHO OFF
SET TRIMSPOOL ON
SPOOL GRANT_COMMANDS  
select 'GRANT SELECT ON ' || object_name || ' TO READ_ONLY ;'
  from DBA_OBJECTS
 where OWNER = owning_user_name
   and object_type in ( 'TABLE', 'VIEW', 'SEQUENCE' );

SPOOL CREATE_PUBLIC_SYNONYMS
select 'CREATE PUBLIC SYNONYM ' || object_name || ' FOR owning_user_name.' ||object_name ;
SPOOL OFF

(I may have left out one or two of the SET commands from SQL*PLUS.  Either look them up or edit the file after you're done to get rid of any extras.)  


0
 
TarekEslimCommented:
You either can create role or user for the read_only purposes.

grant create_session to this role/user.
grant select any table, execute any procedure to this role/user.

Now, if your application uses onwer.object on its code, you should not create synonyms, just refer the object to its owner. for example:
select * from onwer.table
rather than
select * from table
But if your system/application does not use the owner.object policy, you should create synonyms for all tables/objects your original user has.

If you created a role, then create a user and grant this role to him.

This new user or role now works properly overall the system but with select privilege onyl.

Tarek
0
 
DanRollinsCommented:
Hi Navtej,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. If there is no objection or further activity, I will suggest to:

    Refund points and save as a 0-pt PAQ.

** several good answers; I can't decide who should get credit ***

EXPERTS: Post a comment if you think somebody deserves credit here!

Please do not accept this comment as an answer!
==========
DanRollins -- EE database cleanup volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.