I configured in /etc/mongod.conf to enforce keyfile access control, in security option enabled, keyFile is /root/dbtest.key (the absolute path of keyfile). I already gave the ownership to mongodb user by chown, and granted 400 permission on that dbtest.key file. But mongod keeps failing to start, after checking log, the error is Error reading file /root/dbtest.key: Permission denied. After checking the ownership and permissions on dbtest.key Which means I already granted correctly. So I don't know at which step I did wrong

On my local mySQL (on CentOS 6 VM) database I wanted to export some data from the DB to a file like this: SELECT html FROM mytable WHERE lang = 'en' and id='KEY01' INTO OUTFILE 'key01_en.html' By doing this, the file will be saved in the /var/lib/mysql/mydbschema/ by default. Now, I want the file to be saved in my user let say /home/userone/dump_html folder If I say this (I launch the mysql command from the shell, and logged as "userone": SELECT html FROM mytable WHERE lang = 'en' and id='KEY01' INTO OUTFILE '/home/userone/dumphtml/key01_en.html' It makes an error that the process does not have enough rights to write there. But now still from the mysql> prompt, if I make: SELECT html FROM mytable WHERE lang = 'en' and id='KEY01' INTO OUTFILE '/tmp/key01_en.html' ; -- and then system mv /tmp/key01_en.html /home/userone/dumphtml/key01_en.html ; It works without any issue. It looks strange to me the SQL cannot write on my disk but the system command can. Any idea why? (The discussion started on this point from [this answer](https://stackoverflow.com/a/23390257/628006) on SO)

I have created a group (role) called "employees" and I've created some users that are its member and that inherit its rights. I have a database owned by the group "employees". The goal: To setup things in a way that allows all of the users to work with all of the objects in the database. The problem: I can't expect the users to set the owner to "employees" when they create a new object, because they use various limited interfaces to work with the database. When they create a schema or a table, it gets created with the user as its owner, which means that the other users don't have rights on that schema/table. I'm using PostgreSQL 11.2.

Is there a way, in MySQL, to grant access to a single partition of a table to a specific user? I see no way in the doc for that, but maybe I'm missing it... Here's a test case:

CREATE TABLE testing (
	id INT UNSIGNED NOT NULL
)
PARTITION BY RANGE(id) (
	PARTITION p0 VALUES LESS THAN (1000),
	PARTITION p2 VALUES LESS THAN MAXVALUE
);
INSERT INTO testing (id) VALUES (2),(20),(200),(2000),(20000),(200000);
SELECT * FROM testing;

CREATE USER 'foo'@'localhost' IDENTIFIED BY 'bar';
GRANT USAGE ON *.* TO 'foo'@'localhost';
GRANT SELECT ON my_test.testing TO 'foo'@'localhost';
FLUSH PRIVILEGES;

-- Now use foo@localhost: you shouldn't see 2k, 20k and 200k values
SELECT * FROM testing;

What would the proper GRANT syntax be?

I have a server running SQL Server 2022 with a couple of instances. There is the need to grant access to an external supplier working with us on a project. The idea is: the supplier connects to our sql server on a specific database they need to access. We have created a configuration on a proxy server running nginx that allows the connection to the local sql server. However, I cannot reach the database when testing from outside. Is there any configuration I need to do on the SQL server to allow this hebavior? Thanks in advance! nginx entry ----------- server { access_log /var/log/nginx/porgreencloud1433.log upstream_time; error_log /var/log/nginx/porgreencloud1433_error.log; listen 1433; server_name porgreencloud.company.com; location / { proxy_connect_timeout 60s; proxy_socket_keepalive on; proxy_pass http://dbtcp ; } ssl_certificate /etc/letsencrypt/live/porgreencloud.company.com/fullchain.pem; # managed by Certbot ssl_certificate_key /etc/letsencrypt/live/porgreencloud.company.com/privkey.pem; # managed by Certbot include /etc/nginx/sites-available/sslsettings.conf; } SQL Server settings ------------------- Connection String ----------------- SQLUser = "ourUser" SQLPassword = "LetsConnect2DB!!" SQLServer = "porgreencloud.company.com,1433" DbConn = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=" & SQLUser & ";Password=" & SQLPassword & ";Initial Catalog=" & DBName & ";" & _ "Data Source=" & SQLServer & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;" & _ "Use Encryption for Data = False;Tag with column collection when possible=False"

So I've just created a MySQL server. I also created some users only having specific access to certain databases, but do I need to restrict access to default databases to the users I've created? If so, how? Should I just create a role to try to restrict this access? I have been using this site as a reference: https://www.oreilly.com/library/view/mysql-reference-manual/0596002653/ch04s02.html

DBMS allows the admin to grant table access to a user using something like: GRANT ALL ON mydb.mytbl1, mydb.mytbl2 TO 'someuser'@'somehost'; However, is it possible to grant all tables to a user and explicitly block access to some of them? For example (The BLOCK key word is a fake one and is only used for illustration.): GRANT ALL ON mydb.* TO 'someuser'@'somehost'; BLOCK ALL ON mydb.mytbl3 TO 'someuser'@'somehost'; Another idea is to add a where clause to the GRANT statement. However, it seems that the GRANT statement syntax does not allow that (http://dev.mysql.com/doc/refman/5.1/en/grant.html) . This kind of access control might be convenient if the admin trust the user in most cases (e.g. 1000 tables) and only want to block access to a small number of top sensitive tables. Could you please tell me whether such kind of mechanism exists?

I'm green as a dba somewhat. Not really a DBA actually. So we have a bunch of databases/ datatables ... we need to grant a small subsection of access (say 3 tables, show half the fields for PPI reasons). For some reason the project lead initially thought we should create an entirely different database (DB_service_account) and either replicate or ETL-filter-write the limited data necessary to this. The thinking would be this simplifies management -- the service account has access to everything in that database. Is this a common design pattern? I mean --- an alternative would be create a limited/ secure views on these tables ... maybe with a 'schema' name (more of a logical than physical separation) .. right? That would avoid write jobs, additional storage? Then grant access to this schema ... right? Project lead also wants a separate warehouse (compute) for cost monitoring -- again probably not the most efficient but that can be done at the User level anyway --- is it possible or necessary to use "Shares" or "Replication" or are these completely different use cases and over-complicating matters? Just wondering thoughts -- thanks!

Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest". create role authors; create role editors; create user maxwell; create user ernest; grant authors to editors; --editors can do what authors can do grant editors to maxwell; --maxwell is an editor grant authors to ernest; --ernest is an author I would like to write a performant function that returns a list of the roles (preferably their oid's) that maxwell belongs to, something like this: create or replace function get_all_roles() returns oid[] ... It should return the oids for maxwell, authors, and editors (but not ernest). But I am not sure how to do it when there is inheritance.

I installed MariaDB. I started the server as a local server(127.0.0.1:3306). I want to make sure that the www-data user can't access it. I tried to disable the mysql command for that user. But anyone can bypass it by forwarding the port to a different machine and accessing the server through that. I also tried to use IP rules but it didn't work either.

iptables -I OUTPUT -o lo -p tcp --dport 3306 --match owner --uid-owner 33 -j DROP

ip6tables -I OUTPUT -o lo -p tcp --dport 3306 --match owner --uid-owner 33 -j DROP

How can I go around this? My final goal is to make sure that the www-data user can't access the MariaDB server by any means.

I'm trying to add the ability for a user to be authorized with a given role on a contract/site/experiment. More specifically, I want the ability for a user to have role A on a contract but at the same time role B on a specific site of this same contract and possibly a role C on an experiment of this site. My first idea was a join table between User and Role with a third column storing the id of either a contract, site or experiment. This solution has many problems in my opinion: - I need a row for the contract, each authorized sites and each authorized experiments. It can grow very large very quickly. - No integrity. A non existant contract can be referenced - If I want to know all the sites and experiments of a contract where a user is authorized I have to first query all sites of this contract, then all the experiments of these sites and then find all the rows referencing those ids. It seems a bit hacky. I feel like this solution could work but I was wondering if there were other way? Maybe some pattern I am not aware of? PS: I am using MySQL v8

We have an SQL Server 2016 that has 20 different databases on it that are used by different applications and different teams in the company. There are groups of users in Active Directory that combine employees that work on certain projects. The problem is that an employee can be part of several teams at the same time. The same person can belong to several different AD groups. One user complained that he could not access a certain database. When he was trying to refresh the Power BI report that queries that database he was getting an error message "We couldn't authenticate with the credentials provided". Here is a simplified picture. A server has two databases: DB1 and DB2. There are two AD groups App_Excel_Reporter and DB2_User and two corresponding logins in the SQL Server: CREATE LOGIN [LegacyDomain\App_Excel_Reporter] FROM WINDOWS WITH DEFAULT_DATABASE=[master] CREATE LOGIN [Domain\DB2_User] FROM WINDOWS WITH DEFAULT_DATABASE=[master] Not sure if this is important, but this server is a legacy server that was part of another company that the main company bough. So, this SQL Server computer is in domain LegacyDomain. Two AD groups LegacyDomain\App_Excel_Reporter and Domain\DB2_User have their own sets of users, but one user Jack.Universal belongs to both AD groups. Users that are part of App_Excel_Reporter should have access to DB1. Users that are part of DB2_User should have access to DB2. In DB1 there is a user mapped to the corresponding login: USE [DB1] GO CREATE USER [LegacyDomain\App_Excel_Reporter] FOR LOGIN [LegacyDomain\App_Excel_Reporter] WITH DEFAULT_SCHEMA=[dbo] GO In DB2 there is a user mapped to the corresponding login: USE [DB2] GO CREATE USER [Domain\DB2_User] FOR LOGIN [Domain\DB2_User] WITH DEFAULT_SCHEMA=[dbo] GO When a user Jack.Universal tries to refresh the Power BI he is logged into Windows on his laptop as Domain\Jack.Universal and I see these messages in SQL Server log with the same timestamp: Login succeeded for user 'Domain\Jack.Universal'. Connection made using Windows authentication, [CLIENT: ] Error: 18456, Severity: 14, State: 38. Login failed for user 'Domain\Jack.Universal'. Reason: Failed to open the explicitly specified database 'DB2'. [CLIENT: ] When I run xp_logininfo 'Domain\Jack.Universal', @option = 'all' it shows +-----------------------+------+-----------+-----------------------+---------------------------------+ | account name | type | privilege | mapped login name | permission path | +-----------------------+------+-----------+-----------------------+---------------------------------+ | Domain\Jack.Universal | user | user | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter | | Domain\Jack.Universal | user | user | Domain\Jack.Universal | Domain\DB2_User | | Domain\Jack.Universal | user | user | Domain\Jack.Universal | Domain\DB3_User | +-----------------------+------+-----------+-----------------------+---------------------------------+ (yes, there is more than two databases and this user belongs to three AD groups) If I run xp_logininfo 'Domain\Jack.Universal' without option "all", then only the first row is returned: +-----------------------+------+-----------+-----------------------+---------------------------------+ | account name | type | privilege | mapped login name | permission path | +-----------------------+------+-----------+-----------------------+---------------------------------+ | Domain\Jack.Universal | user | user | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter | +-----------------------+------+-----------+-----------------------+---------------------------------+ So, it seems that when a Windows user Domain\Jack.Universal logs into the SQL Server, the SQL Server picks login LegacyDomain\App_Excel_Reporter to let the user in, but when it tries to access a database DB2 this attempt fails because login LegacyDomain\App_Excel_Reporter is mapped only to the user in DB1. "the explicitly specified database" must be due to Power BI's connection string which explicitly specifies DB2. How do we configure this so, that a user who belongs to both AD groups would have access to both databases? Users who belong only to one AD group should have access only to the corresponding database. I am not a domain admin, but I can ask them whatever is needed. The company doesn't have a DBA, I'm just a programmer who knows a thing or two about SQL Server. ---------------- I dug a bit deeper. When I run EXEC xp_logininfo 'Domain\DB2_User', @option = 'members' I get a list of users in that group: +-----------------------+------+-----------+-----------------------+-----------------+ | account name | type | privilege | mapped login name | permission path | +-----------------------+------+-----------+-----------------------+-----------------+ | Domain\user1 | user | user | Domain\user1 | Domain\DB2_User | | Domain\user2 | user | user | Domain\user2 | Domain\DB2_User | | Domain\Jack.Universal | user | user | Domain\Jack.Universal | Domain\DB2_User | +-----------------------+------+-----------+-----------------------+-----------------+ And the users are in Domain as expected. When I run EXEC xp_logininfo 'LegacyDomain\App_Excel_Reporter', @option = 'members' I get this: +-----------------------------+------+-----------+-----------------------------+---------------------------------+ | account name | type | privilege | mapped login name | permission path | +-----------------------------+------+-----------+-----------------------------+---------------------------------+ | LegacyDomain\user3 | user | user | LegacyDomain\user3 | LegacyDomain\App_Excel_Reporter | | LegacyDomain\user4 | user | user | LegacyDomain\user4 | LegacyDomain\App_Excel_Reporter | | LegacyDomain\Jack.Universal | user | user | LegacyDomain\Jack.Universal | LegacyDomain\App_Excel_Reporter | +-----------------------------+------+-----------+-----------------------------+---------------------------------+ Here permission path is in LegacyDomain and account name is also in LegacyDomain, but above in the results of xp_logininfo 'Domain\Jack.Universal' the account name is in Domain, but permission path is in LegacyDomain. Once again: EXEC xp_logininfo 'Domain\Jack.Universal', @option = 'all' +-----------------------+------+-----------+-----------------------+---------------------------------+ | account name | type | privilege | mapped login name | permission path | +-----------------------+------+-----------+-----------------------+---------------------------------+ | Domain\Jack.Universal | user | user | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter | | Domain\Jack.Universal | user | user | Domain\Jack.Universal | Domain\DB2_User | | Domain\Jack.Universal | user | user | Domain\Jack.Universal | Domain\DB3_User | +-----------------------+------+-----------+-----------------------+---------------------------------+ EXEC xp_logininfo 'LegacyDomain\Jack.Universal', @option = 'all' +-----------------------------+------+-----------+-----------------------------+---------------------------------+ | account name | type | privilege | mapped login name | permission path | +-----------------------------+------+-----------+-----------------------------+---------------------------------+ | LegacyDomain\Jack.Universal | user | user | LegacyDomain\Jack.Universal | LegacyDomain\App_Excel_Reporter | +-----------------------------+------+-----------+-----------------------------+---------------------------------+ There must be some magic in Active Directory that maps users from legacy domain into the main domain.

Is there a privilege in Oracle that lets a user view or not view the list of objects in another schema? I've got a user where I've given them select rights to a view in another user's schema. However, they don't seem to be able to see that view in their client (think they are using Oracle's SQL Developer).

I try to apply any of these to create read_only_user: - https://docs.snaplet.dev/guides/postgresql - https://www.commandprompt.com/education/how-to-create-a-read-only-user-in-postgresql/#:~:text=Conclusion-,To%20create%20a%20read%2Donly%20user%20in%20the%20PostgreSQL%20database,schema%2C%20and%20tables%20in%20it . - https://www.keyvanfatehi.com/2021/07/14/how-to-create-read-only-user-in-postgresql/ In all cases, the new read_only_user is able to create a new table in my Database (here: MicroReseau) where I want to prevent that. **Why the read_only_user can create new tables and how to prevent that?** Additional info:

We have a read-only login account that unfortunately was created without proper planning, and now users throughout the company are using this login to access tables and views in our database. The database has grown to include private information we'd rather keep outside of this read-only login. Changing it to access only certain tables is not an option, as it will break other macros and other ODBC related access that could require access to tables we've not thought of. The database has hundreds of tables! Is there a way (auditing preferably), to record all tables or views the user has accessed? I'd rather use auditing than tracing, only due to the possible file size, but I am happy to be convinced on a better method. Has to be for SQL Server 2012 R2. Thanks **UPDATE** I've tried the SQL Audit methods described here , but I don't see options in SQL Server 2012 to audit SELECT for example. I've created a new audit and enabled it. Then I tried to create a new Server Audit Specification, but this is where I am stuck.

I just created a new user dataengineer and user group data-engineer When I try to connect with the user dataengineer using dbeaver I get the following errors. SQL Error : Query failed (#20210927_124120_00084_kcmzr): Access Denied: Cannot select from table system.jdbc.tables Query failed (#20210927_124120_00084_kcmzr): Access Denied: Cannot select from table system.jdbc.tables Query failed (#20210927_124120_00084_kcmzr): Access Denied: Cannot select from table system.jdbc.tables io.trino.spi.security.AccessDeniedException: Access Denied: Cannot select from table system.jdbc.tables Access Denied: Cannot select from table system.jdbc.tables When I try to connect using the same user from the trino cli I get the following errors trino:jdbc> select 1; _col0 ------- 1 (1 row) Query 20210927_143506_00011_m9sds, FINISHED, 1 node https://trino.afsouth1ctcld.net/ui/query.html?20210927_143506_00011_m9sds Splits: 1 total, 1 done (100,00%) CPU Time: 0,0s total, 0 rows/s, 0B/s, 0% active Per Node: 0,0 parallelism, 0 rows/s, 0B/s Parallelism: 0,0 Peak Memory: 0B 0,34 [0 rows, 0B] [0 rows/s, 0B/s] trino:jdbc> select * from system.jdbc.tables limit 10; Query 20210927_144444_00018_m9sds failed: Access Denied: Cannot select from table system.jdbc.tables io.trino.spi.security.AccessDeniedException: Access Denied: Cannot select from table system.jdbc.tables So the user can connect to trino but not query the system tables. If I connect with a different user called data science it works without any issues. This is what my group.txt and rules.json file looks like group.txt: |- data-science:datascience data-engineer:dataengineer rules.json: |- { "catalogs": [ { "user": "dpadmin", "allow": "all" }, { "group": "data-engineering|data-platform-admin", "catalog": ".*", "allow": "all" }, { "group": "data-science", "catalog": "(iceberg)", "allow": "all" }, { "group": "data-science", "catalog": ".*", "allow": "read-only" }, { "catalog": "system", "allow": "none" } ], "schemas": [ { "user": "dpadmin", "schema": ".*", "owner": true }, { "group": "data-engineering", "schema": ".*", "owner": true }, { "group": "data-science", "catalog": ".*", "schema": ".*", "owner": false }, { "user": "guest", "owner": false } ], "tables": [ { "group": "data-science", "privileges": ["SELECT", "INSERT", "DELETE", "OWNERSHIP"], "catalog": "(iceberg)", "schema": "(ds_scratch)" }, { "group": "data-science", "privileges": ["SELECT"], "catalog": ".*", "schema": ".*" } ] }

I have a PostgreSQL database on Azure, and I've created a readonly_user role that allows for external parties to connect to the db in a read-only capacity using a connection string. This readonly_user has been granted SELECT privileges, and it also has USAGE access on the public schema. However, the tables in my database have composite primary keys. When I am logged in as the admin user, I can access these primary keys with the following command: SELECT tc.table_schema, tc.table_name, kcu.column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name WHERE tc.table_name = 'player_data' AND tc.constraint_type = 'PRIMARY KEY'; For some reason, no matter how I try to play with the usage and access roles for readonly_user, this command (and others like it) to access the primary key information for the db tables *always* returns 0 rows. There should be at least 3 for every table, which I see clearly when I issue this command connected as the admin role. Is there something about a read-only user that limits its access to the information_schema? How can I access the primary keys of a table as a read-only user?

I want to check which tables have been updated during a given period, for instance in descending order of access time per table. How can I get that for PostgreSQL?

My question is similar to https://dba.stackexchange.com/questions/54926/rights-to-execute-stored-procedure-but-not-read-underlying-tables-directly , however my storedprocedure reads from different databases. I'm trying to read databases matching some naming pattern, to which the user has access. Everything works per partes, however executing the stored procedure by the dedicated user returns nothing. I believe it's because he doesn't have rights to access the databases (which is correct, a prerequisity). I somehow expected the stored procedure would be able to access them (like in the other thread mentioned above, which I read after that), even though the user can't. Stored Procedure: ALTER PROCEDURE [dbo].[GetUsersDatabases] @UserName varchar(15) AS BEGIN SET NOCOUNT ON; DROP TABLE IF EXISTS [#TMP] CREATE TABLE [dbo].[#TMP] ([DBNAME] NVARCHAR(256) NULL, [NAME] SYSNAME NOT NULL); DECLARE @command varchar(1000) SELECT @command = 'IF ''?'' LIKE ''Pr_%'' BEGIN USE ? INSERT INTO #tmp SELECT ''?'' as DBname, [name] as Uname FROM sys.database_principals WHERE [TYPE] NOT IN (''A'', ''G'', ''R'', ''X'') AND sid IS NOT NULL AND [name] = ''' + @UserName + ''' END' EXEC sp_MSforeachdb @command SELECT * FROM #tmp; END Now I created login and user this way: USE [master] CREATE LOGIN [ListUsersDBs] WITH PASSWORD=N'secretpwd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON GO USE MyDefaultDB CREATE USER ListUsersDBs FOR LOGIN ListUsersDBs GO GRANT EXECUTE ON GetUsersDatabases to ListUsersDBs It all works, if I login as myself and execute exec dbo.GetUsersDatabases @UserName = 'MyUserName' If I log in as the ListUsersDBs, then I get 0 rows with the very same code. The user can access the stored procedure, but it returns no rows for the same input. I think it can be set properly, but I don't know what I'm missing.

I recently wanted to share regular access rights with one user of a server and I realized that a simple CREATE USER and GRANT ALL ON DATABASE commands didn't let him run a simple SELECT on the data. I would like to grant rights to all tables from a given database to a specified user, but I am not sure if it is the best idea to grant him access to whole schema public as I don't know if it would allow some kind of privilege escalation. Is there any other way?