Hilfreiche SQL Statements

Auf dieser Seite soll eine Sammlung von hlfreichen SQL Statements für den JEAF Cloud Desktop aufgebaut werden

Das Ergebnis von SQL-Abfragen lässt sich auch als CSV-Datei exportieren. Dazu ist an ein Statement folgender Nachsatz zu ergänzen

into outfile '/var/lib/mysql-files/export.csv' CHARACTER SET utf8 fields terminated by ',' enclosed by '"' lines terminated by '\n'

Beispiel:

select t.LOGINNAME, t.LOGINCOUNTER, t.LASTLOGINDATE from jeaf_account t order by t.LASTLOGINDATE desc into outfile '/var/lib/mysql-files/export.csv' fields terminated by ',' enclosed by '"' lines terminated by '\n';

Übersicht über die letzen Logins

select t.LOGINNAME, t.LOGINCOUNTER, t.LASTLOGINDATE from jeaf_account t order by t.LASTLOGINDATE desc;

Abfrage aller Benutzeraccounts

-- Übersicht über alle Accounts
select person.firstname, person.name, account.loginname, account.logincounter, account.lastlogindate, address.addressvalue, account.start, account.end, teamaccount.organizationalunitaccountoid 
    from jeaf_account account, jeaf_org_element person, jeaf_address address, jeaf_cloud_account_link teamaccount
    where account.logincounter is not null and person.account_oid = account.oid and person.oid = address.owner_oid and address.mainemailaddress = 1 and account.oid = teamaccount.useraccountoid 
    order by account.lastlogindate;

Abfrage aller Team Accounts

select organizationalunitaccount_oid, name from jeaf_org_element where class_id = 116 order by name;

Abfrage von Berechtigungen eines Benutzers

-- Welche Rollen hat ein Benutzer
select account.loginname, roledef.ROLENAME from jeaf_account account,  jeaf_permission permission, jeaf_account_permission_link link, role_definition roledef 
where account.loginname = 'anonymous' and account.OID = link.ACCOUNTBO_OID and link.PERMISSIONBO_OID = permission.OID and permission.ROLEDEFINITION_OID = roledef.OID
order by roledef.ROLENAME;
 
-- Welche Berechtigungen hat ein Benutzer
select account.loginname, roledef.ROLENAME, type.NAME from jeaf_account account,  jeaf_permission permission, jeaf_account_permission_link link, role_definition roledef, authorization_type type, authorization auth  
where account.loginname = 'anonymous' and account.OID = link.ACCOUNTBO_OID and link.PERMISSIONBO_OID = permission.OID and 
permission.ROLEDEFINITION_OID = roledef.OID and roledef.OID = auth.OWNINGROLE_OID and auth.AUTHORIZATIONTYPE_OID = type.OID
order by type.name;

Datenbank-Dump erstellen

# Dump der WEEASY Tabellen erstellen
mysqldump --user=root --events --databases weeasy_db --single-transaction > ./dump_weeasy_db.sql
 
# mysqldump erstellt in kompletten Datenbank-Dump und schreibt diesen in die angegebene Datei
mysqldump --user=root --events --all-databases --single-transaction > ./mysql-dump.sql

Abfrage Filesystem Access Log

select log.FILESYSTEMACTION as Action, log.PATH as Path, log.NAME as Name, account.LOGINNAME as User, log.TIMESTAMP as Timestamp from jeaf_fs_access_log log, jeaf_account account where log.USERACCOUNTOID = account.OID order by log.TIMESTAMP DESC LIMIT 100;

Ablaufdatum von MySQL Passwort definieren

 mysql -u root -p ALTER USER 'root'@'localhost' IDENTIFIED BY '' PASSWORD EXPIRE NEVER;