Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

User with select only privelege

Posted on 2001-09-06
10
Medium Priority
?
700 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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 35

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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month14 days, 3 hours left to enroll

580 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