Questions tagged [permissions]
Privileges granted to an account or role through the security mechanism of an operating system, database manager or other system.
1,494
questions
0
votes
1
answer
32
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
14
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
24
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
0
votes
0
answers
13
views
An open source ABAC UI for Hive data lake? [closed]
We have several TBs of data stored in Hive for data scientists and we need a permission management system(including a GUI) which satisfies the following features:
Data scientists can search for ...
- 101
2
votes
1
answer
30
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 ...
- 429
1
vote
0
answers
20
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
34
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
23
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,331
0
votes
0
answers
13
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 ...
- 171
1
vote
1
answer
38
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,337
0
votes
1
answer
43
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
64
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,130
0
votes
1
answer
39
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
44
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 ...
- 113
2
votes
1
answer
125
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,032
0
votes
2
answers
38
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
206
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
162
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
0
votes
1
answer
74
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'@'%';
+----------------------...
0
votes
1
answer
32
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
64
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 &...
- 363
1
vote
1
answer
58
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 ...
- 113
0
votes
0
answers
88
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
51
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
26
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
39
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
83
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
119
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
201
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
21
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
15
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
19
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
27
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
344
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
91
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'...
- 179
0
votes
1
answer
26
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
1
vote
1
answer
181
views
Grant read only access to postgresql schema
I want to grant user foo_user read only access to schema bar_schema.
I executed the following commands
postgres=> GRANT SELECT ON ALL TABLES IN SCHEMA bar_schema TO foo_user;
GRANT
postgres=> ...
- 171
1
vote
1
answer
103
views
Unable to restrict user from built in SQL Server Functions
We have a SQL Server (Standard) database with a user whom we are attempting to restrict permissions to only CRUD Operations to certain tables (CREATE, SELECT, UPDATE, DELETE) and only allow connect ...
- 111
0
votes
0
answers
24
views
Which rights does a user need to backup procedures in MySQL 8.x?
I am performing a backup on MySQL 8.0.26 which needs to include all routines, triggers, events etc. in order to be fully recoverable.
The command I am using:
/usr/bin/mysqldump -u $MUSER -p$MPASS ...
- 283
-1
votes
1
answer
29
views
ALTER DEFAULT PRIVILEGES IN SCHEMA vs ALTER DEFAULT PRIVILEGES FOR ROLE, what is the difference?
What is the functional difference between these two commands?
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name REVOKE ALL ON FUNCTIONS FROM old_role;
ALTER DEFAULT PRIVILEGES FOR ROLE old_role IN ...
- 101
0
votes
0
answers
18
views
how can I grant permissions by table name, for all schemas, with postgres?
If I have a database called 'A' and multiple schemas but they all have a table called 't',
Schemas get added per account, but they all look the same inside and have the same tables.
I'd like some ...
- 153
0
votes
1
answer
20
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,450
2
votes
1
answer
50
views
What locks does a REVOKE ALL PRIVILEGES ON a PostgreSQL table require?
What locks does a REVOKE ALL PRIVILEGES ON a PostgreSQL table require?
Specifically, could it get blocked by a long running ALTER TABLE which adds a column to the table?
0
votes
1
answer
78
views
Oracle - EXECUTE on package without explicit privileges
I'm trying to understand package permissions in Oracle.
If user A is granted EXECUTE on a package owned/created by user B, and that package makes various inserts and updates to a table owned by user B,...
- 137
0
votes
0
answers
23
views
Granting privileges on a view
I'm reading Database System Concept, in a chapter about authorization, the book first says that:
A user who creates a view does not necessarily receive all privileges
on that view. She receives only ...
- 11
0
votes
1
answer
39
views
Insufficient privilege for read only transaction
I am using Oracle and SQL Developer and here is a problem I ran into. I have created a table called T_TEST and I am trying to create a read only transaction to it from another user. This is my ...
0
votes
1
answer
39
views
I have permission to the table, but not to a view that uses the table
I am having a behaviour I cannot understand, and I have no idea where to look for solutions.
I have a view called wms_view_orderline that gets information from the wms_orderline table. When I SELECT ...
- 195
1
vote
1
answer
159
views
PostgreSQL denies change in table ownership
I am trying to change the ownership of a table for which I am already the owner.
I am also the owner of the database and have the CREATE and USAGE privileges on the PUBLIC schema.
I get the following ...
- 1,533
-1
votes
1
answer
68
views
How to assign minimal privileges for create view
In Db2 v11.1 on Linux we have a request to create a new user with access only to the "create view" privilege.
What are minimal permissions I have to set in order for the user to be able ...
- 57
0
votes
1
answer
66
views
Data from other user not visible
I am using Oracle and SQL Developer to create a database. I am fairly new to SQL, so this may be a simple question. I have my SYSTEM user, who owns several tables, for example this one:
CREATE TABLE ...