[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2581] trunk
- Subject: SF.net SVN: ledger-smb:[2581] trunk
- From: ..hidden..
- Date: Wed, 29 Apr 2009 01:28:28 +0000
Revision: 2581
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2581&view=rev
Author: einhverfr
Date: 2009-04-29 01:28:28 +0000 (Wed, 29 Apr 2009)
Log Message:
-----------
Session/password expiration now works in theory (needs more testing), some more user management fixes
Modified Paths:
--------------
trunk/LedgerSMB/Form.pm
trunk/LedgerSMB.pm
trunk/UI/lib/ui-header.html
trunk/sql/Pg-database.sql
trunk/sql/modules/Session.sql
trunk/sql/modules/admin.sql
Modified: trunk/LedgerSMB/Form.pm
===================================================================
--- trunk/LedgerSMB/Form.pm 2009-04-29 00:44:39 UTC (rev 2580)
+++ trunk/LedgerSMB/Form.pm 2009-04-29 01:28:28 UTC (rev 2581)
@@ -500,6 +500,12 @@
( $self->{title} )
? "$self->{title} - $self->{titlebar}"
: $self->{titlebar};
+ if ($self->{warn_expire}){
+ $headeradd .= qq|
+ <script type="text/javascript" language="JavaScript">
+ document.alert('Warning: Your password will expire in $self->{pw_expires_in});
+ </script>
+ }
print qq|Content-Type: text/html; charset=utf-8\n\n
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
@@ -1198,6 +1204,16 @@
while (my @roles = $sth->fetchrow_array){
push @{$self->{_roles}}, $roles[0];
}
+
+ $sth->prepare('SELECT check_expiration()');
+ $sth->execute;
+ ($self->{warn_expire}) = $sth->fetchrow_array;
+ if ($self->{warn_expire}){
+ $sth->prepare('SELECT user__check_my_expiration()');
+ $sth->execute;
+ ($self->{pw_expires}) = $sth->fetchrow_array;
+ }
+
$sth->finish();
}
Modified: trunk/LedgerSMB.pm
===================================================================
--- trunk/LedgerSMB.pm 2009-04-29 00:44:39 UTC (rev 2580)
+++ trunk/LedgerSMB.pm 2009-04-29 01:28:28 UTC (rev 2581)
@@ -750,11 +750,22 @@
SELECT value FROM defaults
WHERE setting_key = 'role_prefix'");
$sth->execute;
+
+
($self->{_role_prefix}) = $sth->fetchrow_array;
if ($dbversion ne $self->{dbversion}){
$self->error("Database is not the expected version. Was $dbversion, expected $self->{dbversion}");
}
+ $sth->prepare('SELECT check_expiration()');
+ $sth->execute;
+ ($self->{warn_expire}) = $sth->fetchrow_array;
+
+ if ($self->{warn_expire}){
+ $sth->prepare('SELECT user__check_my_expiration()');
+ $sth->execute;
+ ($self->{pw_expires}) = $sth->fetchrow_array;
+ }
my $query = "SELECT t.extends,
Modified: trunk/UI/lib/ui-header.html
===================================================================
--- trunk/UI/lib/ui-header.html 2009-04-29 00:44:39 UTC (rev 2580)
+++ trunk/UI/lib/ui-header.html 2009-04-29 01:28:28 UTC (rev 2581)
@@ -22,8 +22,12 @@
<script type="text/javascript" language="JavaScript" src="<?lsmb s ?>" ></script>
<?lsmb END ?>
+ <?lsmb IF warn_expire ?>
+ <script type="text/javascript" language="JavaScript">
+ document.alert(<?lsmb text('Warning: Your password will expire in [_1]', pw_expires_in)?>);
+ </script>
+ <?lsmb END ?>
-
<meta http-equiv="content-type" content="text/html; charset=UTF-8" />
<meta name="robots" content="noindex,nofollow" />
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2009-04-29 00:44:39 UTC (rev 2580)
+++ trunk/sql/Pg-database.sql 2009-04-29 01:28:28 UTC (rev 2581)
@@ -65,6 +65,7 @@
CREATE TABLE users (
id serial UNIQUE,
username varchar(30) primary key,
+ notify_password interval not null default '7 days',
entity_id int not null references entity(id) on delete cascade
);
Modified: trunk/sql/modules/Session.sql
===================================================================
--- trunk/sql/modules/Session.sql 2009-04-29 00:44:39 UTC (rev 2580)
+++ trunk/sql/modules/Session.sql 2009-04-29 01:28:28 UTC (rev 2581)
@@ -16,6 +16,32 @@
END;
$$ language plpgsql;
+CREATE OR REPLACE FUNCTION check_expiration() RETURNS bool AS
+$$
+DECLARE test_result BOOL;
+ expires_in interval;
+ notify_again interval;
+BEGIN
+ expires_in := user__check_my_expiration();
+
+ SELECT expires_in < notify_password INTO test_result
+ FROM users WHERE username = SESSION_USER;
+
+ IF test_result THEN
+ IF expires_in < '1 week' THEN
+ notify_again := '1 hour';
+ ELSE
+ notify_again := '1 day';
+ END IF;
+
+ UPDATE users
+ SET notify_password = expires_in - notify_again
+ WHERE username = SESSION_USER;
+ END IF;
+ RETURN test_result;
+END;
+$$ LANGUAGE PLPGSQL SECURITY DEFINER; -- run by public, but no input from user.
+
CREATE OR REPLACE FUNCTION form_open(in_session_id int)
RETURNS INT AS
$$
Modified: trunk/sql/modules/admin.sql
===================================================================
--- trunk/sql/modules/admin.sql 2009-04-29 00:44:39 UTC (rev 2580)
+++ trunk/sql/modules/admin.sql 2009-04-29 01:28:28 UTC (rev 2581)
@@ -7,7 +7,6 @@
--
-- -CT
-begin;
create table lsmb_roles (
@@ -110,7 +109,7 @@
$$ language 'plpgsql' SECURITY DEFINER;
-REVOKE EXECUTE ON admin__add_function_to_group(TEXT, TEXT) FROM PUBIC;
+REVOKE EXECUTE ON FUNCTION admin__add_function_to_group(TEXT, TEXT) FROM PUBLIC;
CREATE OR REPLACE FUNCTION admin__remove_function_from_group(in_func TEXT, in_role TEXT) returns INT AS $$
@@ -285,11 +284,12 @@
returns int as
$$
DECLARE
- t_expires timestamp without timezone;
+ t_expires timestamp;
BEGIN
SELECT now() + (value::numeric::text || ' days')::interval INTO t_expires
FROM defaults WHERE setting_key = password_duration;
+ UPDATE users SET notify_password = DEFAULT where username = SESSION_USER;
EXECUTE 'ALTER USER ' || quote_ident(SESSION_USER) ||
' with ENCRYPTED password ' || quote_literal(in_new_password);
@@ -297,6 +297,7 @@
IF t_expires IS NOT NULL THEN
EXECUTE 'ALTER USER ' || quote_ident(SESSION_USER) ||
' VALID UNTIL '|| quote_literal(t_expires);
+ END IF;
return 1;
END;
$$ language plpgsql security definer;
@@ -344,10 +345,9 @@
-- Finally, issue the create user statement
- stmt := 'CREATE USER ' || quote_ident( in_username ) ||
+ execute 'CREATE USER ' || quote_ident( in_username ) ||
' WITH ENCRYPTED PASSWORD ' || quote_literal (in_password)
- 'valid until now() + ''1 day''::interval';
- execute stmt;
+ || $e$ valid until now() + '1 day'::interval $e$;
return v_user_id ;
@@ -355,11 +355,11 @@
-- update cycle
- stmt := ' alter user '|| quote_ident(in_username) ||
+ execute ' alter user '|| quote_ident(in_username) ||
' with encrypted password '
|| quote_literal(in_password) ||
- 'valid until now() + ''1 day''::interval';
- execute stmt;
+ $e$ valid until now()::timezone + '1 day'::interval $e$;
+
return a_user.id;
@@ -541,7 +541,7 @@
$$ language sql;
-create or replace function admin__get_roles () returns setof text as $$
+create or replace function admin__get_roles () returns setof pg_roles as $$
DECLARE
v_rol record;
t_dbname text;
@@ -554,10 +554,10 @@
pg_roles
where
rolname ~ ('^lsmb_' || t_dbname || '__')
- and rolcanlogin is false;
+ and rolcanlogin is false
order by rolname ASC
LOOP
- RETURN NEXT v_rol.rolname;
+ RETURN NEXT v_rol;
END LOOP;
END;
$$ language plpgsql;
@@ -578,4 +578,3 @@
END;
$$ language plpgsql;
-commit;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.