Configuració inicial del SGBD

Introducció

Després d'instal·lar o engegar per primera vegada un SGBD, cal realitzar un conjunt de passos de configuració inicial abans de posar-lo en ús. Saltar-se aquests passos pot deixar el sistema en un estat insegur o amb un rendiment molt inferior al potencial.

Els passos principals de la configuració inicial són:

1. Canviar les contrasenyes per defecte dels comptes d'administrador.
2. Crear la primera base de dades o esquema de treball.
3. Configurar la codificació de caràcters i la col·lació.
4. Ajustar els paràmetres bàsics de memòria.
5. Establir el límit de connexions simultànies.

Configuració per defecte = Insegura

La majoria de SGBD s'instal·len amb configuracions per defecte pensades per a la màxima compatibilitat, no per a la seguretat. Això inclou comptes sense contrasenya, accés anònim habilitat, ports oberts a totes les interfícies i paràmetres de memòria mínims. Mai poseu un SGBD en producció sense revisar i endurir la configuració.

---

Pas 1 — Canvi de contrasenya de l'usuari administrador

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Connexió inicial com a usuari postgres (sense contrasenya en instal·lació local)
-- docker exec -it postgres psql -U postgres

-- Canviar la contrasenya del superusuari postgres
ALTER USER postgres WITH PASSWORD 'NouPass_Segur!2024';

-- Crear un rol d'administrador no superusuari per a ús diari
CREATE ROLE admin_dba WITH
    LOGIN
    PASSWORD 'AdminDBA_Pass!2024'
    CREATEDB
    CREATEROLE;

-- Verificar
SELECT usename, usesuper, usecreatedb, usecreaterole
FROM pg_user
WHERE usename IN ('postgres', 'admin_dba');

-- MySQL: la instal·lació via apt crea root sense contrasenya
-- però accessible només via socket Unix (auth_socket plugin)

-- Canviar la contrasenya de root
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NouPass_Segur!2024';
FLUSH PRIVILEGES;

-- Eliminar l'usuari anònim (si existeix)
DELETE FROM mysql.user WHERE User = '';
FLUSH PRIVILEGES;

-- Eliminar la base de dades test (si existeix)
DROP DATABASE IF EXISTS test;

-- Verificar usuaris existents
SELECT user, host, plugin, authentication_string != '' AS te_contrasenya
FROM mysql.user;

-- Canviar la contrasenya del compte SA
-- (o es fa al moment de la instal·lació via MSSQL_SA_PASSWORD)
ALTER LOGIN SA WITH PASSWORD = 'NouSAPass!2024';
ALTER LOGIN SA ENABLE;  -- Assegurar que SA no estigui desactivat

-- Crear un login d'administrador dedicat (millor pràctica)
CREATE LOGIN admin_dba WITH PASSWORD = 'AdminDBA_Pass!2024',
    DEFAULT_DATABASE = master,
    CHECK_EXPIRATION = ON,
    CHECK_POLICY = ON;

-- Afegir al rol sysadmin
ALTER SERVER ROLE sysadmin ADD MEMBER admin_dba;

-- Verificar
SELECT name, is_disabled, type_desc
FROM sys.server_principals
WHERE type IN ('S', 'U');

-- Connexió inicial com SYSDBA
-- sqlplus / as sysdba

-- Canviar contrasenya de SYS i SYSTEM
ALTER USER SYS IDENTIFIED BY NouSYSPass!2024;
ALTER USER SYSTEM IDENTIFIED BY NouSystemPass!2024;

-- Desbloquejar HR (esquema de demostració, opcional)
ALTER USER HR IDENTIFIED BY HRPass!2024 ACCOUNT UNLOCK;

-- Verificar estat dels comptes
SELECT username, account_status, expiry_date
FROM dba_users
ORDER BY username;

---

Pas 2 — Creació de la primera base de dades o esquema

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Crear una base de dades amb configuració explícita
CREATE DATABASE empresa
    WITH
    OWNER = admin_dba
    ENCODING = 'UTF8'
    LC_COLLATE = 'ca_ES.UTF-8'
    LC_CTYPE = 'ca_ES.UTF-8'
    TEMPLATE = template0
    CONNECTION LIMIT = 100;

-- Connectar-se a la nova base de dades
\c empresa

