Stack Overflow
Stack Overflow

Questions tagged [database-permissions]

238 questions
222
votes
4 answers

What does GRANT USAGE ON SCHEMA do exactly?

I'm trying to create a Postgres database for the first time. I assigned basic read-only permissions to the DB role that must access the database from my PHP scripts, and I have a curiosity: If I execute GRANT some_or_all_privileges ON ALL TABLES IN…
Marco Sulla
  • 15,299
  • 14
  • 65
  • 100
222
votes
8 answers

Is it better to use multiple databases with one schema each, or one database with multiple schemas?

After this comment to one of my questions, I'm thinking if it is better using one database with X schemas or vice versa. I'm developing a web application where, when people register, I create (actually) a database (no, it's not a social network:…
Strae
  • 18,807
  • 29
  • 92
  • 131
46
votes
5 answers

Permissions for truncating a table

What permission do I need to GRANT a user, in MSSQL, in order to be able to truncate a table? I'm trying to grant the minimal set of permissions, but I can't use DELETE, because the table is very large, and I want the operation to be quick.
Shahar Mosek
  • 1,922
  • 3
  • 17
  • 27
40
votes
5 answers

Grant a user permission to only view a MySQL view and nothing else

This question was originally using MySQL 5.1.44, but is applicable to MySQL 8.0+ too. Let's say I have a table with records inserted by different users of my application. How can I give a specific user access to only see his/her records in that…
Nelson
  • 49,283
  • 8
  • 68
  • 81
38
votes
3 answers

sql open connection in read only mode

Suppose a user has full read/write access to MySQL database. Is there any way (some parameter in connection string) to connect to database by the same username and password in read-only mode? I want this without changing this user's permissions…
gtiwari333
  • 24,554
  • 15
  • 75
  • 102
34
votes
7 answers

How to create a user with readonly privileges for all databases in Postgresql?

I want to create a user with only select privilege for all tables in all databases. I thought that I could get a list of databases and apply the following command for each database: GRANT select ON DATABASE dbname to user1; But I got the following…
Alptugay
  • 1,676
  • 4
  • 22
  • 29
29
votes
3 answers

SQL Server Disabling All Triggers - Cannot find the object "XXXX" because it does not exist or you do not have permissions

I'm trying to run this command in SQL Server: disable trigger all on MYDB This is failing for me. The account I'm logged into has access to MYDB and I've pretty much giving it every single permission available (it's a local DB and my account only,…
Brian Mains
  • 50,520
  • 35
  • 148
  • 257
28
votes
3 answers

Grant permissions to user for any new tables created in postgresql

Currently I am using this to grant permissions: grant select on all tables in schema public to ; alter default privileges in schema public grant select on tables to ; According to the documentation, the second statement…
ishan
  • 1,029
  • 1
  • 12
  • 19
27
votes
4 answers

Amazon Redshift Grants - New table can't be accessed even though user has grants to all tables in schema

I have a bit of a funny situation in Amazon Redshift where I have a user X who has grant select on all tables in schema public, but once a new table is created, this grant doesn't seem to apply to the new table. Is this normal behaviour? If yes, how…
elvikingo
  • 947
  • 1
  • 11
  • 20
25
votes
4 answers

Why did PostgreSQL merge users and groups into roles?

From the PostgreSQL docs: The concept of roles subsumes the concepts of "users" and "groups". In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities, but now there are only roles. Any role can act as a user, a…
KajMagnus
  • 11,308
  • 15
  • 79
  • 127
23
votes
4 answers

Grant privileges on several tables with specific prefix

I'm using the table prefix method for having several clients use the same database. The number of tables created per client will be ~55. Instead of doing all of the granting by manually listing out the tables, can I do something like the…
Chad
  • 714
  • 2
  • 9
  • 26
20
votes
9 answers

The error "Login failed for user 'NT AUTHORITY\IUSR'" in ASP.NET and SQL Server 2008

My ASP.NET v3.5 web application is throwing the following exception when it attempts to open a connection to a SQL Server 2008 database: System.Data.SqlClient.SqlException: Cannot open database "MyDbName" requested by the login. The login failed.…
Justin R.
  • 23,435
  • 23
  • 108
  • 157
13
votes
1 answer

Efficiently determine owner of a record in a hierarchy with MongoDB

I am trying to achieve the following: Select all the records that I own, where ownership is objects I have created OR objects a user I manage has created, where user management can be in a hierarchy of users managing users Ownership is clearly…
Toby Hede
  • 36,755
  • 28
  • 133
  • 162
12
votes
3 answers

Listing table results in "CREATE TABLE permission denied in database" ASP.NET - MVC4

I'm using ASP.NET MVC 4 - c# to connect to a live database, and list the results, however when I go to view the page it returns the following error: CREATE TABLE permission denied in database 'DatabaseName'. Description: An unhandled exception…
user2046422
11
votes
1 answer

Allowing a stored procedure to select from a system table using a certificate in SQL Server 2012

I'm currently having issues relating to permissions when attempting to run a stored procedure that tries to select from a system table. Basically I'm trying to work around the deprecation of the sp_bindtoken system stored procedure in SQL Server…
PairOne
  • 111
  • 6
1
2 3
…
15 16