25 votes

Grant access to all tables of a database

Postgres 14 ... adds the predefined role pg_read_all_data to make this simple: GRANT pg_read_all_data TO myuser; The manual: pg_read_all_data Read all data (tables, views, sequences), as if having ...
user avatar
24 votes

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

This is a simplified version of Craig Ringer's answer that a non superuser can use directly: SELECT oid, rolname FROM pg_roles WHERE pg_has_role( 'maxwell', oid, 'member'); pg_roles is ...
user avatar
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 ...
user avatar
  • 10.3k
14 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 ...
user avatar
  • 33.4k
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 ...
user avatar
  • 3,239
10 votes
Accepted

PostgreSQL: a Login Role to only access Views

Unfortunately that's not that easy because there is no "select any view" privilege (as e.g. Oracle offers). If you can put all views into a single schema, then this could be achieved by granting the ...
user avatar
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 ...
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 ...
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 ...
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 ...
user avatar
  • 10.3k
5 votes
Accepted

SQL Server Chain Permission

Unless you have cross-database ownership chaining enabled (off by default) and have the same User in both databases, then giving access to an object in one database does not imply anything permission-...
user avatar
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 ...
user avatar
5 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 ...
user avatar
5 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 ...
user avatar
  • 10.3k
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 ...
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 ...
user avatar
4 votes
Accepted

Allow user to GRANT certain permissions to others

you can use GRANT ... WITH GRANT OPTION. https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql?view=sql-server-ver15 this option specifies that the security principal receiving the ...
user avatar
  • 3,462
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 ...
user avatar
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 ...
user avatar
  • 6,593
3 votes
Accepted

Block user access to certain tables

I've had to solve this specific problem and realized there's not way to say "all tables except these". What I did to work around this was create an internal database, restricted_tables. It had a ...
user avatar
  • 5,153
3 votes

Block user access to certain tables

For MySQL in addition to the GRANT command, there is also a REVOKE command. However, REVOKE has limitations. If you GRANT ALL ON mydb.* TO 'someuser'@'somehost' you cannot remove one table by ...
user avatar
  • 13.8k
3 votes
Accepted

database design for dynamic menu bar

If you were using SQL Server, what you are looking for is something like: USE tempdb; CREATE TABLE MenuHeaders ( MenuHeaderID INT NOT NULL CONSTRAINT PK_MenuHeader PRIMARY KEY CLUSTERED ...
user avatar
  • 67.2k
3 votes

Why can a new user select from any table?

Also addressing the question in the comments. Role public Per documentation: The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be ...
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 ...
user avatar
2 votes
Accepted

Why can a new user select from any table?

To address the question of why test can SELECT from tables without a GRANT, it is because you have not explicitly run REVOKE for the ability to connect to your database(s) from PUBLIC, and then ...
user avatar
2 votes
Accepted

How to set user-level security in 2007+ MS Access?

As I was stuck in not seeing "User-Level Security Wizard", I made my way to access it by including this item to the custom ribbon tab: After that I was able to continue and create users for the DB.
user avatar
  • 141
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)
user avatar
  • 542
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 ...
user avatar
  • 14.4k
2 votes
Accepted

Individual SQL account per user?

Yes, plenty :) You will have to setup encryption from the application server to the database server as the password is passed in clear text. All login requests from the internet will result in an ...
user avatar
  • 4,534
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 ...
user avatar
  • 67.2k

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