Solved

User with select only privelege

Posted on 2001-09-06
10
681 Views
Last Modified: 2012-08-13
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?
0
Comment
Question by:Navtej
10 Comments
 
LVL 2

Expert Comment

by:RMZ
ID: 6459975
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
 
LVL 2

Expert Comment

by:vanmeerendonk
ID: 6460120
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
 
LVL 9

Expert Comment

by:konektor
ID: 6460218
.. after adding new object to privileged user, u must grant slect on it to 'safe' user ..
0
 
LVL 2

Expert Comment

by:RMZ
ID: 6460260
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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 6460538
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Expert Comment

by:Kong
ID: 6462934
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
 
LVL 1

Expert Comment

by:marek_wiechula
ID: 6466406
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
 
LVL 1

Expert Comment

by:TarekEslim
ID: 6473458
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 7064004
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
 
LVL 6

Accepted Solution

by:
Mindphaser earned 0 total points
ID: 7091405
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle 12c 10 100
Convert fixed months, weeks or years to days in oracle 9 50
Add 0 to end of Number 21 72
statspack purge automate 7 30
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now