20 votes
Accepted

Postgres get function owner

There is a simple and a complicated way. The complicated way is to join with pg_roles and get the user name from there. But since PostgreSQL hackers don't want to type more than necessary, they came ...
Laurenz Albe's user avatar
  • 50.6k
17 votes

How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data Provider

I think there is no reliable solution for your problem since Application Name is modifiable parameter that cam be changed by any user. Here is how to change it within SSMS: In Connect to Database ...
sepupic's user avatar
  • 10.8k
13 votes
Accepted

How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data Provider

You can use a server logon trigger to make custom logon validations and reject them whenever you see fit. You will see this trigger listed below "Server Objects" and inside "Triggers" if you are using ...
EzLo's user avatar
  • 3,296
9 votes

What does GRANT CONTROL on a schema do in MSSQL?

To see what CONTROL on schema brings to you, create a test user without any permission, grant CONTROL on schema to it and check what permissions he has after it: create user test; grant control on ...
sepupic's user avatar
  • 10.8k
9 votes

Why is database ownership by groups not allowed? Which user should own a database?

Honestly, sa or a disabled SQL Server account with absolutely minimum permissions is the best choice. As to why? Well, I could write out three or four paragraphs on that, or I can share my favorite ...
Laughing Vergil's user avatar
8 votes

Why is database ownership by groups not allowed? Which user should own a database?

Is there an underlying reason why databases cannot be owned by a secondary principal? I'm not 100% certain of the reason for this restriction, though I suspect it has to do with the ability to ...
Solomon Rutzky's user avatar
7 votes

How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data Provider

