Toad World® Forums

Test permissions


#1

I am just starting to play with the code tester, hoping to include it in my regular development process. Here is the first snag I ran into.

The database security at my company follows the following model in development.

All developers log in to the database as themselves.
All developers writing pl/sql have permissions to compile code into any schema.
Passwords for application users are not published to the developers.

I created my first test for a package in schema x, and because I do not have execute granted on that package to my user directly, the test case would not compile in my schema.

I then change the owner of the test package in the edit test screen, and the test compiled successfully, but now I do not have permission to edit the test through the GUI, even though my user has permission to drop/modify the package in the database.

I am guessing that the security model for editing the tests is a simple, only the user owning the test case may edit it.

Is there a way around this?


#2

Whew. I had to read that description several times to sort out what is going on!

Looks like we should loosen up the rules for editing, yes. First, I would like to make sure I understand your situation fully.

You write: “I created my first test for a package in schema x, and because I do nothave execute granted on that package to my user directly, the test casewould not compile in my schema.”

When you say “to my user”, what is this user you are talking about? Is this the developer schema or an end user schema? Do you not run code out of the developer schemas? Only execute code from the end user schema?

You also write: “my user has permission to drop/modify the package”. I am familiar with granting execute on a package. Can you also grant edit privileges on a package?

I don’t want to let anyone who can run the package to be able to edit it. I believe I need to explore further what other privs can be set on a package for a particular schema.


#3

I reread my first post, and you are right, it is a little confusing.

Ideally the security model would be as follows:

if you have permission to execute the test package, you are allowed to run the test.

if you have permission to compile the test package, you are allowed to edit the test.

I have a role on the development database that includes the “execute any procedure” system privilege. I also have the “create any procedure” system privilege.

So, I am allowed to compile to any schema and execute any code compiled to any schema. I cannot compile code that tests a package in another schema in my schema because I cannot execute the procedure I want to test from plsql. The reason is role permissions do not apply within plsql.

I just added that to try and clarify more. My office mate said that it sounded a little twisted, so I might have just confused more.


#4

Thanks, this is very helpful and makes sense.

I will put this into our ER list for the first patch release. We cannot make these changes for the commercial release.