Questions tagged [permissions]

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

Filter by
Sorted by
Tagged with
245
votes
10answers
674k views

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 ...
194
votes
3answers
185k views

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?
167
votes
19answers
54k views

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 ...
112
votes
10answers
304k views

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?
63
votes
3answers
66k views

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 ...
60
votes
1answer
65k views

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 ...
58
votes
4answers
61k views

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 ...
54
votes
2answers
67k views

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 ...
42
votes
2answers
147k views

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 ...
41
votes
4answers
133k views

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 ...
34
votes
3answers
144k views

Permission denied in Postgres [duplicate]

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 ...
33
votes
6answers
83k views

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?
32
votes
2answers
294k views

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 ...
30
votes
2answers
85k views

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 ...
27
votes
4answers
48k views

How to clone a user in SQL Server 2008 R2?

Is there a way to clone users' security and permissions in Microsoft SQL Server, preferably using the SQL Server Management Studio GUI?
27
votes
5answers
234k views

CREATE DATABASE permission denied in database 'master'. unable to get the permission

I am making a database in sql server and it shows an error that "CREATE DATABASE permission denied in database 'master'" I am using the administrator log in itself. What i need to do? Please suggest a ...
27
votes
3answers
79k views

Giving file system access to the SQLSERVERAGENT virtual account

I'm trying to grant the NT Service\SQLSERVERAGENT Windows account file system access, based on the marked answer in this question. I believe it's a virtual service account, and it does not show up in ...
26
votes
4answers
3k views

Should I explicitly DENY UPDATE to columns that should not be updated?

I am used to working in very secure environments and so I design my permissions to a very fine degree of granularity. One thing that I normally do is to explicitly DENY users the ability to UPDATE ...
24
votes
3answers
117k views

Permission denied for relation <table>

