SQL.pm 14.9 KB
Newer Older
Francesc Guasch's avatar
Francesc Guasch committed
1
2
3
4
5
package Ravada::Auth::SQL;

use warnings;
use strict;

6
7
8
9
10
11
=head1 NAME

Ravada::Auth::SQL - SQL authentication library for Ravada

=cut

12
13
use Carp qw(carp);

14
use Ravada;
15
use Ravada::Front;
Francesc Guasch's avatar
Francesc Guasch committed
16
use Digest::SHA qw(sha1_hex);
Francesc Guasch's avatar
Francesc Guasch committed
17
18
19
use Hash::Util qw(lock_hash);
use Moose;

Francesc Guasch's avatar
Francesc Guasch committed
20
21
22
use feature qw(signatures);
no warnings "experimental::signatures";

Francesc Guasch's avatar
Francesc Guasch committed
23
24
use vars qw($AUTOLOAD);

25
26
use Data::Dumper;

Francesc Guasch's avatar
Francesc Guasch committed
27
with 'Ravada::Auth::User';
Francesc Guasch's avatar
Francesc Guasch committed
28

Francesc Guasch's avatar
Francesc Guasch committed
29

30
31
32
our $CON;

sub _init_connector {
33
34
35
36
37
38
39
40
    my $connector = shift;

    $CON = \$connector                 if defined $connector;
    return if $CON;

    $CON= \$Ravada::CONNECTOR          if !$CON || !$$CON;
    $CON= \$Ravada::Front::CONNECTOR   if !$CON || !$$CON;

41
    if (!$CON || !$$CON) {
42
43
        my $connector = Ravada::_connect_dbh();
        $CON = \$connector;
44
45
46
    }

    die "Undefined connector"   if !$CON || !$$CON;
47
}
48

Francesc Guasch's avatar
Francesc Guasch committed
49

Francesc Guasch's avatar
Francesc Guasch committed
50
51
52
53
54
55
=head2 BUILD

Internal OO build method

=cut

Francesc Guasch's avatar
Francesc Guasch committed
56
sub BUILD {
57
58
    _init_connector();

Francesc Guasch's avatar
Francesc Guasch committed
59
    my $self = shift;
60
61
62
63
64

    $self->_load_data();

    return $self if !$self->password();

Francesc Guasch's avatar
Francesc Guasch committed
65
66
67
68
69
    die "ERROR: Login failed ".$self->name
        if !$self->login();#$self->name, $self->password);
    return $self;
}

Francesc Guasch's avatar
Francesc Guasch committed
70
71
72
73
74
75
76
77
=head2 search_by_id

Searches a user by its id

    my $user = Ravada::Auth::SQL->search_by_id( $id );

=cut

78
79
80
81
sub search_by_id {
    my $self = shift;
    my $id = shift;
    my $data = _load_data_by_id($id);
82
    return if !keys %$data;
83
84
85
    return Ravada::Auth::SQL->new(name => $data->{name});
}

86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
=head2 list_all_users

Returns a list of all the usernames

=cut

sub list_all_users() {
    my $sth = $$CON->dbh->prepare(
        "SELECT(name) FROM users ORDER BY name"
    );
    $sth->execute;
    my @list;
    while (my $row = $sth->fetchrow) {
        push @list,($row);
    }
    return @list;
}

Francesc Guasch's avatar
Francesc Guasch committed
104
105
106
107
=head2 add_user

Adds a new user in the SQL database. Returns nothing.

108
    Ravada::Auth::SQL::add_user(
109
110
111
112
113
                 name => $user
           , password => $pass
           , is_admin => 0
       , is_temporary => 0
    );
Francesc Guasch's avatar
Francesc Guasch committed
114
115
116

=cut

