Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: cloning a user ! ( Oracle 7 on SCO UNIX)

Re: cloning a user ! ( Oracle 7 on SCO UNIX)

From: Jurij Modic <jmodic_at_src.si>
Date: Mon, 28 Sep 1998 08:24:24 GMT
Message-ID: <360f3f2c.3139135@news.siol.net>


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.

  1. Privileges: Your new user will have to have a set of system and object privileges, needed to access the needed db objects, i.e. SELECT, UPDATE, DELETE, INSERT, .....
You'll have to decide if you'll let your user to perform actions on *any* object in the database, not only on the application's objects. In this case you can simply grant a set of system privileges (perhaps via roles) to your new user (e.g. "GRANT INSERT ANY TABLE TO new_user", "GRANT SELECT ANY SEQUENCE TO ...",...). Personaly I would not recomend this strategy as you are leaving your users to much freedom over any database object.

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)



The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Sep 28 1998 - 03:24:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US