Home » RDBMS Server » Security » privilege for Alter fixed_date parameter (Oracle 9.2.0.6.0 on Windows/HP-UX/Linux)
privilege for Alter fixed_date parameter [message #427414] Thu, 22 October 2009 08:35 Go to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi,

In one of our databases we need to grant users the right for changing the fixed_date parameter.

As of now we are granting privilege using

grant alter system to <username>;


But since this may cause security issue, I am looking for an alternative.

Is there anyways to grant this specific privilege, possibly without writing any trigger?

Thanks and Regards,
Chetana
Re: privilege for Alter fixed_date parameter [message #427417 is a reply to message #427414] Thu, 22 October 2009 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create a procedure to make the action and grant the privilege to user to execute this procedure.

But why do you need this feature?

Regards
Michel
Re: privilege for Alter fixed_date parameter [message #427422 is a reply to message #427414] Thu, 22 October 2009 09:20 Go to previous messageGo to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi Michel,

It is for testing purpose

The Test team needs to run some batch processes for specfic dates to test the software.

Example would be premium calculation for system takes place on 5th of every month or arrears calculation in 10th of month etc.

I do not know exact details since the sytem is new to me.
But in my past experience such testing was carried out.
(obviously then we used to store a business date in table which we used to alter using normal DMLs)

Thanks for the good option, I will try this

Thanks and Regards,
Chetana
Re: privilege for Alter fixed_date parameter [message #427426 is a reply to message #427414] Thu, 22 October 2009 10:02 Go to previous messageGo to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi Michel,

It works! Many Thanks!!

Here are the related lines

Thanks and Regards,
Chetana

conn sys / as sysdba
grant alter system to system;

conn system/<password>

create or replace procedure change_fixed_date(dt date) as
sqls1 varchar2(30):='dd-mon-yyyy hh24:mi:ss';
begin
execute immediate 'alter session set nls_date_format='''||sqls1 || '''';
execute immediate 'alter system set fixed_date='''||dt||'''';
end;
/

grant execute on change_fixed_date to a;

create user a identified by a;
grant connect to a;
grant execute on change_fixed_date to a;

conn a/a
exec change_fixed_date('01-Oct-2009 01:00:00')
Re: privilege for Alter fixed_date parameter [message #427431 is a reply to message #427426] Thu, 22 October 2009 10:37 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of setting the default date format you could use TO_CHAR function.

Regards
Michel
Previous Topic: Extending SQL Statements (merged)
Next Topic: Manage Large Number of Users
Goto Forum:
  


Current Time: Thu Mar 28 14:42:21 CDT 2024