Francesc Guasch's avatar
Francesc Guasch committed
117
sub add_user {
118
119
    my %args = @_;

120
    _init_connector();
121
122
123
124
125

    my $name= $args{name};
    my $password = $args{password};
    my $is_admin = ($args{is_admin} or 0);
    my $is_temporary= ($args{is_temporary} or 0);
126
    my $is_external= ($args{is_external} or 0);
127

128
    delete @args{'name','password','is_admin','is_temporary','is_external'};
129
130
131
132

    confess "WARNING: Unknown arguments ".Dumper(\%args)
        if keys %args;

133

134
135
    my $sth;
    eval { $sth = $$CON->dbh->prepare(
136
137
            "INSERT INTO users (name,password,is_admin,is_temporary, is_external)"
            ." VALUES(?,?,?,?,?)");
138
139
    };
    confess $@ if $@;
140
141
142
143
144
    if ($password) {
        $password = sha1_hex($password);
    } else {
        $password = '*LK* no pss';
    }
145
    $sth->execute($name,$password,$is_admin,$is_temporary, $is_external);
Francesc Guasch's avatar
Francesc Guasch committed
146
    $sth->finish;
147
148
149
150
151
152

    $sth = $$CON->dbh->prepare("SELECT id FROM users WHERE name = ? ");
    $sth->execute($name);
    my ($id_user) = $sth->fetchrow;
    $sth->finish;

153
154
155
156
157
    my $user = Ravada::Auth::SQL->search_by_id($id_user);

    # temporary allow grant permissions
    my $id_grant = _search_id_grant('grant');

158
159
160
161
162
163
164
    $sth = $$CON->dbh->prepare(
            "INSERT INTO grants_user "
            ." (id_grant, id_user, allowed)"
            ." VALUES(?,?,1) "
        );
    $sth->execute($id_grant, $id_user);
    $sth->finish;
Francesc Guasch's avatar
Francesc Guasch committed
165

166
167
168
169
170
171
    $user->grant_user_permissions($user);
    if (!$is_admin) {
        $user->grant_user_permissions($user);
        $user->revoke($user,'grant');
        return $user;
    }
Francesc Guasch's avatar
Francesc Guasch committed
172
    $user->grant_admin_permissions($user);
173
    return $user;
174
175
176
177
178
179
180
181
182
183
184
}

sub _search_id_grant {
    my $type = shift;
    my $sth = $$CON->dbh->prepare("SELECT id FROM grant_types WHERE name = ?");
    $sth->execute($type);
    my ($id) = $sth->fetchrow;
    $sth->finish;

    confess "Unknown grant $type"   if !$id;
    return $id;
Francesc Guasch's avatar
Francesc Guasch committed
185
186
}

187
188
sub _load_data {
    my $self = shift;
189
    _init_connector();
190

191
    die "No login name nor id " if !$self->name && !$self->id;
192

193
    confess "Undefined \$\$CON" if !defined $$CON;
194
195
    my $sth = $$CON->dbh->prepare(
       "SELECT * FROM users WHERE name=? ");
196
    $sth->execute($self->name);
197
198
199
    my ($found) = $sth->fetchrow_hashref;
    $sth->finish;

200
201
202
203
204
205
206
207
208
    return if !$found->{name};

    delete $found->{password};
    lock_hash %$found;
    $self->{_data} = $found if ref $self && $found;
}

sub _load_data_by_id {
    my $id = shift;
209
    _init_connector();
210
211
212
213
214
215
216
217
218
219
220

    my $sth = $$CON->dbh->prepare(
       "SELECT * FROM users WHERE id=? ");
    $sth->execute($id);
    my ($found) = $sth->fetchrow_hashref;
    $sth->finish;

    delete $found->{password};
    lock_hash %$found;

    return $found;
221
222
}

223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
sub _load_data_by_username {
    my $username = shift;
    _init_connector();

    my $sth = $$CON->dbh->prepare(
       "SELECT * FROM users WHERE name=? ");
    $sth->execute($username);
    my ($found) = $sth->fetchrow_hashref;
    $sth->finish;

    delete $found->{password};
    lock_hash %$found;

    return $found;
}

239
240
241
242
243
244
245
246
247
248
249
250
=head2 login

