Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: cloning a user ! ( Oracle 7 on SCO UNIX)
On Sun, 27 Sep 1998 06:23:53 GMT, haddadiank_at_my-dejanews.com wrote:
> There is an application that works fine with a user defined previously by
>its developer. Now, I want to define a new user that act just like the
>original one. But in all the forms I get the ORA-00942 message.
>
>I have no access to developer or sources of the application. What must I do
>for new user to have the same privileges as the original one so it become
>able to do everything the original user does with the application.
There are two different isues here: system and object privileges for the new user and object referencing convention used in the application.
The other, more commonly used principle is to grant adequate privileges only on the objects used by the application. Log in as the owner of the application and grant the needed privileges on each and every object, needed to run the application (e.g. "GRANT INSERT ON table_a TO new_user", "GRANT SELECT ON sequence_x TO new_user", "GRANT EXECUTE ON FUNCTION function_1"....). You also have an option to grant all those privileges to PUBLIC, which means all database users will be able to execute those actions.
In any case, I recomend you to use roles for ease of the security administration.
2. Object referencing convention:
Obviously your application does not use "full" object referencing,
i.e. OWNER_NAME.OBJECT_NAME. For that reason you'll have to create
synonyms for the application objects. Suppose the owner of the
application is user APPL_OWNER and the application need to perform
certan action on table TABLE_A. When your new user has coresponding
privileges on that table, he must reference the table with "full"
notation to be able to perform the actions, e.g. "SELECT * FROM
appl_owner.table.a", otherwise he'll get ORA-942. To avoid this, your
new user have to create a SYNONYM for this table, like "CREATE SYNONYM
table_a FOR appl_owner.table_a". Now he can isue a simple "SELECT *
FROM table_a" as your apllication obviously does.
>- Regards, Kawa Haddadian
HTH,
--
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)