Home » RDBMS Server » Security » creating role with password
creating role with password [message #115711] Wed, 13 April 2005 15:52 Go to next message
Hina
Messages: 51
Registered: April 2004
Member

Would be any issue creating oracle roles with password ?.

We have an application where a role or multiple roles can be assigned to the user based on their functionality. So do we need to enable and provide password everytime ?

Please explain me in details. Also, what would be the best approach to create a role with, or without password

Thanks

Re: creating role with password [message #115808 is a reply to message #115711] Thu, 14 April 2005 09:37 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
If the user should always have a set of one or more roles anytime they log into the database, then you may not really need passwords. They log into the db, they already have a set of roles they need to perform their duties.

Or, you might even want to consider secure application roles:

-- secure_role_example.sql (Scot Martin)
-- Feb 2005
-- 10.1.0.2 or 10.1.0.3

-- Run this logged in as the mydba user, who has dba role.

-- This example creates a secure application role, which is a role that is
-- tied to and can only be set by a specific invokers rights package.  This
-- allows you to procedurally enable a role for a user's session based on
-- criteria you define, and have that role contain all the privs needed to
-- execute a set of packages to run a particular application.

spool secure_role_example.txt;

set echo on;

connect mydba/orcl;

create role secure_role identified using mydba.secure_role_pkg;

create table secure_table (a int, b int);

create package secure_app as
	procedure do_stuff;
	procedure display_stuff;
end;
/
show errors

create package body secure_app as
	procedure do_stuff is
	begin
		insert into secure_table values (1, 1);
		commit;
	end;

	procedure display_stuff is
		l_count number;
	begin
		select count(*) into l_count from secure_table;
		dbms_output.put_line(l_count);
	end;
end;
/
show errors

grant execute on secure_app to secure_role;


create package secure_role_pkg authid current_user as
	procedure enable_role;
	procedure disable_role;
end;
/
show errors

create package body secure_role_pkg as
	procedure enable_role is
	begin
		-- put whatever security checks here, possibly using sys_context
		if 1 = 1 then
			dbms_session.set_role('secure_role');
		end if;
	end;

	procedure disable_role is
	begin
		-- all is really all but password protected and secure app role
		-- although docs are not very clear on this point
		dbms_session.set_role('all');
	end;
end;
/
show errors

grant execute on secure_role_pkg to public;

create user a identified by a;
grant create session to a;

connect a/a;


-- nothing has been enabled yet and user doesn't have any roles
select * from session_roles;
select username, granted_role, default_role from user_role_privs;

-- this should error, because don't have the role
exec mydba.secure_app.do_stuff;

exec mydba.secure_role_pkg.enable_role;

-- now the role shows up
select * from session_roles;

-- still won't show it because role not granted to user in normal way
select username, granted_role, default_role from user_role_privs;

-- now these will work
exec mydba.secure_app.do_stuff;
exec mydba.secure_app.display_stuff;

exec mydba.secure_role_pkg.disable_role;

-- role is gone
select * from session_roles;

-- so this errors again
exec mydba.secure_app.do_stuff;

-- cleanup
connect mydba/orcl;
drop user a;
drop table secure_table;
drop package secure_app;
drop role secure_role;
drop package secure_role_pkg;

spool off;


Re: creating role with password [message #115813 is a reply to message #115711] Thu, 14 April 2005 09:50 Go to previous message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
Hi,

You can use "Secure Application Roles" if you don't want to authenticate with a password. This mechanism restricts the enabling of secure roles to authorised applications.

For more details, read the "Database Security" chapter of the Oracle Concepts Guide. The Database Security Guide provides some examples.

Best regards.

Frank
Previous Topic: Audit Trail?
Next Topic: Update role
Goto Forum:
  


Current Time: Sat Aug 15 09:27:12 CDT 2020