Create Read only user for a Schema

One thing you need to remember before read this post is there is no easy or shortcut way to make a read only user of another schema. Like grant select on username to another_username- there is no such single command like this. However you may have several alternatives to make read only user for a schema.

I will demonstrate the procedure with examples to make a read only user for a schema. In the example I will make devels user which will have read only permission on prod schema.
Let’s start by creating PROD user.
SQL> CREATE USER PROD IDENTIFIED BY P;
User created.

SQL> GRANT DBA TO PROD;
Grant succeeded.

SQL> CONN PROD/P;
Connected.

SQL> CREATE TABLE PROD_TAB1 ( A NUMBER PRIMARY KEY, B NUMBER);
Table created.

SQL> INSERT INTO PROD_TAB1 VALUES(1,2);
1 row created.

SQL> CREATE TABLE PROD_TAB2(DATE_COL DATE);
Table created.

SQL> CREATE OR REPLACE TRIGGER PROD_TAB2_T AFTER INSERT ON PROD_TAB1
BEGIN
INSERT INTO PROD_TAB2 VALUES(SYSDATE);
END;
/
Trigger created.

SQL>CREATE VIEW A AS SELECT * FROM PROD_TAB2;
View created.

Method 1: Granting Privilege Manually

Step 1: Create devels user
SQL> CREATE USER DEVELS IDENTIFIED BY D;
User created.

Step 2: Grant only select session and create synonym privilege to devels user.
SQL> GRANT CREATE SESSION ,CREATE SYNONYM TO DEVELS;
Grant succeeded.

Step 3:Make script to grant select privilege.
$vi /oradata2/script.sql
SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/select_only_to_prod.sql
@@/oradata2/select_only_script.sql
SPOOL OFF

This script will run the /oradata2/select_only_script.sql and generate a output script /oradata2/select_only_to_prod.sql which need to be run in fact.

Step 4:
Prepare the /oradata2/select_only_script.sql script which will work as input for /oradata2/script.sql file.

$vi /oradata2/select_only_script.sql

SELECT ‘GRANT SELECT ON PROD.’ ||TABLE_NAME || ‘ TO DEVELS;’ FROM DBA_TABLES WHERE OWNER=’PROD’;
SELECT ‘GRANT SELECT ON PROD.’ ||VIEW_NAME || ‘ TO DEVELS;’ FROM DBA_VIEWS WHERE OWNER=’PROD’;

Step 5:
Now execute the /oradata2/script.sql which will in fact generate scipt /oradata2/select_only_to_prod.sql.
SQL> @/oradata2/script.sql

GRANT SELECT ON PROD.PROD_TAB1 TO DEVELS;
GRANT SELECT ON PROD.PROD_TAB2 TO DEVELS;

Step 6:
Execute the output script select_only_to_prod.sql which will be used to grant read only permission of devels user to prod schema.
SQL> @/oradata2/select_only_to_prod.sql

Step 7:
Log on devels user and create synonym so that the devels user can access prod’s table without any dot(.). Like to access prod_tab2 of prod schema he need to write prod.prod_tab2. But after creating synonym he simply can use prod_tab2 to access devels table and views.
To create synonym do the following,

SQL>CONN DEVELS/D;

SQL>host vi /oradata2/script_synonym.sql

SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/synonym_to_prod.sql
@@/oradata2/synonym_script.sql
SPOOL OFF

SQL>host vi /oradata2/synonym_script.sql
SELECT ‘CREATE SYNONYM ‘ ||TABLE_NAME|| ‘ FOR PROD.’ ||TABLE_NAME||’;’ FROM ALL_TABLES WHERE OWNER=’PROD’;
SELECT ‘CREATE SYNONYM ‘ ||VIEW_NAME|| ‘ FOR PROD.’ ||VIEW_NAME||’;’ FROM ALL_VIEWS WHERE OWNER=’PROD’;
SQL>@/oradata2/script_synonym.sql
SQL>@/oradata2/synonym_to_prod.sql

Step 8: At this stage you have completed your job. Log on as devels schema and see,
SQL> select * from prod_tab1;
1 2

SQL> show user
USER is “DEVELS”

Only select privilege is there. So DML will throw error. Like,

SQL> insert into prod_tab1 values(4,3);
insert into prod_tab1 values(4,3)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Method 2: Writing PL/SQL Code
This is script for table :

