Read / Write crypted table from / to a SQLite database

Read / Write crypted table from / to a SQLite database

write_db_encrypt(conn, value, name = "credentials", passphrase = NULL)

read_db_decrypt(conn, name = "credentials", passphrase = NULL)

Arguments

conn

A DBIConnection object, as returned by dbConnect.

value

A data.frame.

name

A character string specifying the unquoted DBMS table name.

passphrase

A secret passphrase to crypt the table inside the database

Value

a data.frame for read_db_decrypt.

See also

create_db

Examples

# connect to database
conn <- DBI::dbConnect(RSQLite::SQLite(), dbname = ":memory:")

# write to database
write_db_encrypt(conn, value = head(iris), name = "iris", passphrase = "supersecret")

# read
read_db_decrypt(conn = conn, name = "iris", passphrase = "supersecret")
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> 1          5.1         3.5          1.4         0.2  setosa
#> 2          4.9         3.0          1.4         0.2  setosa
#> 3          4.7         3.2          1.3         0.2  setosa
#> 4          4.6         3.1          1.5         0.2  setosa
#> 5          5.0         3.6          1.4         0.2  setosa
#> 6          5.4         3.9          1.7         0.4  setosa


# with wrong passphrase
if (FALSE) {
read_db_decrypt(conn = conn, name = "iris", passphrase = "forgotten")
}

# with DBI method you'll get a crypted blob
DBI::dbReadTable(conn = conn, name = "iris")
#>         value         iv
#> 1 blob[624 B] blob[16 B]

# add some users to database
if (FALSE) {
conn <- DBI::dbConnect(RSQLite::SQLite(), dbname = "path/to/database.sqlite")

# update "credentials" table
current_user <- read_db_decrypt(
  conn,
  name = "credentials",
  passphrase = key_get("R-shinymanager-key", "obiwankenobi")
)

add_user <- data.frame(user = "new", password = "pwdToChange",
                      start = NA, expire = NA, admin = TRUE)

new_users <- rbind.data.frame(current_user, add_user)

write_db_encrypt(
  conn,
  value = new_users,
  name = "credentials",
  key_get("R-shinymanager-key", "obiwankenobi")
)

# update "pwd_mngt" table
pwd_mngt <- read_db_decrypt(
  conn,
  name = "pwd_mngt",
  passphrase = key_get("R-shinymanager-key", "obiwankenobi")
)

pwd_mngt <- rbind.data.frame(
  pwd_mngt,
  data.frame(user = "new", must_change = T, have_changed = F, date_change = "")
)

write_db_encrypt(
  conn,
  value = pwd_mngt,
  name = "pwd_mngt",
  passphrase = key_get("R-shinymanager-key", "obiwankenobi")
)
}

DBI::dbDisconnect(conn)