Skip to Main Content
Forums

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Unit test : what are the permissions needed?

Laurent SchneiderDec 22 2009 — edited Mar 1 2012
When I try to create a repository I am getting :

Selected connection does not have basic permissions.
You will now be prompted for connection info to grant needed permission

I do not have superuser privilege, and I will never get it, as it is against the company policy to give powerful grants to the developers.

How can I use unit testing then??? Is there an SQL file that I can give to my database administrator to run against the DB?

Thanks
Laurent
This post has been answered by Brian Jeffries-Oracle on Dec 22 2009
Jump to Answer

Comments

Brian Jeffries-Oracle
Hi Laurent,

To create the unit test repository, the repository user needs:
CONNECT, RESOURCE, CREATE VIEW

The "SYS" user can be any user that can connect as SYSDBA
this is used to execute the following:

grant select on dba_roles to repository_user;
grant select on dba_role_privs to repository_user;

create role UT_REPO_ADMINISTRATOR;
create role UT_REPO_USER;
grant create public synonym,drop public synonym to UT_REPO_ADMINISTRATOR;
grant select on dba_role_privs to UT_REPO_USER;
grant select on dba_role_privs to UT_REPO_ADMINISTRATOR;
grant select on dba_roles to UT_REPO_ADMINISTRATOR;
grant select on dba_roles to UT_REPO_USER;
grant select on dba_tab_privs to UT_REPO_USER; --3.1
grant select on dba_tab_privs to UT_REPO_ADMINISTATOR; --3.1
grant execute on dbms_lock to UT_REPO_USER; --3.1
grant execute on dbms_lock to UT_REPO_ADMINISTATOR; --3.1
grant ut_repo_user to UT_REPO_ADMINISTRATOR with admin option;
grant UT_REPO_ADMINISTRATOR to repository_user with admin option;

Alternatively, your dba could do the previous block (create role ... grant UT_REPO_ADMIN...) for repository_user and then you could create one without being prompted for the "SYS" connection.

Brian
SQL Developer Team.

Edited by: bjeffrie on Mar 1, 2012 11:55 AM to update with new privilege requirements
1 person found this helpful
Laurent Schneider
Hi Brian,
I will ask my dba to create the user above.

I will update this thread later if needed

Thank you for your prompt answer,
Laurent
Laurent Schneider
but create public synonym is quite a powerful privilege to give... Can you tell me why is this needed and which synonyms will be created?
Brian Jeffries-Oracle
General answer is that create public synonym is only used in the case of setting a repository as shared. Doing so creates public synonyms for the unit test tables, views, and copy procedure.

Going through final code to see (& test) what the minimum requirements are for using individual/private repositories and will get back to you.

Brian Jeffries
SQL Developer Team
Brian Jeffries-Oracle
Answer
Hi Laurent,

Here is the minimum required to get past the internal checks and be able to use unit testing.

Once your DBA does this:

-- RUN ONCE TO SET UP REQUIRED UT ROLES WITH MINIMUM PRIVILEGES:

-- DROP ROLE UT_REPO_USER ;
-- DROP ROLE UT_REPO_ADMINISTRATOR ;
-- DROP USER MIN_PRIV_UT_REPO WITH CASCADE;

CREATE ROLE UT_REPO_USER ;
GRANT SELECT ON DBA_TAB_PRIVS TO UT_REPO_USER ; -- 3.1
GRANT EXECUTE ON DBMS_LOCK TO UT_REPO_USER ; -- 3.1
CREATE ROLE UT_REPO_ADMINISTRATOR ;
GRANT UT_REPO_USER TO UT_REPO_ADMINISTRATOR ; --3.1

-- EDIT/RUN FOR EACH USER/UNIT TEST REPOSITORY DESIRED:
-- Note: Replace MIN_PRIV_UT_REPO, USERS, TEMP as desired for your situation

CREATE USER MIN_PRIV_UT_REPO IDENTIFIED BY MIN_PRIV_UT_REPO
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

GRANT RESOURCE TO MIN_PRIV_UT_REPO ;
GRANT CONNECT TO MIN_PRIV_UT_REPO ;
GRANT CREATE VIEW TO MIN_PRIV_UT_REPO ;
GRANT SELECT ON DBA_ROLES TO MIN_PRIV_UT_REPO ;
GRANT SELECT ON DBA_ROLE_PRIVS TO MIN_PRIV_UT_REPO ;
GRANT UT_REPO_ADMINISTRATOR TO MIN_PRIV_UT_REPO ;


Then in to SQLDeveloper, select Tools->Unit Test->Select Current Repository, create/select a connection for MIN_PRIV_UT_REPO, and press "Yes" when asked if you want to create one.

Note that with the UT roles set up 'empty' like this, the menu options for managing a shared repository will be active and appear to work, but won't really do anything

Brian Jeffries
SQL Developer Team

Edited by: bjeffrie on Dec 22, 2009 3:08 PM

Edited by: bjeffrie on Dec 22, 2009 4:01 PM

Edited by: bjeffrie on Mar 1, 2012 11:43 AM to update with new privilege requirements for 3.1
Marked as Answer by Laurent Schneider · Sep 27 2020
Laurent Schneider
2 weeks later, I go the permission! I suppose you could optimize this process by removing the need of a role for dedicated repository. Or maybe create the role as a default role in Oracle 12...
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 2 2010
Added on Dec 22 2009
#install, #sql-developer
6 comments
5,844 views