set serveroutput on
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT table_name FROM dba_tables where owner=’PROD’;
BEGIN
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:=’GRANT SELECT ON PROD.’||tables.table_name||’ TO devels’;
execute immediate sql_txt;
END LOOP;
END;
/

This is the script for grant select permission for views.

DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT view_name FROM dba_views where owner=’PROD’;
BEGIN dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:=’GRANT SELECT ON PROD.’||tables.view_name||’ TO devels’;
–dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/

To create synonym on prod schema,
Log on as devels and execute the following procedure.

SQL>CONN DEVELS/D
SQL>
DECLARE
sql_txt VARCHAR2(300);
CURSOR syn_cur IS
SELECT table_name name FROM all_tables where owner=’PROD’
UNION SELECT VIEW_NAME name from all_views where owner=’PROD’ ;
BEGIN dbms_output.enable(10000000);
FOR syn IN syn_cur LOOP
sql_txt:=’CREATE SYNONYM ‘||syn.name|| ‘ FOR PROD.’||syn.name ;
dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/

Method 3: Writing a Trigger

After granting select permission in either of two ways above you can avoid creating synonym by simply creating a trigger.

Create a log on trigger that eventually set current_schema to prod just after log in DEVELS user.

create or replace trigger log_on_after_devels
after logon ON DEVELS.SCHEMA
BEGIN
EXECUTE IMMEDIATE ‘alter session set CURRENT_SCHEMA = prod’;
END;
/

Author Profile

sysdba ;

Other posts by sysdba

Author's web site



Are you satisfied with this blog?
Why not subscribe our RSS Feed? you will always get the latest post.


