Questions tagged [permissions]

Privileges granted to an account or role through the security mechanism of an operating system, database manager or other system.

Various systems, including database management systems, must be able to provide security that prevents unauthorised access or modification of data. Security mechanisms grant permissions to users or roles (groupings of users) that allow or restrict sessions with those credentials from doing certain actions.

Allowable actions that may be controlled by the security system include insert or read permissions on tables, permissions to alter the structure of the database, execute stored procedures, or carry out certain administrative tasks.

'Permissions' typically refers to the set of allowable actions that a given login or role may perform based on the access configured for that role in the systems security manager.

1542 questions
279
votes
10 answers

List the database privileges using psql

I'm in the middle of a database server migration and I can't figure (after googling and searching here) how can I list the database privileges (or all the privileges across the server) on PostgreSQL using the psql command line tool? I'm on Ubuntu…
pedrosanta
  • 3,093
  • 3
  • 15
  • 7
212
votes
3 answers

Granting a user account permission to create databases in PostgreSQL

How do I give a user account in PostgreSQL the ability to create and drop databases? Is there a way to do this with GRANT?
GSto
  • 2,223
  • 2
  • 13
  • 6
174
votes
19 answers

Should developers be able to query production databases?

Should developers be given permission to query (SELECT / read only) production databases? The previous place I worked, the development team had the db_datareader role; where I work now the development team can't even connect to the production…
Tom Hunter
  • 2,139
  • 3
  • 16
  • 11
123
votes
10 answers

mysql: Show GRANTs for all users

MySQL's SHOW GRANTS shows the permissions of the current user. Is there a way to log in as root and show the permissions of all users?
Adam Matan
  • 11,019
  • 29
  • 79
  • 94
69
votes
1 answer

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

I want to migrate a fairly simple, internal, database driven application from SQLite3 to PostgreSQL 9.3 and tighten the permissions in the DB as I go. The application currently consists of a command to update the data; and one to query it.…
Jim Dennis
  • 745
  • 1
  • 6
  • 12
67
votes
3 answers

Why is a new user allowed to create a table?

I'm wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core: postgres=# \l List of databases Name | Owner |…
andreas-h
  • 803
  • 1
  • 6
  • 6
64
votes
4 answers

Created user can access all databases in PostgreSQL without any grants

I must be missing something with regards to setting up PostgreSQL. What I'd like to do is create multiple databases and users that are isolated from each other so that a specific user only has access to the databases I specify. However, from what I…
mikeplate
  • 743
  • 1
  • 5
  • 4
57
votes
2 answers

What is the purpose of the database 'owner'?

Today while troubleshooting a service broker problem, I discovered that the database owner was the Windows login of an employee who had left the company. His login had been removed and thus the query notifications were failing. Supposedly the best…
8kb
  • 2,609
  • 2
  • 30
  • 35
44
votes
2 answers

SQL Server - granting permissions to an entire schema vs. object?

I'm very green when it comes to the world of database permissions management in SQL Sever. Let's keep an example simple. Say account 'admin' is the owner of schemas A, B, and C. There another account 'minion' that you want to have full rights…
user45867
  • 1,553
  • 5
  • 21
  • 30
42
votes
4 answers

How do I change the DEFINER of a VIEW in Mysql?

When I run mysqldump, I get an error: mysqldump: Got error: 1449: The user specified as a definer ('root'@'foobar') does not exist when using LOCK TABLES This makes sense because foobar is a legacy machine that no longer exists. How do I change the…
kfmfe04
  • 819
  • 2
  • 8
  • 12
39
votes
6 answers

PostgreSQL Change owner of all tables under a specific schema

I am trying to change the owner of all tables under the same schema in one command line. i.e: alter table schema_name.* owner to newowner. Is there a way to accomplish that?
Twisted Fate
  • 491
  • 1
  • 4
  • 3
38
votes
3 answers

postgres: permission denied for schema

I am new to postgresql and I would grateful if you could please advise on how-to resolve the following error.. I have issued the followed commands: ip_spotlight-# REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA doc FROM PUBLIC ; ip_spotlight-# REVOKE…
nskalis
  • 1,401
  • 4
  • 12
  • 12
37
votes
2 answers

Permission denied in file trying to import

When I try to use \i on a file that is not in the psql.exe folder it says C:: permission denied. For example I have a file with SQL command at C:\Users\Work\Desktop\School Work\load_database.sql and when I type \i "C:\Users\Work\Desktop\School…
Celeritas
  • 863
  • 3
  • 11
  • 15
36
votes
3 answers

Permission denied in Postgres

I added the user myuserto Postgres. Then I added the database mydatabase in the pgAdmin III GUI and restored from a backup file. So the owner of mydatabase is the superuser postgres. Then I tried to give all rights to access and modify mydatabase to…
Aslan986
  • 671
  • 2
  • 6
  • 10
34
votes
2 answers

oracle - list users with access to certain tables

I'm sure this has been asked before but I can't seem to find the relevant details for the following. Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it is limited and does not meet all my needs), if not…
dgf
  • 359
  • 1
  • 3
  • 4
1
2 3
99 100