Managing Schema Privileges in Oracle
Hi there,
Wondering if someone can help me out here...
We're in the middle of implementing a new financial system (JDE E1 on Oracle), which will be integrated with our work management application (Maximo). So far, I created a schema for Maximo (MAXIMO) with a mapping table (MAPITM). I created a role FC_MAXIMO with SELECT, INSERT, UPDATE, DELETE privileges on MAPITM. There are 2 roles in JDE (JDEADMIN, JDEUSER), and I added them to FC_MAXIMO. There are also 4 users: JDE, JDEPROXY, JDESRV, TESTDTA and added them to FC_MAXIMO. I created a Public Synonym for the MAPITM (but found out later on the 'Net, it really isn't 'best practice').
This is where I'm a little out of my expertise (I don't have a lot of experience in Oracle...just enough to be dangerous...LOL). The SME (subject matter expert) wants me to create a view of the MAPITM table in the TESTDTA schema. After, he wanted the 2 roles (JDEADMIN, JDEUSER) to have access to the view. I told him I don't think you can assign views to Roles, only to Users (I could be wrong). So, he wanted the users: JDE, JDEPROXY, and JDESRV to have access to the view in TESTDTA. This is where I'm lost.
I'm wondering if I'm on the right path, or just completely out to lunch. What would be 'best practise' in this situation? Is it necessary to use a View? I would like to keep MAXIMO table(s) separate from JDE, but need multiple Roles/Users to access the MAXIMO schema.
Hope this clarifies things, and please let me know if you need additional info/details...
Much appreciated,
Classic
Wondering if someone can help me out here...
We're in the middle of implementing a new financial system (JDE E1 on Oracle), which will be integrated with our work management application (Maximo). So far, I created a schema for Maximo (MAXIMO) with a mapping table (MAPITM). I created a role FC_MAXIMO with SELECT, INSERT, UPDATE, DELETE privileges on MAPITM. There are 2 roles in JDE (JDEADMIN, JDEUSER), and I added them to FC_MAXIMO. There are also 4 users: JDE, JDEPROXY, JDESRV, TESTDTA and added them to FC_MAXIMO. I created a Public Synonym for the MAPITM (but found out later on the 'Net, it really isn't 'best practice').
This is where I'm a little out of my expertise (I don't have a lot of experience in Oracle...just enough to be dangerous...LOL). The SME (subject matter expert) wants me to create a view of the MAPITM table in the TESTDTA schema. After, he wanted the 2 roles (JDEADMIN, JDEUSER) to have access to the view. I told him I don't think you can assign views to Roles, only to Users (I could be wrong). So, he wanted the users: JDE, JDEPROXY, and JDESRV to have access to the view in TESTDTA. This is where I'm lost.
I'm wondering if I'm on the right path, or just completely out to lunch. What would be 'best practise' in this situation? Is it necessary to use a View? I would like to keep MAXIMO table(s) separate from JDE, but need multiple Roles/Users to access the MAXIMO schema.
Hope this clarifies things, and please let me know if you need additional info/details...
Much appreciated,
Classic
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You create a view on a table when you need to mask/hide some of the columns or you need to manipulate columns with functions or other things.
If the view is "select * from MAPITM", I'm not seeing the benefit.
>> I told him I don't think you can assign views to Roles
Anything you can grant to a "user" you can grant to a role. Where you run into problems is when there is stored procedures. Grants cannot come through a role.