127 Comments

  1. dynclactalm

    Really nice site. Hope to visit it again soon

    1
  2. vovcsexik

    Вы продаете свой сайт?

    2
  3. Vano

    Привет, статья хорошая ,но что то с отображением шаблона твоего блога.
    В Опере проверь свой шаблон!

    3
  4. visualdesigning

    Very interesting blog. I will come regularly here. Thanks the author

    4
  5. Bestreader

    I regret, that I can not participate in discussion now. I do not own the necessary information. But with pleasure I will watch this theme.
    Certainly. I join told all above.
    What good phrase
    I am am excited too with this question. Prompt, where I can find more information on this question?
    Yes, really. I join told all above. Let’s discuss this question.

    5
  6. Читатель

    Как раз то, что нужно. Я знаю, что вместе мы сможем прийти к правильному ответу.

    6
  7. Г.Апдейт

    И как в таком случае нужно поступать?

    7
  8. Alasail

    I very much liked the given material. I will come here often

    8
  9. dexterra

    its really great blog. Thx for the auther

    9
  10. Гриша

    In my opinion you are mistaken. I suggest it to discuss. Write to me in PM.
    In my opinion you are not right. I can defend the position.
    It is remarkable, very amusing idea
    I consider, that you are not right. Write to me in PM.
    It is remarkable, it is rather valuable answer

    10
  11. numodeljob

    I very much like this blog. Yet time I will come here

    11
  12. sanatorka

    The author has very much tried. I support the majority of commentators

    12
  13. buy-tadalafil-cialis-online

    I wanna say: auther is the best I will visit this blog very soon again

    13
  14. getpillsonline

    Waw its really nice blog. Hope I can visit this again

    14
  15. aonebill

    Hello. All of us could discuss together this theme in more details!?

    15
  16. euronis-free

    The best blog which I saw before. There are many usefull information for me

    16
  17. vovcfaam

    Можно разместить вашу статью на своем сайте?

    17
  18. Online Degree

    There’s a wealth of information here. Thanks! I’ll be back for more.

    18
  19. beerNireets

    good post

    19
  20. Grootscow

    cool posting

    20
  21. roomofrequirement

    The best blog which I saw before. Hope to vissit it again

    21
  22. room-of-requirement

    Very interestin, why u thik so? Is`t matter? Huh

    22
  23. BatteSwadeZef

    good post, thank you!

    23
  24. Affiliate Network

    Where does funding for UFT merit bonuses come from?

    24
  25. Dima.Kruchuev

    I fully agree with all the positive feedback on the blog, although there is little negative.

    25
  26. Zorealleypeax

    eee, respect! cool post!

    26
  27. Slava.Fitukopewich

    Всех пользователей женского рода поздравляю с 8 марта.

    27
  28. Artem.Fabkifin

    Мне понравилось, обязательно добавлю в закладки и буду пользоваться.

    28
  29. Affibript

    yahoo good post !

    29
  30. Alex.Parret

    Все понравилось, присутствуют обновления, комменты еще бы почистить.

    30
  31. Russian dvd

    Thank you, I regularly read your blog, I have some questions for you, let me know if you want to contact me by e-mail

    31
  32. Fedor.Swertifen

    Интересно, подобного мало где найдёшь.

    32
  33. Нью Йорк

    can i translate in Russian and post on my blog? )

    33
  34. Hristianin

    Почему нигде, было бы желание и все найдешь.

    34
  35. sysdba

    Ok, feel free to do it

    35
  36. Relaxxxni

    Нашел много интересного в этом блоге, очень доволен.

    36
  37. Bayliner

    Согласен, что можно найти без проблем такую информацию.

    37
  38. Georgiy.Fekolatov

    Блог просто гуд, побольше бы таких в инете.

    38
  39. Daheng

    неплохой ресурс, все полностью устроило. буду пользоваться дальше.

    39
  40. download free adobe photoshop cs4

    I now, this is a great article.A successful blog needs unique, useful content that interests the readers

    40
  41. Infland

    Хороший блог, также все понравилось как и многим другим отписавшимся.

    41
  42. Kinokashh

    Не против, действительно хорошо сделано.

    42
  43. Download Movies

    Goodmorning
    awesome post – i’m creating video about it and i will post it to youtube !
    if you wana to help or just need a link send me email !

    43
  44. Mihsa.Fitrewovich

    Такие вопросы можно здесь и не обсуждать, между собой общайтесь.

    44
  45. Formasamara

    Отлично сделали, есть чему поучиться.

    45
  46. Serpantin

    Отличный блог, буду и дальше пользоваться им.

    46
  47. Mcomes

    неплохой ресурс подсказали, очень доволен. спс.

    47
  48. Bettech

    Отлично сделали блог, есть чему начинающим поучиться. Молодцы.

    48
  49. Vadim.Gukoilov

    Меня все полностью устраивает, поэтому и молчу.

    49
  50. Lobsterauto

    Автор, выражаю тебе респект за качественный блог! Наконец-то встретил сайт, который похож на СДЛ. Здесь приятно находиться, будет время еще почитаю другие темы!

    50
  51. Grigoriy.Kon

    Все бы ничего, только комментов много почистить надо.

    51
  52. Tydert

    А ак часто обновления происходят скажите.

    52
  53. effexor xr and testimonials

    class action suits agianst avandia lawyers

    53
  54. Ferkinast

    тематика блога понравилась, буду пользоваться и дальше.

    54
  55. Evrodims

    Отлично сделали, и тематика подходящая. Автору респект.

    55
  56. Avtolubitil

    А автомобильный по тематике блог никто не подскажет.

    56
  57. Awmobile

    У меня у самого есть несколько блогов и поэтому точно скажу, этот блог сделан для людей.

    57
  58. Brockers

    Да в общем так оно и есть, в этом плане я с Вами соглашусь.

    58
  59. Muzikallo

    Все отлично работает, не знаю чего у тебя не грузится.

    59
  60. Dubafikos

    Мне как журналисту понравилось наполнение, побольше бы таких.

    60
  61. Beautystore

    Можно и так, но я бы лучше подождал немного.

    61
  62. Skladpack

    интересно продумали все, есть чему позавидовать даже.

    62
  63. Biznes.Lady

    А может мне кто подскажет хороший ресурс по малой авиации, заранее спс.

    63
  64. Allcomedy

    Пользовался ранее и буду пользоваться дальше.

    64
  65. Rukadelnik

    Можно и так, главное чтобы результат был хороший, а в остальном походу процесса разберемся.

    65
  66. Shopider

    Used earlier and more I will blog for people to do.

    66
  67. смотреть онлайн сериал интерны

    сериал интерны онлайн

    67
  68. сглаз

    Супер сайт спасибо!

    68
  69. Futerfas

    А может будите писать это в другом месте, надоело уже.

    69
  70. Privatik

    Blog liked, but a lot of unnecessary comments.

    70
  71. Avtogiro

    Который раз уже убеждаюсь в том, что много клонов тут.

    71
  72. Serialu

    Да, не ожидал что увижу сдесь столько интересного в комментах.

    72
  73. Nicerb

    Да уже частенько такое можно встретить, так что не удивляйся.

    73
  74. Nicerb

    Interestingly, and advertising posts are removed or you can write.

    74
  75. Hodikos

    Неплохое решение, только зачем столько рекламы.

    75
  76. mirknig

    хороший блог, лично мне понравился

    76
  77. Mninvest

    Кто нибудь из пользователей занимается недвижимостью еще?

    77
  78. Ulitka

    Если кому интересны музыка, игры и новинки кино, могу безвозмездно помоч.

    78
  79. kirpich

    Мне понравился, не плохой блог.

    79
  80. Royalik

    Да по чему бы и нет??!! просто стараться надо иногда.

    80
  81. Vitatez

    Добавляй, я уже давно так сделал и не жалею.

    81
  82. Musicdown

    Может и подойдет, нало проверить.

    82
  83. Bitplanet

    Мне так же все подходит, хотя было и получше.

    83
  84. Lucter

    Если кому интересна информация по лучевой терапии, обращайтесь.

    84
  85. Bitlesc

    Согласен с автором по поводу информации.

    85
  86. 86
  87. Toxyzone

    This theme is simply matchless :), it is very interesting to me)))
    What charming message
    Yes, really. And I have faced it. We can communicate on this theme.
    You are not right. I suggest it to discuss. Write to me in PM, we will talk.
    I think, that you are mistaken. I can prove it. Write to me in PM, we will talk.
    -

    87
  88. Clipartmag

    Тоже думаю добавить в закладки, интересный блог.

    88
  89. Sofitya

    Нашёл, то что искал! Отдельное спасибо за сборники! Нет слов!

    89
  90. XNHoward

    Спасибо за материал..

    90
  91. Accounting

    I fully agree with the author.

    91
  92. форум посёлка

    Супер сайт спасибо!

    92
  93. Rumanager

    Поддерживаю автора, все правильно.

    93
  94. bu-dzyucu.ru

    хороший блог, мне нравятся боевые исскуства!!!

    94
  95. похудение

    Супер сайт спасибо!

    95
  96. Gricha.Fityjorevoder

    Можно и так сказать, но факт есть факт.

    96
  97. samuray

    Автор все правильно сделал, поддерживаю его.

    97
  98. Prosto-dohod

    Не плохая информация о видах зароботка в интернете и правильном выборе направления.

    98
  99. Cenaby

    Тоже верно, хотя многие бы не согласились.

    99
  100. Maconsulting

    Лучше бы сделал, как все говорят. Иногда надо прислушиваться к людям.

    100
  101. Card

    Понравилось, добавлю в закладки.

    101
  102. divx онлайн

    Интересно очень

    102
  103. Avtolux

    Сегодня чтото страницы хреново грузятся везде.

    103
  104. Landfil

    неплохо придумано, молодцы.

    104
  105. Dlivel

    Согласен с автором, так и надо делать.

    105
  106. Accent

    Тоже так думаю, лучше проверять сразу.

    106
  107. Yurispred

    Да, лучше бы Вы писали по человечески.

    107
  108. Sdelays

    Понравилось конечно, буду пользоваться и дальше.

    108
  109. Filonet

    тут интересно, но надо и поспать.

    109
  110. dlaxisvansоs

    Классная статья. А вы знали что форумы могут быть настроены различным образом.

    110
  111. Estate

    Отдельное спасибо за это, очень приятно.

    111
  112. Spacereal

    Зачем писать такое, можно и подумать иногда.

    112
  113. Izosimov

    Не часто такое можно увидеть, удивлен.

    113
  114. Webmasterdept

    We made good, I’ll use it.

    114
  115. Продать

    Интересно очень

    115
  116. Rssweb

    Everything is written correctly, and rightly so.

    116
  117. Jobde

    Also agree with the author, all true.

    117
  118. Kinoshnik

    Правильно написал, только для чего.

    118
  119. Weather

    Каспер не пускает, ели зашел.

    119
  120. Hcmsu

    Приятно работать на таких ресурсах.

    120
  121. Zrela

    готово, спасибо.

    121
  122. Ytkin

    Хороший ресурс, добавлю в закладки.

    122
  123. Meshochek

    Попал случайно, но доволен.

    123
  124. воздушные фильтра

    Классный у вас у блога дизайн

    124
  125. Fanstyle

    Конечно правильно думаешь, хотя можно и по другому.

    125
  126. Swiden

    Всё выше сказанное правда. Присоединяюсью.

    126
  127. industriya

    In it something is. Now all is clear, I thank for the information.
    It is remarkable, very useful phrase
    Your opinion is useful
    You have quickly thought up such matchless answer?
    I consider, that you commit an error. Write to me in PM, we will talk.

    127

Leave A Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>