Questions tagged [permissions]
Privileges granted to an account or role through the security mechanism of an operating system, database manager or other system.
1,610 questions
0
votes
1
answer
1k
views
Grant permissions only if an object exists, but without mentioning the object twice
We have a number of database build scripts that grant permissions to specific objects (sprocs, functions, etc) only to specific roles. We have recently moved to an automated deployment model that has ...
CommunityBot
- 1
0
votes
0
answers
72
views
SQL Agent job step update permissions
I have a non-sysadmin user whose ID (Windows login) is set as owner on a set of SQL Agent jobs. The user also has the SQLAgentReader role in MSDB. The user can see the Agent node in SSMS, can view ...
1
vote
1
answer
6k
views
How to check who has permission to execute a stored procedure?
I have two environments/servers. Each has two databases, say DATABASE_A and DATABASE_B. There is a stored procedure in DATABASE_A (in both environments) that queries DATABASE_B and also calls multiple ...
CommunityBot
- 1
0
votes
1
answer
354
views
SQL Developer: View only permissions
I am using SQL Developer, generally to assist in writing reports against various databases but there is one application that is run on an annual basis and at the end of that processing (about a 3 hour ...
CommunityBot
- 1
0
votes
1
answer
398
views
Disabled SA user is listed as logged in
I was looking for the last time users have been logged into the system and saw SA as being used. Strange is that SA is disabled by default and not used on our systems. How is that possible, what am I ...
CommunityBot
- 1
3
votes
1
answer
620
views
How to set minimal permissions to change SQL Azure Firewall rules
I have a desktop app that connects to SQL Azure database.
Some users have dynamic IP addresses, and it is not possible to get them to use fixed addresses.
I've written an ASP.NET page hosted on the ...
CommunityBot
- 1
0
votes
1
answer
404
views
Can Postgres restrict usage of the SET and SHOW commands?
Is it possible to restrict a role from being able to set arbitrary session variables, or from being able to view them?
select current_setting('x');
show x;
Even after I've switched roles to a one ...
CommunityBot
- 1
1
vote
1
answer
1k
views
Granting specific permissions on ALL tables
I have a tutorial that users need to type in PostgreSQL commands. I don't give the script to users simply because it's a learning experience. Everything in the script below is typed in except for the ...
CommunityBot
- 1
0
votes
1
answer
2k
views
Grant PG permissions to not existing tables
I would like to have a dedicated user in the schema, that can have access to tables/objects by pattern like foo* (starts with foo).
He should have permissions to create new tables like foo_table1 and ...
- 204
0
votes
1
answer
1k
views
The SELECT permission was denied on the object '__EFMigrationsHistory', database 'master', schema 'dbo'
I'm trying to create asp.net core web api. (https://youtu.be/EmV_IBYIlyo?si=GcPVN7BuautDy6-j&t=1351) At the point where he executes Add-Migration InitialCreate and Update-database my console ...
CommunityBot
- 1
0
votes
1
answer
417
views
SQL Server permissions to create arbitrary custom-types and stored procedures with them
What are the most restrictive permissions to allow for creation of arbitrary custom types and stored procedures with use the types in SQL Server?
For instance:
create type dbo.MyType as table (dt: ...
CommunityBot
- 1
0
votes
1
answer
3k
views
Grant VIEW DATABASE STATE on master DB
I am asked to provide access to VIEW DATABASE STATE in the master DB of an Azure SQL Server instance (Premium P6) to an external user (vendor) in order some investigation to be done.
When run "...
CommunityBot
- 1
0
votes
1
answer
6k
views
Postgresql: Permission denied to schema to user created based on master with rights
I'm trying to write a function that eventually will rotate users. Currently I have the code below which works and creates a new user using the prior user which ultimately has the same rights as the ...
CommunityBot
- 1
2
votes
2
answers
139
views
Need to add user and assign roles automatically, using DDL Trigger, when database is created
I am trying to create an automated process that will add a user and assign them the roles of db_datareader, db_datawriter and db_ddladmin whenever a database starting with PA is created. I have ...
- 5,926
8
votes
5
answers
29k
views
Export all MySQL users
I need export all of a MySQL database users (including permissions) for a migration. There is over 5,000 users, what is the best way to do this?
Should I just dump the mysql.user table?
- 1,762
1
vote
2
answers
84
views
User can't access a database because he belongs to multiple AD groups
We have an SQL Server 2016 that has 20 different databases on it that are used by different applications and different teams in the company. There are groups of users in Active Directory that combine ...
- 14.5k
0
votes
2
answers
4k
views
Grant All on All tabes in schema?
I have a question about grants...
I ran the following command, assuming it would allow me to make any changes to the tables in the public schema:
grant all on all tables in schema public to User1;
...
CommunityBot
- 1
7
votes
2
answers
11k
views
Execution-Policy Error running Powershell Script in SQL Server Agent
Running a powershell script from SQL Server Agent in 2014 using my AD account via a credential. I am getting the following error.
A job step received an error at line 1 in a PowerShell script. The
...
- 115
0
votes
1
answer
63
views
Mysql replication - Access denied for user 'repl'@'hostname' (using password: YES)
I had setup mysql replication using percona Xtrabackup. Replication was from my web server to my local machine. It used to work but my ISP changed IP address and replication stopped working. I'm ...
- 101
0
votes
2
answers
1k
views
ALTER permission on database allows creation of security principals
It appears the ALTER permission granted at the database level allows the principal to which it is granted, to create security principals in the database.
Consider the below example:
/* setup database *...
- 1
0
votes
1
answer
24
views
Same global user works local but fails on live server for information_schema database UPDATE query
I have two database servers, local and live.
The local server is running MariaDB 10.11.6, the live server is running 10.11.8.
I have the exact same global user, example@localhost on both the local and ...
- 737
0
votes
2
answers
2k
views
Postgres view role permissions
I am trying to work out how database roles work.
Here is my user case...
create role user1 login password 'user1';
create schema authorization user1;
CREATE ROLE DEV_ROLE;
grant connect, temporary ...
- 3,363
1
vote
2
answers
135
views
error when re-applying the permissions script into the original database - sql server
when I run the following script:
-- ——— SCRIPT GRANTS for Object Privileges—————
IF OBJECT_ID('[sys].[sysrowsets]') IS NOT NULL GRANT CONTROL on [sys].[sysrowsets] to [db_myrole_BA]
I get this ...
- 14.5k
34
votes
5
answers
192k
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,
...
- 182k
0
votes
2
answers
71
views
How do I prevent a DB user to change table contents in SQL Server?
How do I specify that a DB user cannot edit the AspNetUserRoles table in SQL Server? Currently, the user has Membership->db_owner permission, and it can do everything.
I would like to control ...
- 4,685
0
votes
1
answer
287
views
How can I allow a user to alter tables they do not own in Postgres?
I need to be able to assign a role to users to allow them to do anything they want to tables in a schema. I need to do this without assigning superuser priv in Postgres.
I already figured out how to ...
- 56.4k
0
votes
0
answers
198
views
Permission denied to ALTER DEFAULT PRIVILEGES
I'm trying to alter the default privileges of a role (roleB) that I am a member of, to allow it to modify objects created by another role (roleA) that I am also a member of.
I have the inherit ...
- 1
1
vote
1
answer
10k
views
NT SERVICE\MSSQL$SQLEXPRESS user cannot be found when added to network drive
For SQL Server 2016 Express, I would like to backup/restore directly to/from network storage I have mapped to drive S:. SQL Server Express runs under the (virtual?) account NT SERVICE\MSSQL$SQLEXPRESS ...
CommunityBot
- 1
0
votes
1
answer
291
views
Checking REFERENCES permission is granted or not?
I am trying to execute the following minimalized example on SQL Server (14.0.3465.1):
--drop TABLE [dbo].[testtable]
--drop TABLE [dbo].[referencetable]
create table [dbo].[referencetable](
[...
- 101
1
vote
1
answer
52
views
PostgreSQL crosstab query not using view owner permission when queried from other users
I'd like some help with regards to creating a view in PostgreSQL using the crosstab function.
I've been trying to create a view as follows:
create or replace view csv_data as with answers as(
select * ...
- 21
0
votes
1
answer
124
views
PostgreSQL: permission denied to COPY to or from an external program, even after Grant pg_execute_server_program
Running Copy test(id, name) From Program 'zcat /tmp/test.csv.gz' CSV Header; fails with:
SQL Error [42501]: ERROR: permission denied to COPY to or from an external program
Detail: Only roles with ...
- 131
4
votes
1
answer
435
views
Granting SELECT...INTO permissions
I've created a new schema, archive, and am able to create, read, update and delete tables within it.
I have an EXTERNAL_USER who I'm trying to give permissions to so that they can carry out select.....
- 90.3k
2
votes
2
answers
403
views
In Postgres, can an isolation level be set as mandatory?
At https://wiki.postgresql.org/wiki/Serializable#PostgreSQL_Implementation, it says
Any transaction which is run at a transaction isolation level other than SERIALIZABLE will not be affected by SSI. ...
- 121
2
votes
3
answers
7k
views
How do I grant PostgreSQL superuser access to vacuum tables?
My logs are constantly filling up with these messages for hundreds of tables:
WARNING: skipping "some_table_name" --- only table or database owner can analyze it
How do I perform any the following ...
- 101
5
votes
2
answers
10k
views
Can I GRANT SELECT on all schemas in a database?
I need to create role users of which can only read (select) data.
I can do it for a particular schema.
But I need it to work with all existing and future schemas (existing at least). How can I do it?
...
- 56.4k
8
votes
1
answer
6k
views
postgres create limited user that can select tables and columns of those tables
I have an app that automates the making of a PostgreSQL database, and also automates the creation of a database user. This database user is given limited access for security purposes, as it will be ...
- 31.6k
0
votes
0
answers
21
views
CREATE TABLE permissions required to append Pyspark dataframe to SSMS table
I am using AWS glue to extract some data from RDS, parse it into some other format and push it back to RDS.
The RDS user I use has SELECT and INSERT permissions on tic.Example. However, when I try to ...
0
votes
0
answers
79
views
How to prevent user from connecting via SSMS but allow query via .net application connectionstring?
I have been given task to create a SQL login that can be used to query the db via a .net application connection string.
But the user shouldn't be able to connect to SQL via SSMS.
I have created the ...
- 3,489
178
votes
20
answers
63k
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 ...
- 36
1
vote
0
answers
24
views
How to stop losing control of a user I just created due to SYSTEM_USER privilege in MySQL
We have recently been plagued by users getting created with the SYSTEM_USER privilege by users who don't have that privilege.
What do we need to do from a systematic perspective to stop this from ...
- 125
1
vote
1
answer
762
views
Bulk operation prerequisites for non-sysadmins
A developer need to use OPENROWSET function. The steps I did to allow him:
Enable ad hoc distributed queries Server Configuration Option
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
...
CommunityBot
- 1
9
votes
9
answers
61k
views
Error Authenticating Proxy Account when Executing SSIS job
I have a SQL Server instance that runs 5 scheduled tasks each night, each of which run SSIS packages.
These packages have been running for years and the associated steps run via a Proxy Account (...
- 317
0
votes
3
answers
1k
views
Create Sequence Permission for Postgres
When my postgres function is tries to create a new sequence, it returns a permission denied error. It looks like the only way to make it work is to give Create permission on schema using the below ...
CommunityBot
- 1
0
votes
1
answer
82
views
SQL Job Roles: How to grant a group permission to access SQL Jobs with unique mapping?
Issue
Developers on the same team are members of a Windows_Group.
These developers create jobs which they each own. Only the owner can edit the job so they cannot work together on same job. Because of ...
- 18k
14
votes
1
answer
19k
views
What are the privileges required to execute a trigger function in PostgreSQL?
What are the privileges required to execute a trigger function in PostgreSQL 8.4?
It seems that privileges set to a role do not matter when executing a trigger function. I think I have seen some day ...
- 182k
0
votes
1
answer
310
views
Import roles from <16 into >=16
I've got a backup from PostgreSQL 15 that I'm trying to get imported into PostgreSQL 16. One of the breaking changes between the versions is to "Restrict the privileges of CREATEROLE and its ...
- 56.4k
0
votes
1
answer
47
views
How to get list of permissions for a role in Postgres?
I'm trying to do what is described here.
That is, execute the following:
BEGIN;
DROP ROLE role_name;
ROLLBACK;
and get the:
list of permissions it can find that are granted to the role.
I can't ...
- 1
0
votes
1
answer
1k
views
How to list all functions with arguments that a role is allowed to execute in Postgres?
I found how to list tables a role has permissions on. I found how to list all functions with arguments.
My question is: How to list all functions with arguments a role is allowed to execute?
CommunityBot
- 1
29
votes
5
answers
104k
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 ...
- 117
0
votes
3
answers
1k
views
Cascading REVOKE not working in MYSQL
I've read about an SQL option of cascading permissions when issuing a REVOKE statement. I've googled for how this option is implemented in MySQL, but haven't found it at all.
This is the docs page ...
CommunityBot
- 1