In the ideal sense, this is a process / policy / management issue. Even if someone knows the password, if it is against company policy for anyone but a DBA to connect to Production (well, you might ...
Solomon Rutzky's user avatar
6 votes
Accepted

How to allow user access to non-owned objects in Postgres?

Read the manual on GRANT and ALTER TABLE. There is no USAGE privilege for tables. You would see an error message if you tried your GRANT command. You need permissions on the schema, too. Here USAGE is ...
Erwin Brandstetter's user avatar
6 votes
Accepted

I'm a MS SQL DB Developer looking to get more permissions on the server

I am wondering if Grant Control on Schema will cover everything I need Will Grant Control give me everything I need as a Database developer. But you didn't write what exactly you need as database ...
sepupic's user avatar
  • 10.8k
5 votes

How to get all roles that a user is a member of (including inherited roles)?

Here is my take on it. It works for one specific user or all users. select a.oid as user_role_id , a.rolname as user_role_name , b.roleid as other_role_id , c.rolname as other_role_name from pg_roles ...
Alexis.Rolland's user avatar
4 votes

How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data Provider

You can't cut off a specific client, as already detailed in the other answers. The solution is to remove the access privileges to the production systems from the developer's accounts. Any change ...
Paolo's user avatar
  • 434
4 votes

How to grant execute permissions to a stored procedure but not to the underlying databases

This is easy to accomplish with Module Signing. The concept is to create a certificate in the database that the user, BI_User, has access to (and contains the stored procedure), [ORMAN], which will ...
Solomon Rutzky's user avatar
4 votes
Accepted

Allow user to GRANT certain permissions to others

you can use GRANT ... WITH GRANT OPTION. https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql?view=sql-server-ver15 this option specifies that the security principal receiving the ...
MBuschi's user avatar
  • 4,440
3 votes

How do I prevent my clients viewing my database definition when it is installed on their server?

Short Answer: you can't. If the database is being run on the customer's infrastructure, then the customer must have control over their own infrastructure. Without it, they cannot operate. If you ...
Phill  W.'s user avatar
  • 8,578
3 votes

Scipt User roles and permissions

I recommend you have a look at the procedure sp_dbpermissions by Kenneth Fisher. I have been using it and it allows you to list all the permissions of a particular user in a database, and gives you ...
Marcello Miorelli's user avatar
3 votes
Accepted

What are the real life uses of role based authentication control in a database?

You can group several privileges into one Role. It is simpler to grant roles rather than dealing with a bunch of privileges. In a multi-tier architecture this might be no so important. You may have ...
Wernfried Domscheit's user avatar
2 votes
Accepted

Revoking execute access on builtin functions on SQL Server

You cannot restrict permissions to LEFT built-in function. You can control permissions that are listed by the output of this command: SELECT * FROM fn_builtin_permissions(default)
Serge's user avatar
  • 542
2 votes
Accepted

Access rights on SQL Server 2012

GRANT ALTER ANY DATABASE TO [somelogin]; or adding the user to the db_creator server-level role, will allow that login to create and/or restore databases, however, that user will not be able to access ...
Hannah Vernon's user avatar
  • 70k
2 votes
Accepted

How do I Configure Per Database Roles for PostgreSQL?

The thing you remembered and are looking for is db_user_namespace. It is not a very good solution, but I wouldn't hold my breath waiting for something better.
jjanes's user avatar
  • 39.5k
2 votes

How do I Configure Per Database Roles for PostgreSQL?

There is no direct way. Roles are stored in the cluster-wide system table pg_authid And, quoting the manual: Because user identities are cluster-wide, pg_authid is shared across all databases of a ...
Erwin Brandstetter's user avatar
2 votes

Scipt User roles and permissions

You can accomplish this via the dbatools powershell module. There is an Export-SqlLogin cmdlet that will do nearly exactly what you describe. From the documentation, this cmdlet will: Exports ...
AMtwo's user avatar
  • 16.1k
2 votes

In trino I keep getting `Access Denied: Cannot select from table system.jdbc.tables`

This is related to a problem with how the trino authorization system works. Take from the git issue You will need to create policies at the Catalog level, Schema level and Table level in order to ...
nelaaro's user avatar
  • 727
2 votes

Enable remote access to database via proxy server

Have you thought of something like humongous.io (disclaimer I'm a co-founder)? It's an online GUI for MongoDB. You whitelist one IP address (Humongous' server) and your team can access MongoDB through ...
Kamagatos's user avatar
  • 121
2 votes
Accepted

Why can't my read-only user role see any of my tables' primary key information in psql?

This is by design. The SQL standard defines (ISO/IEC 9075-11:2003, 5.58): CREATE VIEW TABLE_CONSTRAINTS AS ... WHERE ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ) IN ( SELECT TP....
Laurenz Albe's user avatar
  • 50.6k
1 vote

How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data Provider

I previously worked for a company that had this problem with one developer. He was fired, but we also implemented a table that had the LoginName and AllowedMachine (Application Server) via a Login ...
TerryCC's user avatar
  • 11
1 vote
Accepted

Is it possible to grant role privileges to a user by document (not collection) in MongoDB?

No, you cannot grant rights for document... But you can do views what shows only that public information.
JJussi's user avatar
  • 5,703
1 vote
Accepted

`"C:\xampp\mysql\bin"' is not recognized as an internal or external command,operable program or batch file

You might want to try the following instead: C:\xampp\mysql\bin\mysql.exe -u root -p assuming you are using a CMD / DOS Prompt Depending on the path in which your mysql.exe resides, you might have ...
John K. N.'s user avatar
  • 17.6k
1 vote
Accepted

Designing Database access levels

Having generalized security across all servers / applications would be a bad approach. With your suggestion, once you let John Doe from Customer Service have read access the Customer database (which ...
Colin Douglas's user avatar
1 vote
Accepted

pg_hba.conf entry query

Your understanding is correct. For every connection attempt, each line in pg_hba.conf is checked in turn. The first matching line applies and the connection then either passes with this or fails ...
Erwin Brandstetter's user avatar
1 vote
Accepted

Need help designing an Access Control List (ACL) database structure

I favor computing and materializing granular object access entitlements so they are simple and cheap for the database to enforce at runtime. And to simplify the relational model. Order(OrderId, ...) ...
David Browne - Microsoft's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible