Up front, allow me to point out that I realize this question might be better suited being directed to Quest Software, publishers of TOAD...but, I'll pose it here first.
Our application connections to an Oracle database (good, otherwise, this is in the wrong newsgroup...grin). The Schema owner is used to create all the tables, procedures, functions, etc. In the cause of security, we would like to create a user that has access to those opjects in the schema, but not to the degree that the schema owner does. I have created a user while connected to the database as the shema owner. I've created two distict roles and BASIC and a FULL access role for this new user. Once connected, I can set the FULL role. I can alter session to set the Schema equal to the one I need to work with, so that I do not need to use fully qualified names (and not rewrite all of the existing PL/SQL and plain SQL). However, when using the TOAD application I can not "see" any of the objects in the schema I want to work with. I can successfully perform selects on those tables, and obtain desc of those tables. But, in the schema browser mode of TOAD, I do not see anything under my application user.
The technical features of TOAD aside, am I missing a fundamental aspect of Oracle? Can only the schema owner "see" those objects, ever? Any users created can only "see" those objects they create, and those objects are part of a schema named like that user. Is this correct?
I wish I could word what I'm trying to ask/clarify better. I'll try the following example:
DATA_OWNER is used to create the database. This user can "see" everything in the schema. APPLICATION_USER is created for our external application to connect to the database. This user is granted roles ROLE_BASIC and ROLE_FULL with the BASIC being default and the FULL being password protected. Once connected, and the FULL role set, this user can not "see" the objects in the DATA_OWNER schema. Is this the way it will always be? I can issue the alter session set current_schema=DATA_OWNER and then not need to issue fully qualified object names, but can not "see" those objects in a list. Or can it?
If you've not become confused by question, or my attempt to clarify my question, or my example, and can offer some insight or help, I would greatly appreciate it. Our goal is to provide our client base with the ability to use the application user in it's basic role for any adhoc reporting that they would like to perform on their data outside of the application.
Thank you again for any help any of you can provide.
Are you granting rights on these tables to the other users or roles? There's no reason TOAD shouldn't see the tables if you have "GRANT SELECT ON TABLE_NAME TO ROLE_FULL".
-- Remove NOSPAM woven into e-mail address to reply directly.
When you grant privileges on objects to another "user" in Oracle the user must qualify the object name with the user name in order to perform a query unless you create public synonyms for those objects. The granting of privileges, whether through a role or direct grants, will allow the user to perform that operation on the object but they will still need to qualify with the object owner's userid. The only way around this is to create a public synonym. That way the user does not need to qualify the object with the owner name.
Hope that helps
"David Stidham" <DStid...@msn.com> wrote in message
> Up front, allow me to point out that I realize this question might be better > suited being directed to Quest Software, publishers of TOAD...but, I'll pose > it here first.
> Our application connections to an Oracle database (good, otherwise, this is > in the wrong newsgroup...grin). The Schema owner is used to create all the > tables, procedures, functions, etc. In the cause of security, we would like > to create a user that has access to those opjects in the schema, but not to > the degree that the schema owner does. I have created a user while > connected to the database as the shema owner. I've created two distict > roles and BASIC and a FULL access role for this new user. Once connected, I > can set the FULL role. I can alter session to set the Schema equal to the > one I need to work with, so that I do not need to use fully qualified names > (and not rewrite all of the existing PL/SQL and plain SQL). However, when > using the TOAD application I can not "see" any of the objects in the schema > I want to work with. I can successfully perform selects on those tables, > and obtain desc of those tables. But, in the schema browser mode of TOAD, I > do not see anything under my application user.
> The technical features of TOAD aside, am I missing a fundamental aspect of > Oracle? Can only the schema owner "see" those objects, ever? Any users > created can only "see" those objects they create, and those objects are part > of a schema named like that user. Is this correct?
> I wish I could word what I'm trying to ask/clarify better. I'll try the > following example:
> DATA_OWNER is used to create the database. This user can "see" everything > in the schema. > APPLICATION_USER is created for our external application to connect to the > database. This user is granted roles ROLE_BASIC and ROLE_FULL with the > BASIC being default and the FULL being password protected. Once connected, > and the FULL role set, this user can not "see" the objects in the DATA_OWNER > schema. Is this the way it will always be? I can issue the alter session > set current_schema=DATA_OWNER and then not need to issue fully qualified > object names, but can not "see" those objects in a list. Or can it?
> If you've not become confused by question, or my attempt to clarify my > question, or my example, and can offer some insight or help, I would greatly > appreciate it. Our goal is to provide our client base with the ability to > use the application user in it's basic role for any adhoc reporting that > they would like to perform on their data outside of the application.
> Thank you again for any help any of you can provide.
If I understand your question correctly, you have this user DATA_OWNER who owns tables, and you granted rights on those tables to another user. Now you expect to see those tables under this other user in the schema browser. That does not work, because in the schema browser you only see the tables OWNED by the user. To get a list of the tables that a user has rights on, you can select TABLE_NAME from ALL_TABLES when logged in as that user. This lists all tables that are visible for that user. When selecting TABLE_NAME from USER_TABLES you see all tables OWNED by the user you are logged on with.
If you want to be able to select from those tables without having to type DATAOWNER.TABLE_NAME in the query, you should create synonyms for the tables in the schema of this other user (create synonym table_name for data_owner.table_name). Or , if there are alot of users who need this, you could create public synonyms for this. In this way, any user who has rights to see the table can just use TABLE_NAME to select from it. (create PUBLIC synonym table_name for data_owner.table_name)
I don't know TOAD that well, but it sounds to me that not very much is amiss.
You say the user you create can select from the objects, but just can't see the opjects in TOAD. Sounds like a limitation in TOAD.
Any User to whom a privilege to another User's object is granted will see that object in the ALL_TABLES (or equivalent) view. They won't see it in the USER_TABLES view, though. USER_TABLES shows you what tables you actually own. ALL_TABLES shows you what objects you own AND what objects you have been granted privileges to. If your application user has been granted privileges to the schema tables, I would expect you to be able to log in as that User and do 'select * from all_tables' and see all those tables. If TOAD then refuses to show you those tables, it's a TOAD problem.
On the other hand, you claim that with the BASIC role set, all is visible, but with the FULL role set, *not* all is visible. Given that setting one role disables all other roles, the immediate question is exactly what set of privileges is granted in BASIC, and what is in FULL. Because there is clearly something in BASIC, which is disabled by enabling FULL, which FULL doesn't have. I can't think what it might be, so your best bet is to post the entire set of privileges in each role here.
Regards HJR -- =============================!!============================= The views expressed are my own only, and definitely NOT those of Oracle Corporation =============================!!=============================
"David Stidham" <DStid...@msn.com> wrote in message
> Up front, allow me to point out that I realize this question might be better > suited being directed to Quest Software, publishers of TOAD...but, I'll pose > it here first.
> Our application connections to an Oracle database (good, otherwise, this is > in the wrong newsgroup...grin). The Schema owner is used to create all the > tables, procedures, functions, etc. In the cause of security, we would like > to create a user that has access to those opjects in the schema, but not to > the degree that the schema owner does. I have created a user while > connected to the database as the shema owner. I've created two distict > roles and BASIC and a FULL access role for this new user. Once connected, I > can set the FULL role. I can alter session to set the Schema equal to the > one I need to work with, so that I do not need to use fully qualified names > (and not rewrite all of the existing PL/SQL and plain SQL). However, when > using the TOAD application I can not "see" any of the objects in the schema > I want to work with. I can successfully perform selects on those tables, > and obtain desc of those tables. But, in the schema browser mode of TOAD, I > do not see anything under my application user.
> The technical features of TOAD aside, am I missing a fundamental aspect of > Oracle? Can only the schema owner "see" those objects, ever? Any users > created can only "see" those objects they create, and those objects are part > of a schema named like that user. Is this correct?
> I wish I could word what I'm trying to ask/clarify better. I'll try the > following example:
> DATA_OWNER is used to create the database. This user can "see" everything > in the schema. > APPLICATION_USER is created for our external application to connect to the > database. This user is granted roles ROLE_BASIC and ROLE_FULL with the > BASIC being default and the FULL being password protected. Once connected, > and the FULL role set, this user can not "see" the objects in the DATA_OWNER > schema. Is this the way it will always be? I can issue the alter session > set current_schema=DATA_OWNER and then not need to issue fully qualified > object names, but can not "see" those objects in a list. Or can it?
> If you've not become confused by question, or my attempt to clarify my > question, or my example, and can offer some insight or help, I would greatly > appreciate it. Our goal is to provide our client base with the ability to > use the application user in it's basic role for any adhoc reporting that > they would like to perform on their data outside of the application.
> Thank you again for any help any of you can provide.
Thank you all for your assistance. You've confirmed what I have been "seeing" in my attempts to "see" those tables and other objects owned by the DATA_OWNER user.
There is one other way to avoid using fully qualified names that no one has mentioned; ALTER SESSION SET SCHEMA=new_schema_name;
This allows the alternate user to then reference the tables and objects of the new_schema_name without fully qualifying them or having to create public synonyms for them. Thought I would share that tidbit in the event that there were any readers that were unaware of this information.
> Thank you all for your assistance. You've confirmed what I have been > "seeing" in my attempts to "see" those tables and other objects owned by the > DATA_OWNER user.
> There is one other way to avoid using fully qualified names that no one has > mentioned; > ALTER SESSION SET SCHEMA=new_schema_name;
> This allows the alternate user to then reference the tables and objects of > the new_schema_name without fully qualifying them or having to create public > synonyms for them. Thought I would share that tidbit in the event that > there were any readers that were unaware of this information.
Let me understand your question first. In Oracle data security is given by two way i.e one with giving priviledges on object to user(schema) or to the ROLE. Role is collection of priviledges on objects for better maintance of object priviledges. Now what you need is dynamic enabling or disabling of USER ROLE in a concurrent transaction depending upon condition. For that Oracle provides SYSTEM CONTROL statement SET ROLE. In a transaction if u have two ROLE like DATA_OWNER and APPLICATION_OWNER. While crearting APPLICATION_OWNER give password or use following statement to alter role property.
ALTER ROLE APPLICATION_USER IDENTIFIED BY david; (Here david is password)
Then issue following statement in your program. It is assumed that user(schema) has granted DATA_OWNER role initially. To change role DATA_OWNER for given user, pl. use following statement ..
SET ROLE APPLICATION_OWNER IDENTIFIED BY david;
and then u can access all priviledges that u specify in APPLICATION_OWNER role.
> Up front, allow me to point out that I realize this question might be better > suited being directed to Quest Software, publishers of TOAD...but, I'll pose > it here first.
> Our application connections to an Oracle database (good, otherwise, this is > in the wrong newsgroup...grin). The Schema owner is used to create all the > tables, procedures, functions, etc. In the cause of security, we would like > to create a user that has access to those opjects in the schema, but not to > the degree that the schema owner does. I have created a user while > connected to the database as the shema owner. I've created two distict > roles and BASIC and a FULL access role for this new user. Once connected, I > can set the FULL role. I can alter session to set the Schema equal to the > one I need to work with, so that I do not need to use fully qualified names > (and not rewrite all of the existing PL/SQL and plain SQL). However, when > using the TOAD application I can not "see" any of the objects in the schema > I want to work with. I can successfully perform selects on those tables, > and obtain desc of those tables. But, in the schema browser mode of TOAD, I > do not see anything under my application user.
> The technical features of TOAD aside, am I missing a fundamental aspect of > Oracle? Can only the schema owner "see" those objects, ever? Any users > created can only "see" those objects they create, and those objects are part > of a schema named like that user. Is this correct?
> I wish I could word what I'm trying to ask/clarify better. I'll try the > following example:
> DATA_OWNER is used to create the database. This user can "see" everything > in the schema. > APPLICATION_USER is created for our external application to connect to the > database. This user is granted roles ROLE_BASIC and ROLE_FULL with the > BASIC being default and the FULL being password protected. Once connected, > and the FULL role set, this user can not "see" the objects in the DATA_OWNER > schema. Is this the way it will always be? I can issue the alter session > set current_schema=DATA_OWNER and then not need to issue fully qualified > object names, but can not "see" those objects in a list. Or can it?
> If you've not become confused by question, or my attempt to clarify my > question, or my example, and can offer some insight or help, I would greatly > appreciate it. Our goal is to provide our client base with the ability to > use the application user in it's basic role for any adhoc reporting that > they would like to perform on their data outside of the application.
> Thank you again for any help any of you can provide.
|
