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