I have ran the following SQL in psql: CREATE USER bspu LOGIN; CREATE DATABASE bsp OWNER bspu; GRANT ALL PRIVILEGES ON DATABASE bsp TO bspu; \c bsp CREATE TABLE users ( id SERIAL PRIMARY KEY, ...
24
votes
2answers
73k views

DDL_admin vs db_owner permissions

I'm taking over a project that involves removing and limiting permissions of all database users across our server farm. (fun times) One of the permissions currently being limited is db_owner ...
23
votes
6answers
27k views

How can I create readonly user for backups in PostgreSQL?

Is it true that it is IMPOSSIBLE to create a readonly backup user in PostgreSQL? I've been advised on an IRC channel that you simply can't have a backup only user with no ownership privileges. I ...
23
votes
3answers
116k views

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 ...
23
votes
4answers
93k views

Grant permissions to run an SQL server job

I have a job on my MSSQL server 2005, that I want to allow any database user to run. I'm not worried about security, since the input to the job's actual work comes from a database table. Just running ...
23
votes
1answer
20k views

CREATE DATABASE vs. CREATE ANY DATABASE permissions

In Microsoft SQL Server, what is the difference between the CREATE DATABASE and CREATE ANY DATABASE permissions? I am unable to find an authoritative answer. The best I can infer is that either (a) ...
22
votes
8answers
84k views

A query that lists all mapped users for a given login

When looking at the properties of a particular login, it's possible to see a list of users mapped to that login: I profiled SQL Server Management Studio (SSMS) and I see that SSMS connects to every ...
22
votes
4answers
16k views

Why is BULK INSERT Considered Dangerous?

I would like to understand why cyber-security teams in general (more than one organization I've dealt with) is dead set against granting BULK INSERT (e.g. TSQL) rights to applications and database ...
20
votes
5answers
75k views

What permissions are necessary for truncating a table?

I have a SQL account with the following permissions on a database: The db_executor role you see this account being a member of was created by this script: CREATE ROLE [db_executor] AUTHORIZATION [...
20
votes
2answers
26k views

Broken NTFS Permissions for MSSQL$SQLEXPRESS

I have been "securing" our database server. To do this I removed some permissions from the folders that the SQL Express install created. D:\SQL D:\SQL\Data D:\SQL\Backup etc.. I kept the ...
20
votes
1answer
15k views

Why do I get a PostgreSQL permission error when specifying a tablespace in the "create database" command?

When I create a database in PostgreSQL without explicitly specifying a default tablespace the database is created without issue (I'm logged in as the pgsys user): postgres=> create database rich1; ...
18
votes
4answers
46k views

Granting Select access to all tables within a specific Database in MS SQL

I have a server that has multiple databases that are contained within that same server/project. I am using MS SQL Server 2012. I have a special role set up with 3 users that are apart of that role. I ...
18
votes
3answers
40k views

Find objects linked to a PostgreSQL role

Some times ago I created a PostgreSQL user named user1 (PostgreSQL 9.4.9). I want to drop this user. So I first revoke all permissions on tables, sequences, functions, default privileges and ...
18
votes
2answers
111k views

"ORA-01950: no privileges on tablespace 'SYSTEM' " : What error is it?

Earlier I created a user : SQL>create user suhail identified by password SQL>User created. SQL>Grant create session,create table to suhail; SQL>Grant Succeeded. but when I login as ...
18
votes
2answers
41k views

Why scalar valued functions need execute permission rather than select?

I am wondering why, for scalar valued function, that I have to grant the user to execute rather than just a select? meanwhile a table valued functions works just fine with only select permission or ...
17
votes
2answers
31k views

How to create a read-only server role on SQL Server 2012?

I am granting the "View any database" permission when creating a new server role, but realized this permission only allows the user to view the system databases. I am trying to create a server role ...
17
votes
1answer
62k views

Grant access to all tables of a database

I recently wanted to share regular access rights with one user of a server and I realized that a simple CREATE USER and GRANT ALL ON DATABASE commands didn't let him run a simple SELECT on the data. ...
17
votes
3answers
63k views

Can't start MySQL server because of wrong file permissions?

I'm trying to start the server and then fix the root user by $ mysql -u root mysql $mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root'; $mysql> FLUSH PRIVILEGES; But the ...
17
votes
2answers
23k views

"Access is denied" when connecting SSMS to Integration Services

I receive the following error when attempting to connect SSMS to Integration Services using a particular SQL Server cluster's network name: Connecting to Integration Services service on the ...
16
votes
2answers
10k views

What are reasonable privileges to grant typical users? [closed]

I find the list of privileges provided by MySQL to be a bit overwhelming. I'm not sure who should have what privileges. In my mind there are three typical users for my situation: root developer ...
16
votes
5answers
42k views

Property Size is not available for database

I recently restored a database to the same instance it was backed up from (SQL Server 2008 R2 Enterprise) and found that I couldn't access the database properties. I have done the following: Checked ...
16
votes
4answers
62k views

SQL Server 2008 R2 error: 15023, user, group, or role already exists

I have a test database that I'm having permissions issues with. I can't access the reporting database, and the application's help documentation says to do the following: Resolution: 1. Launch the ...
16
votes
1answer
480 views

In what order does PostgreSQL check object permissions?

Given a database role, user1, a function something() defined as a stored procedure and a view created like: CREATE VIEW view1 AS select * from something() And, given this permissions: REVOKE ALL ON ...
15
votes
2answers
11k views

How to view the query of another session in pg_stat_activity without being super user?

I have a Postgresql 9.2 database. Two users are created on this database. When I perform the following query as superuser, I can see everything. select * from pg_stat_activity However, is it ...
15
votes
3answers
35k views

can't remove "GRANT USAGE"

I was testing some stuff, and added a: grant usage on statistics.* to cptnotsoawesome@localhost identified by 'password'; so now when I do show grants for cptnotsoawesome@localhost; I can see that ...
15
votes
1answer
21k views

Minimal grants for readonly single-table access on PostgreSQL

Following is a list of commands that seems to work to create new user (login) and grant readonly access for one specified table on PostgreSQL. Let's assume that these commands are executed on login ...
15
votes
1answer
2k views

Are there reasons why I should not set my db owner to [sa]?

Yesterday I asked this question regarding changing the dbo of multiple databases that I have. The change makes sense, but I want to be clear. Is there any, good reason or circumstance why I should ...
15
votes
1answer
11k views

Running SSIS package from SQL Agent job owned by a non-sysadmin domain user

I have two SSIS packages that run scheduled overnight (via SQL Server Agent) as part of a larger SSIS deployment, without any issues. Everything is using Windows authentication, and the scheduled job ...
15
votes
1answer
23k views

Postgresql enabling extensions without super-user

I have a PostgreSQL 9.5 server on which I have scripts that create roles and databases for users automatically. Within these databases it would be helpful to enable specific extensions (e.g. pgcrypto),...
15
votes
1answer
4k views

How do I find out *why* a user has certain effective permissions?

I know that I can query effective permissions by using sys.fn_my_permissions: USE myDatabase; SELECT * FROM fn_my_permissions('dbo.myTable', 'OBJECT') entity_name | subentity_name | ...
14
votes
2answers
44k views

Grant SELECT on all tables in Redshift

I am trying to assign SELECT privilege to a group in Redshift. So I created a group and a user in that group: CREATE GROUP data_viewers; CREATE USER <user> PASSWORD '<password>' IN GROUP ...
14
votes
1answer
115k views

SQL-Server - Grant user DBO permission on database

I have a reporting database that I restore every night from a backup of the production database. When I restore, I need to add a user to the DBO role for that database. I thought this would work: ...

1
2 3 4 5
30