Let’s go physical(ly separated)

Дата публикации: 18-02-2026 09:44:18

In my previous article , we discussed best practices for securing and monitoring admin access, such as role-based access controls (RBAC). It’s a good time to have a quick reminder about AAA aka “ Authentication, Authorization and Accounting” , which is a framework designed to manage access to networked resources and ensure secure, controlled interactions :Authentication : Verifies the identity of a user or system attempting to access a resource. This step involves credentials to confirm legitimacy before granting access.Authorization : Determines what a user or system is allowed to do after authentication. It sets permissions, defining the actions or resources that can be accessed based on the user’s role, ensuring they only interact with the data or systems for which they have clearance.Accounting : Tracks and logs user activities within a system, providing a record of what actions were taken, when, and by whom. This audit trail helps in monitoring, troubleshooting, and detecting unauthorized or malicious behavior.Altogether, AAA enhances security by ensuring that only authenticated users can access specific resources, with their actions monitored and recorded for accountability. And that’s exactly what we did with use of personal logins, role based access control and using the audit log to track admin activity.We know that admins aren’t the only one being able to temper with or gaining unwanted access to datas : any user is a potential risk for our database. Obviously, the previous advices still applies but role based access can prove to be inefficient in case of fine grained access control.Let’s imagine we are in a high security line of business and users should have very strict and tight access control. One way to prevent malicous users to circumvent the filtering si to physically isolate the user from the data. But can we dot it with MariaDB ?First step, we create a user as mentionned in my previous article.CREATE USER \'IT-O\'@\'Coruscant\' IDENTIFIED via ed25519 REQUIRE SSL WITH MAX_USER_CONNECTIONS 1 PASSWORD EXPIRE INTERVAL xx DAYS;Second step, we create a dedicated schema using his user token.Third step, we are gonna use to match the tables. We are now leveraging 3 parameters of said views : , DEFINER and SQL SECURITY :ALGORITHM allows us to tell how to execute the view. Here we will be choosing to make use of a temporary table to store the result of the view. While not the fastest, it has the good taste to make the view unwritable, on top of not having granted INSERT,UPDATE/DELETE privileges to the user,DEFINER allows us to give “ownership” of the view to a specific account,SQL SECURITY allows us to have the view executed with the DEFINER set of privileges instead of the INVOKER (aka the user) one.CREATE VIEW `IT-O`.BoobyTable ALGORITHM=temptable DEFINER=`locked.admin.account`@`localhost` SQL SECURITY=DEFINER AS SELECT necessary,columns,only FROM RealSchema.RealTable WHERE RestrictionClauses=values;Fourth thing, said user needs to manipulate the underlying table datas. We could theoritically allow the user to write through the view . But the control over its action is then limited, hence i prefer creating ad hoc that will emulate the desired actions. With those objects we can leverage the same last 2 parameters( stored routines ) as with views to ensure a good isolation. DEFINER & SQL SECURITYDELIMITER // CREATE OR REPLACE DEFINER=`locked.admin.account`@`localhost` PROCEDURE `IT-O`.Action_BoobyTable (IN param_name type , OUT param_name type) SQL SECURITY=DEFINER BEGIN ACTIONS in SQL/PSM or PL/SQL END// DELIMITER ;Fifth step, we give the permissions to said user over the objects it needs.CREATE ROLE `IT-O`; GRANT SELECT on `IT-O`.* to `IT-O`; GRANT EXECUTE on `IT-O`.necesary_procs to `IT-O`; GRANT `IT-O` to `IT-O`@`Coruscant`; SET DEFAULT ROLE `IT-O` for `IT-O`@`Coruscant`;And now we have a user which acces is tightly controled and monitored even tho he has direct access to the database. Of course this could also be upgraded making him flow through on a dedicated service with , general login , query throttling , and possibly also resultset size limitation . inserting connexion IP in the statement data maskingOf course, deployment of such an internal architecture can be partially automated since it is mostly linked to parametrizing.Special thanks to Federico Razzoli for mentionning the use of a locked admin account as DEFINER.Originally published at https://www.linkedin.com.
Let’s go physical(ly separated) appeared first on MariaDB.org

Схожие новости

#	Наименование новости	Тональность	Информативность	Дата публикации
1	Zero-configuration TLS and password management best practices in MariaDB 11.8	0	10.83	14-09-2025
2	Automated security validation: How 7,000+ tests shaped MariaDB’s new AppArmor profile	0	8.42	19-03-2026
3	Hardening MySQL: Practical Security Strategies for DBAs	0	5.76	02-03-2026
4	MariaDB Privacy-First Stack: Nextcloud, Passbolt and MariaDB Server	0	8.35	26-06-2026
5	How I Stopped Babysitting My Coding Agent (With Dotfiles)	0	7.17	05-05-2026
6	The Insert Benchmark vs MariaDB 10.2 to 13.0 on a 32-core server	0	6.43	08-04-2026
7	Sysbench vs MariaDB on a small server: using the same charset for all versions	0	8.32	06-04-2026
8	I got swarmed by a replication issue	0	6.83	02-06-2026
9	Deploying the MariaDB Privacy-First Stack Anywhere with Terraform	0	13.41	22-07-2026
10	Tag1 Insights: A New Direction for Authentication in Drupal Core	0	5	15-07-2026

Классификация: . Схожих патентов: 0. Схожих новостей: 10. Тональность: 0. Информативность: 6.89. Источник: mariadb.org.