Logins the user

     my $ok = $user->login($password);
     my $ok = Ravada::LDAP::SQL::login($name, $password);

returns true if it succeeds

=cut


Francesc Guasch's avatar
Francesc Guasch committed
251
sub login {
Francesc Guasch's avatar
Francesc Guasch committed
252
253
    my $self = shift;

254
255
    _init_connector();

Francesc Guasch's avatar
Francesc Guasch committed
256
257
258
259
260
261
262
263
264
265
266
    my ($name, $password);

    if (ref $self) {
        $name = $self->name;
        $password = $self->password;
        $self->{_data} = {};
    } else { # old login API
        $name = $self;
        $password = shift;
    }

Francesc Guasch's avatar
Francesc Guasch committed
267

268
    my $sth = $$CON->dbh->prepare(
Francesc Guasch's avatar
Francesc Guasch committed
269
       "SELECT * FROM users WHERE name=? AND password=?");
Francesc Guasch's avatar
Francesc Guasch committed
270
    $sth->execute($name , sha1_hex($password));
Francesc Guasch's avatar
Francesc Guasch committed
271
    my ($found) = $sth->fetchrow_hashref;
Francesc Guasch's avatar
Francesc Guasch committed
272
    $sth->finish;
273

Francesc Guasch's avatar
Francesc Guasch committed
274
275
    if ($found) {
        lock_hash %$found;
Francesc Guasch's avatar
Francesc Guasch committed
276
        $self->{_data} = $found if ref $self && $found;
Francesc Guasch's avatar
Francesc Guasch committed
277
278
279
    }

    return 1 if $found;
280
281

    return;
Francesc Guasch's avatar
Francesc Guasch committed
282
283
}

Laura Figuerola's avatar
Laura Figuerola committed
284
285
286
287
288
289
=head2 make_admin

Makes the user admin. Returns nothing.

     Ravada::Auth::SQL::make_admin($id);

290
=cut
Laura Figuerola's avatar
Laura Figuerola committed
291

Francesc Guasch's avatar
Francesc Guasch committed
292
sub make_admin($self, $id) {
Laura Figuerola's avatar
Laura Figuerola committed
293
294
295
296
297
    my $sth = $$CON->dbh->prepare(
            "UPDATE users SET is_admin=1 WHERE id=?");

    $sth->execute($id);
    $sth->finish;
298

299
300
301
    my $user = $self->search_by_id($id);
    $self->grant_admin_permissions($user);

Laura Figuerola's avatar
Laura Figuerola committed
302
303
}

304
305
306
307
308
309
=head2 remove_admin

Remove user admin privileges. Returns nothing.

     Ravada::Auth::SQL::remove_admin($id);

310
=cut
311

Francesc Guasch's avatar
Francesc Guasch committed
312
sub remove_admin($self, $id) {
313
314
315
316
317
    my $sth = $$CON->dbh->prepare(
            "UPDATE users SET is_admin=NULL WHERE id=?");

    $sth->execute($id);
    $sth->finish;
318

319
320
321
    my $user = $self->search_by_id($id);
    $self->revoke_all_permissions($user);
    $self->grant_user_permissions($user);
322
323
}

Francesc Guasch's avatar
Francesc Guasch committed
324
325
326
327
328
329
330
331
332
=head2 is_admin

Returns true if the user is admin.

    my $is = $user->is_admin;

=cut


Francesc Guasch's avatar
Francesc Guasch committed
333
334
335
336
sub is_admin {
    my $self = shift;
    return $self->{_data}->{is_admin};
}
337

338
339
340
341
342
343
344
345
=head2 is_operator

Returns true if the user is admin or has been granted special permissions

=cut

sub is_operator {
    my $self = shift;
Kimia Mirehbar's avatar
Kimia Mirehbar committed
346
    return $self->is_admin()
347
        || $self->can_shutdown_clone()
Kimia Mirehbar's avatar
Kimia Mirehbar committed
348
349
	|| $self->can_hibernate_clone
	|| $self->can_change_settings_clones()
350
        || $self->can_remove_clone()
351
352
        || $self->can_create_base()
        || $self->can_create_machine();
353
354
}

