Questions tagged [permissions]
Privileges granted to an account or role through the security mechanism of an operating system, database manager or other system.
1,507
questions
0
votes
0
answers
9
views
pg_dump permission denied for relation error for owner user
I am attempting to dump out a PostgreSQL database with pg_dump:
/usr/bin/pg_dump -f /home/crmpicco/rfc_754622.sql -F c -x -E UTF8 -b --disable-triggers -U root -h tillman.ff1lvb.us-west-2.rds....
- 145
0
votes
0
answers
17
views
How can I list all privileges that a PostgreSQL role has?
... including the inherited ones? When organizing access rights through different roles, this seems to be something really useful, yet I haven't found anything that would show how to do this.
AIUI, I ...
- 1
0
votes
0
answers
31
views
SSIS ForEach Loop Container Error: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80131509 "(null)"
I have done quite a bit of research on this and nothing has helped so far.
I have a multi-tab Excel file I need to load into a sql staging table. I created a new project/package in SSIS to loop ...
- 1
1
vote
1
answer
73
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
...
- 143
0
votes
0
answers
12
views
How to allow group write access for lo_export generated files on PostgreSQL server?
When saving a binary file using the large object function lo_export() (PostgreSQL 14, Ubuntu 22.04), the output files are read-only for the group.
$ ls -lh /tmp/*.tif
-rw-r--r-- 1 postgres postgres 2....
- 2,864
0
votes
1
answer
14
views
How to give Read_write users access to foreign tables imported through foreign data wrapper?
I had a question regarding the extension postgres_fdw. I am trying to use postgres_fdw to import foreign tables from DB A to DB B. These databases are on the same host.
In Database B, I have a set of ...
0
votes
1
answer
31
views
postgres: could not access directory "C:/opt/PostgreSql/14/data": Permission denied
Importantly: This is on Windows 10 and Postgres 14 (for newbies, Postgres is also known as PostgreSQL), with Cygwin providing the CLI.
On a fully functional, up and running, and apparently perfectly ...
- 209
0
votes
0
answers
38
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 ...
- 101
0
votes
0
answers
17
views
MySQL Database Setup Script(s) - Limiting Created User Access
PREFACE:
Per comments on my original post, this question will focus on the MySQL-specific elements of my goals. A separate question for the PostgreSQL version has been posted here: PostgreSQL Database ...
- 333
1
vote
0
answers
67
views
PostgreSQL Database Setup Script(s) - Limiting Created User Access
PREFACE (EDIT):
Per comments on my original post, this question has been modified to focus on the PostgreSQL-specific elements of my goals. A separate question for the MySQL version has been posted ...
- 333
0
votes
0
answers
15
views
Suddenly pgadmin doesn't allow me to select from a table
I created a database, and created a user to access it via Python. Postgres user is an owner. I didn't access the database for a couple of weeks (before everything was working), and now when I want to ...
- 101
0
votes
1
answer
34
views
Mysql DB viewer user (only for views) should access to information_schema and test?
I've created a user in our mysql/mariaDB engine so our customers can read some views. User has been created as follows and can query the views with no problem.
CREATE USER IF NOT EXISTS "...
- 111
0
votes
1
answer
63
views
How to grant read-write access to a user within the whole database cluster?
I got a database cluster with 1300 databases. I created a role and a user and granted access using the command GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser, so myuser ...
- 1
0
votes
1
answer
43
views
Postgres Materialized Views keeps getting dropped every time underlying views is updated
I am using Postgres as my DB and I have a bunch of Materialized Views that are created from a view.
The MVs are in Schema B and Views are in Schema A.
They are created like create SchemaB.mv1 as ...
0
votes
1
answer
87
views
How to allow remote MSSQL user to bulk insert my local txt file?
I have a txt file on my local PC. I run localy Java app which connects to remote MSSQL. Now I need to bulk insert from that text file into my database table.
However, SQL login can't read my file. I ...
- 133
0
votes
1
answer
68
views
Restrict access to a new schema to a specific role despite users having db_datareader?
I'm working in a SQL Server 2016 database that is not of my design nor can I fundamentally change the security structure. I know it is likely trash/worst practice but I have to play the hand I am ...
- 3
0
votes
1
answer
38
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;
...
- 11
0
votes
0
answers
71
views
How to use "with execute as" while querying a linked server from SQL Server
I have a strange permission issue with SQL Server 2019 (same thing happened also with 2016 version).
I have a database ReportDB, where reportuser (login: reportlogin) can run several reports.
I also ...
- 11
2
votes
1
answer
51
views
Error trying to add a login to Azure SQL Special Role 'dbmanager'
Upon running these commands on an Azure SQL instance:
CREATE LOGIN test WITH PASSWORD=N'Passw0rd'
GO
ALTER ROLE dbmanager ADD MEMBER test
GO
... I'm getting an error:
Cannot add the server principal ...
- 439
1
vote
0
answers
26
views
Maria DB limiting root user privileges
I am logged in as the root user in Maria DB and I cannot grant view access to a service account user.
Can anyone explain a cause or suggest a fix?
0
votes
2
answers
54
views
Oracle: a litte confused about privileges granted to my user
I want to give few permission to my non-dba user on my database.
I give create session, create table, view, etc..
This query seems to confirm
SELECT privilege from USER_SYS_PRIVS;
PRIVILEGE
--...
- 183
0
votes
2
answers
39
views
Use PostgreSQL users for all application users
Up to now I always used one database user, and handled permission checking, and corresponding filtering in my application.
This pattern has the drawback that the programming language which was chosen ...
- 1,341
0
votes
0
answers
32
views
Unable to drop a user in AWS Redshift
I want to drop user foo from an AWS Redshift db
DROP USER foo;
ERROR: user "foo" cannot be dropped because some objects depend on it
DETAIL: 1 object in database bar
1 object in database ...
- 181
1
vote
1
answer
95
views
Insufficient Privileges after creating Pluggable Database
I have to confess I have no idea about this pluggable database thing. I have experience in PostgresSQL, MySQL, SQL Server, but Oracle has me defeated.
I have Oracle 18c running in a Docker image. I ...
- 2,357
0
votes
1
answer
138
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 ...
0
votes
3
answers
77
views
How to migrate the permissions of logins - for example - grant/remove/deny permissions on system objects and server instance?
I'm migrating sql server 2017 to sql server 2019 on a separate machine.
I have backed up all the user databases, scripted all the sql jobs and logins.
On target server I will 1st run the logins script,...
- 2,407
0
votes
1
answer
44
views
Client has Granted Update/Insert/Delete - may accomplish this with Stored Proc, but not with a direct query
If I run Select * from fm_my_permissions(null,'database'), the UPDATE/INSERT/DELETE permissions are missing from the user. The user is mapped, shows up as owner, and I ran GRANT UPDATE/INSERT/DELETE ...
- 1
1
vote
2
answers
70
views
unable to drop user in PostgreSQL 12.4
I am unable to drop user as I am getting default privileges error.
postgres=# drop user xyz;
ERROR: role "xyz" cannot be dropped because some objects depend on it
DETAIL: owner of default ...
- 133
2
votes
2
answers
187
views
SQL Server Login Access getting revoked by a daily database refresh
Scenario : I have granted dbdatareader access on a database to the dev team. They do have daily database refresh in place . Because of this the permissions to the login , which is a SQL login are ...
- 1,574
0
votes
2
answers
44
views
What can malicious postgres db user do to a linux server?
Suppose I created a db user with
create role myuser login password 'xyz';
and allowed typical read and write db permissions.
If a malicious user finds these login credentials (and has access to db), ...
- 103
5
votes
1
answer
234
views
SQL Server allow stored procedure chaining
I have to premit that i am not a DBA but i have the need to mantain a SQL Server instance and have few problem with stored procedure permission chaining (hope terms is correct).
To brifiley explain my ...
- 287
4
votes
1
answer
251
views
How to revoke SA permissions that seem to be "stuck" to a login?
Apologies in advance if I don't use some terminology correctly here. I'm not a DBA but I've been temporarily forced into the role.
I've inherited an application that runs on SQL Server 2016 ...
- 43
1
vote
1
answer
172
views
MariaDB Read-Only user can't see database, why?
I have 2 users, one with ALL grants, another with SELECT only.
User 1 can see the database, not user 2, why?
Read-only user:
MariaDB [(none)]> SHOW GRANTS FOR 'readonly'@'%';
+----------------------...
- 11
0
votes
1
answer
109
views
Proper roles for a backup-only user on PostgreSQL
I am working on new processes and policies for my PostgreSQL databases when they are first created, and I am trying to define what role attributes are necessary for a role specifically used for ...
- 37
0
votes
1
answer
217
views
SQL Server on Linux, an issue with filesystem privileges for a backup creation
I'm trying to backup SQL Server to a directory that different users should have access to. To do this, I create a user group, and include the mssql user there - but when I try to create a backup, an &...
- 433
1
vote
1
answer
60
views
Cannot Retrieve User Permissions for All Users
I have the following database and users:
I would like to be able to check what permissions users have to certain tables. I can run the following command and get the permissions for tgrover:
USE ...
- 115
0
votes
0
answers
207
views
Linked Server Error Msg 7321, Level 16, State 2, Line 1
I am getting the following error when I execute a simple linked server query.
OLE DB provider "MSDASQL" for linked server "name" returned message "HTTP request returned status ...
- 63
0
votes
0
answers
71
views
How do you copy the permissions of a certificate to another certificate using Powershell?
How do you copy the permissions of a certificate to another certificate using Powershell? I added a certificate through a pfx file and the new certificate doesn't have the same permissions as the old ...
- 1
0
votes
1
answer
107
views
How to create a Postgres schema, let one role create tables in it, and let another role read from those tables
I'm trying to set up a Postgres database using a process along the following lines:
A script is run, authenticated to role postgres that:
Creates a new, empty schema schema0.
Creates a role role0 ...
- 103
0
votes
0
answers
137
views
Read Only user's access getting removed automatically in PostgreSQL
I have created a read only user per below commands:
CREATE ROLE test_dw WITH
LOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
NOINHERIT
NOREPLICATION
CONNECTION LIMIT -1
...
0
votes
1
answer
205
views
PostgreSQL granted all privileges on schema, but user still cannot insert a record
This is maddening.
I'm running PostgreSQL 14 in a Docker container using a standard image.
# docker-compose.yml
version: "3.7"
services:
pg:
image: postgres
environment:
- ...
- 321
0
votes
3
answers
269
views
Unable to grant membership to public role
I'm trying to refresh my dev db with a snapshot from prod. I've restored the production db from the prod server to my dev server just named as development. I'm trying to grant access to the dev db to ...
- 111
7
votes
3
answers
273
views
Preventing stored procedures from writing to databases
I am a database developer mostly writing SSRS reports. I would like to create stored procedures and assure the DBAs that those stored procedures cannot write to the production databases, without the ...
- 83
0
votes
0
answers
47
views
Default permissions / permission inheritance or some other common sense way to control permissions in Redshift
It blows my mind how clunky Redshift is. Maybe its PostgreSQL in general. In any case, it's ridiculous.
I have users connecting to a Redshift cluster with SAML authentication. The user is created on ...
- 254
0
votes
1
answer
21
views
Drop view from package
I have a user called DEPLOY defined with DBA role.
When logged on via SQL Developer the user can drop a view as follows:
DROP VIEW APP_USER.VIEW_NAME_VW CASCADE CONSTRAINTS
If I put the same ...
- 103
0
votes
0
answers
25
views
When root user changes a table name, how to ensure end user permissions also gets updated to the new table in MySQL?
I have a table - user1_db.table1
user1 has the following permission -
GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER ON `user1_db`.`table1` TO `user1`@`localhost`
Now the root user changes the ...
- 123
0
votes
0
answers
41
views
DBA role is not applied
This worked yesterday, but now it won't.
We've defined a user 'DEPLOY' on 'Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0' which will be used for deployments into each environment - DEV, ...
- 103
2
votes
2
answers
731
views
How can I grant permission to multiple databases / tables to a user in a single query in MySQL?
I am trying the query -
GRANT SELECT ON company.*, school.*, onlineshop.* TO user1@localhost;
To which I am getting a syntax error.
I can grant permissions one by one using -
GRANT SELECT ON company.*...
- 123
0
votes
1
answer
512
views
Create readonly user in postgresql
Using pg 13.4, created a readonly user reader for database abc, via:
CREATE ROLE reader WITH LOGIN PASSWORD 'psssswwwd'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity'...
- 189
0
votes
1
answer
30
views
Oracle 12cR2 User Can Select Table Without Privilege
I noticed an interesting thing today. Consider a system where the production and test databases are equipped with the same privileges. In the production database, a user can select the table under ...
- 29