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;
/
Really nice site. Hope to visit it again soon
Вы продаете свой сайт?
Привет, статья хорошая ,но что то с отображением шаблона твоего блога.
В Опере проверь свой шаблон!
Very interesting blog. I will come regularly here. Thanks the author
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.
Как раз то, что нужно. Я знаю, что вместе мы сможем прийти к правильному ответу.
И как в таком случае нужно поступать?
I very much liked the given material. I will come here often
its really great blog. Thx for the auther
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
I very much like this blog. Yet time I will come here
The author has very much tried. I support the majority of commentators
I wanna say: auther is the best I will visit this blog very soon again
Waw its really nice blog. Hope I can visit this again
Hello. All of us could discuss together this theme in more details!?
The best blog which I saw before. There are many usefull information for me
Можно разместить вашу статью на своем сайте?
There’s a wealth of information here. Thanks! I’ll be back for more.
good post
cool posting
The best blog which I saw before. Hope to vissit it again
Very interestin, why u thik so? Is`t matter? Huh
good post, thank you!
Where does funding for UFT merit bonuses come from?
I fully agree with all the positive feedback on the blog, although there is little negative.
eee, respect! cool post!
Всех пользователей женского рода поздравляю с 8 марта.
Мне понравилось, обязательно добавлю в закладки и буду пользоваться.
yahoo good post !
Все понравилось, присутствуют обновления, комменты еще бы почистить.
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
Интересно, подобного мало где найдёшь.
can i translate in Russian and post on my blog? )
Почему нигде, было бы желание и все найдешь.
Ok, feel free to do it
Нашел много интересного в этом блоге, очень доволен.
Согласен, что можно найти без проблем такую информацию.
Блог просто гуд, побольше бы таких в инете.
неплохой ресурс, все полностью устроило. буду пользоваться дальше.
I now, this is a great article.A successful blog needs unique, useful content that interests the readers
Хороший блог, также все понравилось как и многим другим отписавшимся.
Не против, действительно хорошо сделано.
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 !
Такие вопросы можно здесь и не обсуждать, между собой общайтесь.
Отлично сделали, есть чему поучиться.
Отличный блог, буду и дальше пользоваться им.
неплохой ресурс подсказали, очень доволен. спс.
Отлично сделали блог, есть чему начинающим поучиться. Молодцы.
Меня все полностью устраивает, поэтому и молчу.
Автор, выражаю тебе респект за качественный блог! Наконец-то встретил сайт, который похож на СДЛ. Здесь приятно находиться, будет время еще почитаю другие темы!
Все бы ничего, только комментов много почистить надо.
А ак часто обновления происходят скажите.
class action suits agianst avandia lawyers
тематика блога понравилась, буду пользоваться и дальше.
Отлично сделали, и тематика подходящая. Автору респект.
А автомобильный по тематике блог никто не подскажет.
У меня у самого есть несколько блогов и поэтому точно скажу, этот блог сделан для людей.
Да в общем так оно и есть, в этом плане я с Вами соглашусь.
Все отлично работает, не знаю чего у тебя не грузится.
Мне как журналисту понравилось наполнение, побольше бы таких.
Можно и так, но я бы лучше подождал немного.
интересно продумали все, есть чему позавидовать даже.
А может мне кто подскажет хороший ресурс по малой авиации, заранее спс.
Пользовался ранее и буду пользоваться дальше.
Можно и так, главное чтобы результат был хороший, а в остальном походу процесса разберемся.
Used earlier and more I will blog for people to do.
сериал интерны онлайн
Супер сайт спасибо!
А может будите писать это в другом месте, надоело уже.
Blog liked, but a lot of unnecessary comments.
Который раз уже убеждаюсь в том, что много клонов тут.
Да, не ожидал что увижу сдесь столько интересного в комментах.
Да уже частенько такое можно встретить, так что не удивляйся.
Interestingly, and advertising posts are removed or you can write.
Неплохое решение, только зачем столько рекламы.
хороший блог, лично мне понравился
Кто нибудь из пользователей занимается недвижимостью еще?
Если кому интересны музыка, игры и новинки кино, могу безвозмездно помоч.
Мне понравился, не плохой блог.
Да по чему бы и нет??!! просто стараться надо иногда.
Добавляй, я уже давно так сделал и не жалею.
Может и подойдет, нало проверить.
Мне так же все подходит, хотя было и получше.
Если кому интересна информация по лучевой терапии, обращайтесь.
Согласен с автором по поводу информации.
http://feeds.feedburner.com/blogspot/BVPZ
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.
-
Тоже думаю добавить в закладки, интересный блог.
Нашёл, то что искал! Отдельное спасибо за сборники! Нет слов!
Спасибо за материал..
I fully agree with the author.
Супер сайт спасибо!
Поддерживаю автора, все правильно.
хороший блог, мне нравятся боевые исскуства!!!
Супер сайт спасибо!
Можно и так сказать, но факт есть факт.
Автор все правильно сделал, поддерживаю его.
Не плохая информация о видах зароботка в интернете и правильном выборе направления.
Тоже верно, хотя многие бы не согласились.
Лучше бы сделал, как все говорят. Иногда надо прислушиваться к людям.
Понравилось, добавлю в закладки.
Интересно очень
Сегодня чтото страницы хреново грузятся везде.
неплохо придумано, молодцы.
Согласен с автором, так и надо делать.
Тоже так думаю, лучше проверять сразу.
Да, лучше бы Вы писали по человечески.
Понравилось конечно, буду пользоваться и дальше.
тут интересно, но надо и поспать.
Классная статья. А вы знали что форумы могут быть настроены различным образом.
Отдельное спасибо за это, очень приятно.
Зачем писать такое, можно и подумать иногда.
Не часто такое можно увидеть, удивлен.
We made good, I’ll use it.
Интересно очень
Everything is written correctly, and rightly so.
Also agree with the author, all true.
Правильно написал, только для чего.
Каспер не пускает, ели зашел.
Приятно работать на таких ресурсах.
готово, спасибо.
Хороший ресурс, добавлю в закладки.
Попал случайно, но доволен.
Классный у вас у блога дизайн
Конечно правильно думаешь, хотя можно и по другому.
Всё выше сказанное правда. Присоединяюсью.
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.