355
356
357
358
359
360
361
362
=head2 can_list_own_machines

Returns true if the user can list her own virtual machines at the web frontend

=cut

sub can_list_own_machines {
    my $self = shift;
363
364
365
366
    return 1
        if $self->can_create_base()
            || $self->can_create_machine
        ;
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
    return 0;
}

=head2 can_list_machines

Returns true if the user can list all the virtual machines at the web frontend

=cut

sub can_list_machines {
    my $self = shift;
    return 1 if $self->is_admin();
    return 0;
}


383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
=head2 is_external

Returns true if the user authentication is not from SQL

    my $is = $user->is_external;

=cut


sub is_external {
    my $self = shift;
    return $self->{_data}->{is_external};
}


398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
=head2 is_temporary

Returns true if the user is admin.

    my $is = $user->is_temporary;

=cut


sub is_temporary{
    my $self = shift;
    return $self->{_data}->{is_temporary};
}


Francesc Guasch's avatar
Francesc Guasch committed
413
414
415
416
417
418
419
420
=head2 id

Returns the user id

    my $id = $user->id;

=cut

421
422
sub id {
    my $self = shift;
423
424
425
    my $id;
    eval { $id = $self->{_data}->{id} };
    confess $@ if $@;
426

427
428
    return $id;
}
Francesc Guasch's avatar
Francesc Guasch committed
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443

=head2 change_password

Changes the password of an User

    $user->change_password();

Arguments: password

=cut

sub change_password {
    my $self = shift;
    my $password = shift or die "ERROR: password required\n";

444
445
    _init_connector();

Francesc Guasch's avatar
Francesc Guasch committed
446
447
    die "Password too small" if length($password)<6;

448
    my $sth= $$CON->dbh->prepare("UPDATE users set password=?"
Francesc Guasch's avatar
Francesc Guasch committed
449
        ." WHERE name=?");
450
    $sth->execute(sha1_hex($password), $self->name);
Francesc Guasch's avatar
Francesc Guasch committed
451
}
Francesc Guasch's avatar
Francesc Guasch committed
452

453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
=head2 compare_password

Changes the input with the password of an User

    $user->compare_password();

Arguments: password

=cut

sub compare_password {
    my $self = shift;
    my $password = shift or die "ERROR: password required\n";
    
    _init_connector();
    
    my $sth= $$CON->dbh->prepare("SELECT password FROM users WHERE name=?");
    $sth->execute($self->name);
    my $hex_pass = $sth->fetchrow();
    if ($hex_pass eq sha1_hex($password)) {
        return 1;
    }
    else {
        return 0;
    }
}

480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
=head2 language

  Updates or selects the language selected for an User

    $user->language();

  Arguments: lang

=cut

  sub language {
    my $self = shift;
    my $tongue = shift;
    if (defined $tongue) {
      my $sth= $$CON->dbh->prepare("UPDATE users set language=?"
          ." WHERE name=?");
      $sth->execute($tongue, $self->name);
    }
    else {
      my $sth = $$CON->dbh->prepare(
         "SELECT language FROM users WHERE name=? ");
      $sth->execute($self->name);
      return $sth->fetchrow();
    }
  }

Francesc Guasch's avatar
Francesc Guasch committed
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520

=head2 remove

Removes the user

    $user->remove();

=cut

sub remove($self) {
    my $sth = $$CON->dbh->prepare("DELETE FROM users where id=?");
    $sth->execute($self->id);
    $sth->finish;
}

Francesc Guasch's avatar
Francesc Guasch committed
521
522
523
524
525
526
527
528
529
=head2 can_do

