19
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 ...
- 47.8k
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 ...
- 10.6k
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 ...
- 3,286
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 ...
- 2,023
8
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 ...
- 10.6k
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 ...
- 68.3k
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 ...
- 68.3k
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 ...
- 172k
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 ...
- 10.6k
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 ...
- 151
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 ...
- 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 ...
- 68.3k
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 ...
- 4,260
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 ...
- 8,259
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 ...
- 15.8k
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 ...
- 2,923
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)
- 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 ...
- 69.7k
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.
- 38.6k
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 ...
- 172k
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 ...
- 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 ...
- 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 ...
- 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....
- 47.8k
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 ...
- 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.
- 5,693
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 ...
- 17.4k
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 ...
- 236
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 ...
- 172k
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, ...)
...
- 45.9k
Only top scored, non community-wiki answers of a minimum length are eligible