-- Crear un esquema de treball
CREATE SCHEMA IF NOT EXISTS produccion AUTHORIZATION admin_dba;

-- Configurar el search_path per defecte
ALTER DATABASE empresa SET search_path TO produccion, public;

-- Verificar
\l empresa
\dn

-- Crear la base de dades amb codificació explícita
CREATE DATABASE empresa
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Crear un usuari dedicat per a l'aplicació
CREATE USER 'app_user'@'%' IDENTIFIED BY 'AppUser_Pass!2024';

-- Donar privilegis sobre la base de dades
GRANT ALL PRIVILEGES ON empresa.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

-- Verificar
SHOW DATABASES;
SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE schema_name = 'empresa';

-- Crear una base de dades amb configuració personalitzada
CREATE DATABASE Empresa
ON PRIMARY (
    NAME = Empresa_data,
    FILENAME = '/var/opt/mssql/data/Empresa.mdf',
    SIZE = 100MB,
    MAXSIZE = 10GB,
    FILEGROWTH = 64MB
)
LOG ON (
    NAME = Empresa_log,
    FILENAME = '/var/opt/mssql/data/Empresa_log.ldf',
    SIZE = 50MB,
    MAXSIZE = 2GB,
    FILEGROWTH = 32MB
)
COLLATE Catalan_CI_AS;
GO

-- Crear un esquema dins de la base de dades
USE Empresa;
GO
CREATE SCHEMA produccio;
GO

-- Verificar
SELECT name, collation_name, state_desc
FROM sys.databases
WHERE name = 'Empresa';
GO

-- A Oracle, la unitat equivalent a "base de dades" per a aplicacions
-- és el PLUGGABLE DATABASE (PDB) en arquitectura CDB

-- Connexió com SYSDBA al CDB
-- sqlplus / as sysdba

-- Crear un nou PDB
CREATE PLUGGABLE DATABASE empresa_pdb
    ADMIN USER pdb_admin IDENTIFIED BY PdbAdmin!2024
    ROLES = (DBA)
    FILE_NAME_CONVERT = ('/opt/oracle/oradata/XE/pdbseed/',
                         '/opt/oracle/oradata/XE/empresa_pdb/');

-- Obrir el PDB
ALTER PLUGGABLE DATABASE empresa_pdb OPEN;

-- Fer-lo persistent entre reinicis
ALTER PLUGGABLE DATABASE empresa_pdb SAVE STATE;

-- Connectar al PDB i crear un usuari/esquema
ALTER SESSION SET CONTAINER = empresa_pdb;
CREATE USER empresa_app IDENTIFIED BY AppPass!2024
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW,
      CREATE PROCEDURE, CREATE SEQUENCE TO empresa_app;

---

Pas 3 — Codificació de caràcters i col·lació

La configuració de la codificació és irrevocable un cop creada la base de dades (en la majoria de motors). Per al context europeu i català, s'han d'usar configuracions que suportin caràcters accentuats i símbols especials.

Recomanació per a projectes nous

Useu sempre UTF-8 com a codificació i una col·lació insensible a accents per a cerques (utf8mb4_unicode_ci a MySQL, und-x-icu a PostgreSQL 15+, Catalan_CI_AS a SQL Server). Eviteu latin1 o latin2 en projectes nous.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Comprovar la codificació actual de la base de dades
SHOW server_encoding;
SELECT datname, encoding, datcollate, datctype
FROM pg_database;

-- Crear base de dades amb col·lació ICU (PostgreSQL 15+)
CREATE DATABASE empresa_icu
    ENCODING 'UTF8'
    LOCALE_PROVIDER icu
    ICU_LOCALE 'ca-ES'
    TEMPLATE template0;

-- Crear una columna amb col·lació específica per a cerques
CREATE TABLE clients (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100) COLLATE "ca-ES-x-icu",
    email VARCHAR(200)
);

-- Comprovar col·lació d'una columna
SELECT column_name, collation_name
FROM information_schema.columns
WHERE table_name = 'clients';

-- Comprovar configuració actual
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- Canviar la codificació per defecte del servidor (my.cnf / runtime)
SET GLOBAL character_set_server = 'utf8mb4';
SET GLOBAL collation_server = 'utf8mb4_unicode_ci';

-- Convertir una taula existent a utf8mb4
ALTER TABLE clients
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Comprovar una taula específica
SHOW CREATE TABLE clients\G

-- Comprovar col·lació del servidor
SELECT SERVERPROPERTY('Collation') AS collacio_servidor;

-- Comprovar col·lació de totes les bases de dades
SELECT name, collation_name
FROM sys.databases;

-- Crear base de dades amb col·lació catalana
CREATE DATABASE Empresa COLLATE Catalan_CI_AS;
GO

-- Canviar la col·lació d'una columna existent
ALTER TABLE clients
ALTER COLUMN nom VARCHAR(100) COLLATE Catalan_CI_AS;
GO

-- Col·lacions disponibles per al català
SELECT name, description
FROM fn_helpcollations()
WHERE name LIKE 'Catalan%';
GO

-- Comprovar el joc de caràcters de la base de dades
SELECT value FROM nls_database_parameters
WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY');

-- Establir paràmetres NLS per a la sessió
ALTER SESSION SET NLS_LANGUAGE = 'CATALAN';
ALTER SESSION SET NLS_TERRITORY = 'SPAIN';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';

-- Establir paràmetres NLS per defecte de la base de dades
ALTER SYSTEM SET NLS_LANGUAGE = 'CATALAN' SCOPE = SPFILE;
ALTER SYSTEM SET NLS_TERRITORY = 'SPAIN' SCOPE = SPFILE;
-- (Requereix reinici per tenir efecte)

-- Comprovar configuració NLS de la sessió actual
SELECT parameter, value FROM nls_session_parameters;

---

Pas 4 — Configuració bàsica de memòria

La configuració de memòria és un dels factors més importants per al rendiment del SGBD. Els valors per defecte solen ser molt conservadors.

Regla pràctica de memòria

Com a punt de partida, assigneu al buffer pool / shared memory del SGBD entre el 25% i el 40% de la RAM total del servidor. Ajusteu gradualment en funció del monitoratge.

PostgreSQLMySQL / MariaDBSQL ServerOracle

# Editar postgresql.conf
# Ubicació: /etc/postgresql/16/main/postgresql.conf (apt)
# o /var/lib/postgresql/data/postgresql.conf (Docker)

# Memòria compartida: 25% de la RAM total
shared_buffers = 512MB          # Per a un servidor amb 2 GB RAM

# Estimació de RAM disponible per a operacions de memòria
effective_cache_size = 1536MB   # 75% de la RAM total

# Memòria per a operacions d'ordenació i hash per connexió
work_mem = 16MB

# Memòria per a operacions de manteniment (VACUUM, CREATE INDEX)
maintenance_work_mem = 128MB

# Editar my.cnf: /etc/mysql/mysql.conf.d/mysqld.cnf
# o /etc/my.cnf (RHEL) / /etc/mysql/my.cnf (Debian)

[mysqld]
# Buffer pool d'InnoDB: 50-70% de la RAM per a servidors dedicats
innodb_buffer_pool_size = 1G        # Per a 2 GB RAM

# Nombre d'instàncies del buffer pool (recomanat >= 8 per > 1 GB pool)
innodb_buffer_pool_instances = 2

# Log buffer per a transaccions
innodb_log_buffer_size = 64M

# Memòria per a ordenació
sort_buffer_size = 4M
join_buffer_size = 4M

# Taula de claus MyISAM (llegat)
key_buffer_size = 32M

-- Configurar memòria màxima i mínima del servidor
-- (en MB)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

-- Memòria mínima garantitzada per a SQL Server
EXEC sp_configure 'min server memory (MB)', 512;
RECONFIGURE;
GO

-- Memòria màxima (deixeu sempre 2-4 GB per al SO)
-- Per a un servidor amb 8 GB RAM:
EXEC sp_configure 'max server memory (MB)', 6144;
RECONFIGURE;
GO

-- Comprovar configuració actual
SELECT name, value_in_use, description
FROM sys.configurations
WHERE name IN ('min server memory (MB)', 'max server memory (MB)');
GO

-- Oracle pot gestionar la memòria automàticament (ASMM / AMM)

-- Gestió automàtica de memòria (AMM) — Oracle 11g+
-- Definir la memòria total disponible per a Oracle
ALTER SYSTEM SET memory_target = 1G SCOPE = SPFILE;
ALTER SYSTEM SET memory_max_target = 2G SCOPE = SPFILE;

-- O bé gestió manual:
-- SGA (Shared Global Area): 40% de la RAM
ALTER SYSTEM SET sga_target = 800M SCOPE = SPFILE;
ALTER SYSTEM SET sga_max_size = 1200M SCOPE = SPFILE;

-- PGA (Program Global Area): 20% de la RAM
ALTER SYSTEM SET pga_aggregate_target = 400M SCOPE = SPFILE;

-- Comprovar valors actuals
SELECT name, value, description
FROM v$parameter
WHERE name IN ('memory_target', 'sga_target', 'pga_aggregate_target');

---

Pas 5 — Límit de connexions simultànies

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Veure el límit actual
SHOW max_connections;

-- Canviar al fitxer postgresql.conf (requereix reinici)
-- max_connections = 100

-- Comprovar connexions actuals
SELECT count(*) AS connexions_actives,
       max_conn AS limit_maxim,
       max_conn - count(*) AS disponibles
FROM pg_stat_activity,
     (SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') mc
GROUP BY max_conn;

-- Limitar connexions per a un usuari específic
ALTER ROLE app_user CONNECTION LIMIT 20;

-- Limitar connexions per a una base de dades
ALTER DATABASE empresa CONNECTION LIMIT 50;

-- Veure el límit actual
SHOW VARIABLES LIKE 'max_connections';

-- Canviar en temps d'execució (sense reinici)
SET GLOBAL max_connections = 200;

-- Canviar permanentment (my.cnf)
-- max_connections = 200

-- Comprovar connexions actuals
SHOW STATUS LIKE 'Threads_connected';
SHOW PROCESSLIST;

-- Limitar connexions per usuari
ALTER USER 'app_user'@'%'
    WITH MAX_USER_CONNECTIONS 20
         MAX_CONNECTIONS_PER_HOUR 1000;

-- SQL Server no té un límit de connexions configurable directament;
-- es limita pel maquinari i les llicències.
-- Es pot limitar a nivell de pool de connexions a l'aplicació.

-- Comprovar connexions actives per base de dades
SELECT DB_NAME(dbid) AS base_de_dades,
       COUNT(*) AS num_connexions
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid
ORDER BY num_connexions DESC;
GO

-- Veure totes les connexions actives
SELECT session_id, login_name, host_name,
       program_name, status, cpu_time, memory_usage
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
ORDER BY cpu_time DESC;
GO

-- Veure el límit actual de processos i sessions
SELECT name, value
FROM v$parameter
WHERE name IN ('processes', 'sessions', 'transactions');

-- Canviar el límit (requereix reinici)
ALTER SYSTEM SET processes = 300 SCOPE = SPFILE;
-- sessions = ceil(1.1 * processes) + 5 (calculat automàticament)

-- Comprovar connexions actives
SELECT COUNT(*) AS connexions_actives FROM v$session;

SELECT username, status, COUNT(*) AS num_sessious
FROM v$session
WHERE username IS NOT NULL
GROUP BY username, status
ORDER BY num_sessious DESC;

-- Limitar sessions per perfil d'usuari
CREATE PROFILE app_profile LIMIT
    SESSIONS_PER_USER 20
    IDLE_TIME 30
    CONNECT_TIME 480;

ALTER USER empresa_app PROFILE app_profile;

---

Miniactivitat — AC0111

Configuració inicial del SGBD

Treballeu amb el contenidor Docker de MySQL o PostgreSQL creat a la pràctica anterior.

1. Connecteu-vos com a administrador i canvieu la contrasenya del compte root/postgres usant ALTER USER.
2. Creeu una nova base de dades anomenada sgbd_<nom_alumne> amb codificació UTF-8 i la col·lació adequada.
3. Dins d'aquesta base de dades, creeu un esquema (o usuari a Oracle) per a l'aplicació.
4. Comproveu la configuració de max_connections (o equivalent) i el nombre de connexions actives en aquest moment.
5. Consulteu la configuració de memòria del buffer pool / shared_buffers i anoteu el valor per defecte.
6. Responeu: Per quin motiu és important establir CONNECTION LIMIT per base de dades o per usuari en un entorn de producció?

Entrega: captura de pantalla de cada comanda executada i la resposta a la pregunta 6.