Returns if the user is allowed to perform a privileged action

    if ($user->can_do("remove")) { 
        ...

=cut

Francesc Guasch's avatar
Francesc Guasch committed
530
sub can_do($self, $grant) {
Francesc Guasch's avatar
Francesc Guasch committed
531
    return $self->{_grant}->{$grant} if defined $self->{_grant}->{$grant};
Francesc Guasch's avatar
Francesc Guasch committed
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550

    $self->_load_grants();

    confess "Unknown permission '$grant'\n" if !exists $self->{_grant}->{$grant};
    return $self->{_grant}->{$grant};
}

sub _load_grants($self) {
    my $sth = $$CON->dbh->prepare(
        "SELECT gt.name, gu.allowed"
        ." FROM grant_types gt LEFT JOIN grants_user gu "
        ."      ON gt.id = gu.id_grant "
        ."      AND gu.id_user=?"
    );
    $sth->execute($self->id);
    my ($name, $allowed);
    $sth->bind_columns(\($name, $allowed));

    while ($sth->fetch) {
551
        $self->{_grant}->{$name} = $allowed;# or undef);
Francesc Guasch's avatar
Francesc Guasch committed
552
553
554
555
    }
    $sth->finish;
}

Francesc Guasch's avatar
Francesc Guasch committed
556
557
558
559
560
561
=head2 grant_user_permissions

Grant an user permissions for normal users

=cut

Francesc Guasch's avatar
Francesc Guasch committed
562
sub grant_user_permissions($self,$user) {
563
564
565
566
567
568
    $self->grant($user, 'clone');
    $self->grant($user, 'change_settings');
    $self->grant($user, 'remove');
    $self->grant($user, 'screenshot');
}

Francesc Guasch's avatar
Francesc Guasch committed
569
570
571
572
573
574
=head2 grant_operator_permissions

Grant an user operator permissions, ie: hibernate all

=cut

575
576
577
578
579
sub grant_operator_permissions($self,$user) {
    $self->grant($user, 'hibernate_all');
    #TODO
}

Francesc Guasch's avatar
Francesc Guasch committed
580
581
582
583
584
585
=head2 grant_manager_permissions

Grant an user manager permissions, ie: hibernate all clones

=cut

586
587
588
589
590
sub grant_manager_permissions($self,$user) {
    $self->grant($user, 'hibernate_clone');
    #TODO
}

Francesc Guasch's avatar
Francesc Guasch committed
591
592
593
594
595
596
=head2 grant_admin_permissions

Grant an user all the permissions

=cut

597
598
599
600
601
602
603
604
605
606
607
608
sub grant_admin_permissions($self,$user) {
    my $sth = $$CON->dbh->prepare(
            "SELECT name FROM grant_types "
    );
    $sth->execute();
    while ( my ($name) = $sth->fetchrow) {
        $self->grant($user,$name);
    }
    $sth->finish;

}

609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
=head2 revoke_all_permissions

Revoke all permissions from an user

=cut

sub revoke_all_permissions($self,$user) {
    my $sth = $$CON->dbh->prepare(
            "SELECT name FROM grant_types "
    );
    $sth->execute();
    while ( my ($name) = $sth->fetchrow) {
        $self->revoke($user,$name);
    }
    $sth->finish;

}


Francesc Guasch's avatar
Francesc Guasch committed
628
629
630
631
632
633
634
635
636
637
638
=head2 grant

Grant an user a specific permission, or revoke it

    $admin_user->grant($user2,"clone");    # both are 
    $admin_user->grant($user3,"clone",1);  # the same

    $admin_user->grant($user4,"clone",0);  # revoke a grant

=cut

639
sub grant($self,$user,$permission,$value=1) {
Francesc Guasch's avatar
Francesc Guasch committed
640
641
642
643
644
    if ( !$self->can_grant() && $self->name ne $Ravada::USER_DAEMON_NAME ) {
        my @perms = $self->list_permissions();
        confess "ERROR: ".$self->name." can't grant permissions for ".$user->name."\n"
            .Dumper(\@perms);
    }
645

646
    return 0 if !$value && !$user->can_do($permission);
647
648
649

    my $value_sql = $user->can_do($permission);
    return $value if defined $value_sql && $value_sql eq $value;
650

651
    my $id_grant = _search_id_grant($permission);
652
    if (! defined $user->can_do($permission)) {
653
        my $sth = $$CON->dbh->prepare(
654
655
            "INSERT INTO grants_user "
            ." (id_grant, id_user, allowed)"
656
            ." VALUES(?,?,?) "
657
658
659
660
661
        );
        $sth->execute($id_grant, $user->id, $value);
        $sth->finish;
    } else {
        my $sth = $$CON->dbh->prepare(
662
663
664
665
666
667
668
669
670
671
672
            "UPDATE grants_user "
            ." set allowed=?"
            ." WHERE id_grant = ? AND id_user=?"
        );
        $sth->execute($value, $id_grant, $user->id);
        $sth->finish;
    }
    $user->{_grant}->{$permission} = $value;
    confess "Unable to grant $permission for ".$user->name ." expecting=$value "
            ." got= ".$user->can_do($permission)
        if $user->can_do($permission) ne $value;
Francesc Guasch's avatar
Francesc Guasch committed
673
    return $value;
Francesc Guasch's avatar
Francesc Guasch committed
674
675
}

Francesc Guasch's avatar
Francesc Guasch committed
676
677
678
679
680
681
682
683
=head2 revoke

Revoke a permission from an user

    $admin_user->revoke($user2,"clone");

=cut

684
685
686
687
688
sub revoke($self,$user,$permission) {
    return $self->grant($user,$permission,0);
}


Francesc Guasch's avatar
Francesc Guasch committed
689
690
691
692
693
694
=head2 list_all_permissions

Returns a list of all the available permissions

=cut

Francesc Guasch's avatar
Francesc Guasch committed
695
sub list_all_permissions($self) {
Francesc Guasch's avatar
Francesc Guasch committed
696
    return if !$self->is_admin;
697

Francesc Guasch's avatar
Francesc Guasch committed
698
    my $sth = $$CON->dbh->prepare(
699
        "SELECT * FROM grant_types ORDER BY name"
Francesc Guasch's avatar
Francesc Guasch committed
700
701
702
    );
    $sth->execute;
    my @list;
703
704
705
    while (my $row = $sth->fetchrow_hashref ) {
        lock_hash(%$row);
        push @list,($row);
Francesc Guasch's avatar
Francesc Guasch committed
706
707
    }
    return @list;
Francesc Guasch's avatar
Francesc Guasch committed
708
709
}

Francesc Guasch's avatar
Francesc Guasch committed
710
711
712
713
714
715
=head2 list_permissions

Returns a list of all the permissions granted to the user

=cut

Francesc Guasch's avatar
Francesc Guasch committed
716
717
718
719
720
721
722
723
724
sub list_permissions($self) {
    my @list;
    for my $grant (sort keys %{$self->{_grant}}) {
        push @list , (  [$grant => $self->{_grant}->{$grant} ] )
            if $self->{_grant}->{$grant};
    }
    return @list;
}

725
726
727
728
729
730
731
732
733
734
735
736
737
738
sub can_change_settings($self, $id_domain=undef) {
    if (!defined $id_domain) {
        return $self->can_do("change_settings");
    }
    return 1 if $self->can_change_settings_all();

    return 0 if !$self->can_change_settings();

    my $domain = Ravada::Front::Domain->open($id_domain);
    return 1 if $self->id == $domain->id_owner;

    return 0;
}

739
740
741
742
743
744
sub grants($self) {
    $self->_load_grants()   if !$self->{_grant};
    return () if !$self->{_grant};
    return %{$self->{_grant}};
}

745
sub AUTOLOAD($self) {
Francesc Guasch's avatar
Francesc Guasch committed
746
747
748
749
750
751
752
753
754
755
756

    my $name = $AUTOLOAD;
    $name =~ s/.*://;

    confess "Can't locate object method $name via package $self"
        if !ref($self) || $name !~ /^can_(.*)/;

    my ($permission) = $name =~ /^can_([a-z_]+)/;
    return $self->can_do($permission)  if $permission;
}

757
1;