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

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


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
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
-- or

-- 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;
show errors

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

	procedure display_stuff is
		l_count number;
		select count(*) into l_count from secure_table;
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;
show errors

create package body secure_role_pkg as
	procedure enable_role is
		-- put whatever security checks here, possibly using sys_context
		if 1 = 1 then
		end if;

	procedure disable_role is
		-- all is really all but password protected and secure app role
		-- although docs are not very clear on this point
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: 4575
Registered: April 1998
Senior Member

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.

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

Current Time: Thu Sep 21 09:49:00 CDT 2023