I got handed this requirement and I don't think there's a good way to solve it.
They have a user.. say USERA who has some tables, data, and packages. They want another user, say USERB who has select privilege on that data (easy enough).. the ability to make their own packages (easy enough).. but also want to give USERA the ability to edit USERB's packages. I think this is impossible without giving an ANY privilege. I think it best if USERA just has USERB's password. Any other solution?
Thanks.
... I think it best if USERA just has USERB's password. ...
no, that would be a pretty bad idea.
you can allow A to assume B's identity
ops$tkyte%ORA11GR2> create user a identified by a;
User created.
ops$tkyte%ORA11GR2> grant create session, create procedure to a;
Grant succeeded.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create user b identified by b default tablespace users quota unlimited on users;
User created.
ops$tkyte%ORA11GR2> grant create session, create table, create procedure to b;
Grant succeeded.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> alter user b grant connect through a;
User altered.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> connect b/b
Connected.
b%ORA11GR2> create table t ( x int );
Table created.
b%ORA11GR2> create procedure p as begin null; end;
2 /
Procedure created.
b%ORA11GR2>
b%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> show user
USER is "A"
a%ORA11GR2> connect a[b]/a
Connected.
b%ORA11GR2> show user
USER is "B"
b%ORA11GR2> create or replace procedure p as begin dbms_output.put_line( 'new stuff' ); end;
2 /
Procedure created.
b%ORA11GR2> create table t2 ( x int );
Table created.
b%ORA11GR2> grant execute on p to a;
Grant succeeded.
b%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> exec b.p
new stuff
PL/SQL procedure successfully completed.
so user A uses their own password to "become" B.
but that would give them the ability to do more than just edit B's packages - it would allow them to do anything B can do (as shown by creating a table)
we can restrict that somewhat by using roles - but not entirely (since some things will be granted directly to B)
ops$tkyte%ORA11GR2> create role b_role1;
Role created.
ops$tkyte%ORA11GR2> create role b_role2;
Role created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> grant create procedure, create session to b_role1;
Grant succeeded.
ops$tkyte%ORA11GR2> grant create table to b_role2;
Grant succeeded.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create user a identified by a;
User created.
ops$tkyte%ORA11GR2> grant create session, create procedure to a;
Grant succeeded.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create user b identified by b default tablespace users quota unlimited on users;
User created.
ops$tkyte%ORA11GR2> grant b_role1 to b;
Grant succeeded.
ops$tkyte%ORA11GR2> grant b_role2 to b;
Grant succeeded.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> alter user b grant connect through a with role b_role1;
User altered.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> connect b/b
Connected.
b%ORA11GR2> create table t ( x int );
Table created.
b%ORA11GR2> create procedure p as begin null; end;
2 /
Procedure created.
b%ORA11GR2>
b%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> show user
USER is "A"
a%ORA11GR2> connect a[b]/a
Connected.
b%ORA11GR2> show user
USER is "B"
b%ORA11GR2> create or replace procedure p as begin dbms_output.put_line( 'new stuff' ); end;
2 /
Procedure created.
b%ORA11GR2> create table t2 ( x int );
create table t2 ( x int )
*
ERROR at line 1:
ORA-01031: insufficient privileges
b%ORA11GR2> grant execute on p to a;
Grant succeeded.
b%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> exec b.p
new stuff
PL/SQL procedure successfully completed.
here we made it so that A only has create session and create procedure when connected as B.