Role Maintenance
Background: oracle 9.2.0 running on win2k
I got 2 logical userids
A) OPTELADMIN
--given RESOURCE, CONNECT roles and owned all the user created objects such as tables, indexes, sequences, etc. This userid is shared by 3 physical developers
B) OPTEL
--given CONNECT,CREATE SYNONYM roles and physically shared by 4-5 end application users. End application users manipulate the data via application interface. That is, they logon, logoff, delete, insert, delete data via application, they do not have command line to execute SQL
Everytime, OPTELADMIN had tested the application & is ready to let OPTEL used the new tables, i had to do 2 things
A) Create the synonym, for OPTEL to use the tables,eg,
CREATE SYNONYM Cust FOR opteladmin.Cust;
B) Create the grants, for OPTEL to manipulate the tables, eg,
GRANT SELECT, INSERT, UPDATE, DELETE ON Cust TO optel;
This is very manual. Is there any suggestion how i can automate this?
Software/Hardware used:
Software/Hardware used:
ASKED:
September 22, 2005 2:14 AM
UPDATED:
September 22, 2005 9:47 AM
Answer Wiki:
Granting permissions to a table after it is created is pretty much necessary. You could eliminate creating the synonym if you specify the owner in the application. (ex. select column,... from owner.table;) Keep in mind, if you do this, the application is tied to the owner of the tables; so if the owner changes, you would have to change the applications. I have done it this way for years and never had the need to change the owner even once; but each to their own.
To see all answers submitted to the Answer Wiki: View Answer History.
Should be easy enought to script (if you already haven’t).
Still a step or two depending on how you do it. But very simple steps.
Ken
Should be easy enought to script (if you already haven’t).
Still a step or two depending on how you do it. But very simple steps.
Ken