This is one of the number one issues for a DBA. Well, the good news is that it has become really easy to do this.
My methodology is to create public synonyms for the user tables and grant access to the user tables underlying those synonyms.
Example:
User : Tom
Table : Products, PriceList
Purpose : Grant access to user Bob
Here is how to do it:
Create public synonyms for tables Products, PriceList
Normally User Tom sees them as Products and PriceList. Even if you grant access to those tables to user Bob, he still gonna access them as Tom.Products and Tom.PriceList
To allow user Bob to access those tables as Products and PriceList, you must create public synonyms. Public synonyms can be created under SYS so log in to your SQL Worksheet as SYS and type the following:
SET SERVEROUTPUT ON
begin
dbms_output.enable(1000000);
for x in ( select table_name from DBA_ALL_TABLES where owner='TOM' )
loop
dbms_output.put_line('CREATE OR REPLACE PUBLIC SYNONYM ' x.table_name ' FOR Tom.' x.table_name);
EXECUTE IMMEDIATE ('CREATE OR REPLACE PUBLIC SYNONYM ' x.table_name ' FOR Tom.' x.table_name);
end loop;
end;
You will find this piece of source code really useful when you will apply it in a production system with hundreds or thousands of tables.
What it does is apply for every table belonging to user Tom a public synonym which is the name of the table (but ommiting the Tom. part).
Now what you must do is grant access to user Bob on those tables
In the following piece of code i will grant select on a role. It's better to work with roles because they can be easily implemented to a user and then you can customize user rights independently
Here is the source :
SET SERVEROUTPUT ON
begin
dbms_output.enable(1000000);
for x in ( select table_name from DBA_ALL_TABLES where owner='Tom' )
loop
dbms_output.put_line('grant select on ' x.table_name ' to myrole');
execute immediate 'grant select on ' x.table_name ' to myrole';
end loop;
end;
After that, you will assign the role to your user(s) and that's it.
You have granted access to those tables to your second user with a few lines of code. |
Author's note : Thank you all for your supporting letters. You have been a motivation to this web spot's posting. I wish you all the
very best for you and your families for the new year.
ssd固態硬碟 外接式硬碟 餐飲設備 製冰機 洗碗機 咖啡機 冷凍冷藏冰箱 蒸烤箱 External Storage Solid State Drives 關島婚禮 關島蜜月 花蓮民宿 彈簧床 床墊 獨立筒床墊 乳膠床墊 床墊工廠 巴里島旅遊 巴里島機票 記憶體 情趣用品 情趣用品 USB Flash Drives Memory Cards