New answers tagged

0

Short Answer: Try changing the default database of the dev user to their particular database. In the Query window within SS Studio, make sure they're using the right database context within the Query window via the database drop-down list or T-SQL USE [DBNAME]. Long Answer: I cannot speak for every circumstance but I know a case where this definitely ...


3

Yes. What you have will do what you want. I do question the need for UserA to have db_owner access even on database A though. Do they have a need to be able to drop the database? Run backups that could interrupt your backup chain? Change recovery model (same problem)? Generally even if a developer has complete control over a given database I would ...


2

Interesting one - difficult to pin this one down. Have you thought about looking at the public role? sp_helprotect 'CREATE PROCEDURE',NULL,NULL,'s' Does that bring you back anything?


2

Launch Shell as User If all else fails, on Ubuntu 14.04 LTS running on AWS, try this... ubuntu@foo $ sudo -u mongodb bash # OR whatever shell (zsh) mongodb@foo $ mongod --dbpath /data/db --fork OF COURSE, you'll add a much larger set of flags to the command. Invoke Command as User YMMV but this should work too... mongodb@foo $ sudo -u mongodb mongod ...


1

By default the DEFINER is the only user that can read stored programs. If DEFINER is left out then the default user that is created the function is used. This is a security feature. Definer and invoker security contexts differ as follows: A stored program or view that executes in definer security context executes with the privileges of the account named ...


1

Unlike sysadmin which bypasses checks, the built in database roles aren't so special that they can't be overridden with a DENY. Try looking at Exec sp_helpprotect Null, 'Username' and seeing what DENY records show up.


0

You are really creating your own authentication methods. Plan A: Use the PAM stuff in newer versions of MySQL. Plan B: Write a database layer where the user, via some UI, asks for something. Then the layer checks your tables to see whether he is allowed to see/change/etc the thing in question. Then it either denies access, or proceeds to generate and ...


6

Assuming they're all in the dbo schema: DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'GRANT SELECT ON dbo.' + QUOTENAME(t.name) + N' TO [User1];' FROM sys.tables AS t WHERE t.name LIKE N'VVC[_]%' AND t.[schema_id] = 1; EXEC sys.sp_executesql @sql; But Martin is right, it would be much better to use a schema than a string prefix.


6

There is no inbuilt method for granting permissions on tables matching a pattern. You need to grant it to the individual tables. It would be quite easy to generate the required script with a query against sys.tables for names like 'VVC[_]%' though. Instead of using a VVC prefix you could create a VVC schema. Then you can grant select permissions on the ...


1

Something like that could be achieved through dynamic sql, or via using stored procedures instead of views (for selecting) or triggers (for controlling inserts/updates).alter But you can also create the memberview to return values according to the currently logged on user, which is probably more practical: create view memberview as select Dept, Name ...


0

SYSTEM is a typical database user (such as HR or OE) The DBMS_LOB package belongs to SYS schema so that only the owner(Or user having the GRANT OPTION) can grant the object privilieges to other users. So if you connect as SYSDBA using SYSTEM user you will be connected to SYS schema. SQL> conn system/oracle Connected. SQL> show user USER is ...


3

Triggers run, by default, under the security context of the principal who caused the trigger to fire. In order to change this behavior, you'll need to create the trigger using the WITH EXECUTE AS OWNER clause. Below is an example which shows how that works. WITH EXECUTE AS OWNER allows the trigger to run in the security context of the database owner, ...


5

If you're happy using EXECUTE AS (trusting users that can impersonate), an alternative is: Tables CREATE TABLE dbo.Test ( TestID integer IDENTITY PRIMARY KEY, SomeDate datetime NOT NULL ); GO CREATE TABLE dbo.TestArchive ( TestID integer PRIMARY KEY, SomeDate datetime NOT NULL ); Users -- Ordinary user with the ability to insert to the ...


7

This can be accomplished using a Certificate and module signing (i.e. ADD SIGNATURE). Using Impersonation via EXECUTE AS can get messy, and it leaves the possibility of someone else Impersonating the "allowed" User, or changing the contents of a module that is using the EXECUTE AS. But with module signing: the Certificate-based User cannot be Impersonated ...


2

You need the USAGE privilege (at least) for the schema as well: GRANT USAGE ON SCHEMA something TO GROUP data_viewers; Related Postgres example: Permission for sequence in another schema Remember you only granted permissions to already existing tables. Does not apply to tables created later. To cover those, too: ALTER DEFAULT PRIVILEGES FOR USER ...


0

If the table is replicated. (SELECT * FROM sys.tables where is_replicated = 1). Then only SA role is able to execute "DELETE"


0

Community Wiki answer generated from question comments by Brandon Williams In the tutorial, the repl_distribution account will need to be added to the subscription database as db_owner. In the New Subscription Wizard, on the Distribution Agent Security page, you specify a Process Account, which would be the repl_distribution account that was created on ...


0

The simple test: postgres=# create schema foo; CREATE SCHEMA postgres=# create table foo.t(x int); CREATE TABLE postgres=# create role foomaster nosuperuser nologin inherit; CREATE ROLE postgres=# create role fooslave password '111' login inherit in role foomaster; CREATE ROLE postgres=# grant usage on schema foo to foomaster; GRANT postgres=# set role ...


3

A SQL Server login is used to authenticate the principal at the server level, and to provide server level permissions. A database user maps to zero or one login and is used to provide a security context within the database. Database permissions are assigned to database principals, including roles, not directly to logins. User names are generally not ...


2

I am not quite sure that I understand your question clearly, so please bear with me on the issue of Logins and Users. It appears to be, in your case, SQL Server logins and not Active Directory accounts, but they behave essentially the same within a server and database. Also, for what it is worth, it seems that some step or steps are missing from your ...


-1

You wanna do this (i think): GRANT SELECT, INSERT ON id.testtable TO GROUP NewGroup; "To group" is mandatory in that case.


1

janerole has been granted ALL privileges and johnrole has only been granted the SELECT privilege. You got at least one aspect of the command backwards in all your attempts: Alter default privileges for the role that creates objects - which in turn grants privileges to another role. I.e.: ALTER DEFAULT PRIVILEGES FOR ROLE janerole IN SCHEMA public GRANT ...


1

What you are looking for does not exist in MySQL 5.5 Unfortunately, the ALTER USER command for MySQL 5.6 is limited. All you can do is ALTER USER user@host PASSWORD EXPIRE; In MySQL 5.7, you could run the ALTER USER command as follows ALTER USER user@host REQUIRE NONE; When it comes to MySQL 5.5, you did the most expedient way possible. Great !!! A ...


1

GRANT EXECUTE TO [principal] is simply a shortcut for GRANT EXECUTE ON DATABASE::<dbname> TO [principal]; You can check this using the following: SELECT dp.name , perms.class_desc , perms.permission_name , perms.state_desc FROM sys.database_permissions perms INNER JOIN sys.database_principals dp ON perms.grantee_principal_id = ...


1

I wasn't paying attention to, or correctly interpreting, the results of the query from Kin's answer to this question: List all permissions for a given role? In the results of the query, you'll see execute listed but with no specific object type or object name!


1

The problem is that AutoClose option for database is set to True by default when you create a database with Express Editions. Go to DB properties, switch it to false and try again dropping it or use below script before dropping ALTER DATABASE [MyDb] SET AUTO_CLOSE OFF GO



Top 50 recent answers are included