| Accessing objects in SYSTEM schema [message #2676] |
Tue, 06 August 2002 06:27  |
Mark Grimshaw
Messages: 73
Registered: June 2002
|
Member |
|
|
Hi,
I have a problem accesing db objects that I have created whilst working under the username SYSTEM. I did the following under system: -
CREATE USER Jim identified by password;
CREATE ROLE DataManager;
GRANT EXECUTE ON TestSecurityAccess TO DataManager;
GRANT DataManager TO Jim;
GRANT CREATE SESSION TO Jim;
I then logged in as Jim and found that the Package named TestSecurityAccess which I had created was not
recognised. Can anyone help?
Mark
|
|
|
|
|
|
| Re: Accessing objects in SYSTEM schema [message #2685 is a reply to message #2676] |
Tue, 06 August 2002 09:34  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
First off, Mark, I would highly recommend that you do not create your own objects in the SYSTEM schema. That schema (and SYS) should be reserved for built-in data dictionary objects. Any objects you create should really be in another schema.
But, as far as how to refer to objects in other schemas without having to specify the prefix, you would use either public or private synonyms to handle this.
|
|
|
|
| Re: Accessing objects in SYSTEM schema [message #2698 is a reply to message #2676] |
Wed, 07 August 2002 00:50 |
Mark Grimshaw
Messages: 73
Registered: June 2002
|
Member |
|
|
Thanks, I will take your advice regarding the schema issue - I will probbaly create a special user and create the tables and other database objects there. However, I now seem to be having prblems with these synonyms that you mentioned. I did the following:-
CREATE SYNONYM TestSynonym FOR MyPackage;
I then logged in as Jim who has already been granted EXECUTE privileges through a role DataManager to execute any procedure in MyPackage. After logging in as Jim to Sql Plus and setting the role using SET ROLE DataManager when I tried to do a DESC on MyPackage it wasn't recognised. What step do I appear to have missed out as I followed the instructions in the Oracle SqlRef documentation?
Mark
|
|
|
|
| Re: Accessing objects in SYSTEM schema [message #2709 is a reply to message #2676] |
Wed, 07 August 2002 11:19 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
It's hard to know/follow in what schema you are executing these commands, but...
The CREATE SYNONYM command is creating a private synonym in that schema. Only that user can reference that synonym.
If you want JIM to be able to refer to an object in another schema using a synonym, you need to either create a private synonym in JIM, or a PUBLIC synonym in any schema with the privileges to create such.
JIM would then 'desc synonym_name' (or 'desc other_schema.object_name'), but not 'desc object_name'.
|
|
|
|
| Re: Accessing objects in SYSTEM schema [message #2721 is a reply to message #2709] |
Thu, 08 August 2002 00:45 |
Mark Grimshaw
Messages: 73
Registered: June 2002
|
Member |
|
|
Todd,
I did the public thing and this works fine. As a follow up question to that point you made about the location of the application db tables and other objects - do you think it is better to create an 'Application' user and have a schema generated implictly. Alternatively, I have come across references made in the documentation to creating the db objects within the context of a CREATE SCHEMA sql statement.
Mark
|
|
|
|
| Re: Accessing objects in SYSTEM schema [message #2728 is a reply to message #2709] |
Thu, 08 August 2002 08:56 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
You need to create the user anyway (which implicitly creates the schema). The only option is whether you create the objects individually (hopefully through a script) or through the CREATE SCHEMA statement. Personally, I have never used or had a need for that statement.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
